Trick #4 – Pull XML with DBMS_XMLGEN

Share Button

Wouldn’t it be fantastic if you could simply pull data from the database in XML? Many shops use XML for data transfer, web services, reports, and more. Oracle’s XML Publisher product can retrieve XML from an HTTP feed and use it to generate rich reports with graphs, images, and other content, and then mail, fax, print, or FTP them.

All we need is the actual XML!

For this, we have the DBMS_XMLGEN package. This package generates XML on the fly using any query you want; in addition, it’s extremely easy to use from either the SQL prompt or in code, as it’s just a simple query.

Generating XML From the Database

Take this standard query:

We get our standard output with no frills. Behold as we transform this into XML!

Beautiful! Fully compliant XML that can be easily integrated into any application, with ROWSET and ROW tags in place to identify nodes, and tags for each column you pulled out of the database.

All we had to do was wrap the query in the DBMS_XMLGEN.GETXML function call, selected from DUAL. The query remained exactly the same.

A More Advanced Use

Let’s take it one step further. Most XML has subnodes for each main node. For instance, what if we wanted to pull XML for every department, and a subnode for every employee under it? We can use the CURSOR function!

The results don’t look too impressive at the SQL prompt. However, watch as we surround it with a call to DBMS_XMLGEN.GETXML:

Note that I didn’t change the query in any way. But check out the results! We have each DEPARTMENT as a ROW tag, and the cursor we created gives us an EMP_ROW node containing recurring EMP_ROW_ROW nodes.

Conclusion

This can be extremely useful for quick retrieval of web records. Simply make a page that accepts input, such as DEPARTMENT_ID, and passes it into the query above. Display the response directly to the screen, and you have an easy XML display program. The best part comes with reports. XML Publisher is made to accept XML that looks just like this and form extremely detailed reports using templates made in Microsoft Word. With queries such as these and XML Publisher you can have a full reporting suite that easily pulls data, forms it into a PDF, DOC, XLS, or HTML report, and distributes it anywhere you would like it to go.

Share Button

40 comments

  1. Pretty cool! But there is one problem if I try this with a simple query. I just get something like this:

    XML
    ——————————————————————————–

    0
    zzz_frei_Rechts

    1 row selected.

    Seems that it stops in the middle of nowhere with the output. Any suggestions?

    Best Regards
    Ralf Kanis

  2. Well written. Excellent article.I am new to XML publisher. I would like to know how I can integrate the generated XML with the layout template that I have created [RTF] using XML publisher.

    Thanks,
    Ash

  3. Ralf, did you try running these lines before the query?

    set pages 0
    set linesize 150
    set long 9999999
    set head off

    The result looks odd (no tags), but getting cut off in the middle of nowhere usually happens when you don’t use “set long.”

    If that doesn’t fix it, can you post your query?

  4. Ashley,

    Thank you very much for your compliment. Are you trying to integrate using the Enterprise publisher, or the Word plugin?

    If it’s the word plugin, you can simply use a query in the wizard, it will automatically wrap DBMS_XMLGEN around it to make it work. Or you can generate an XML file using the methods I have outlined in my article then open the XML in Word to make it work with your template.

    If you are talking about the Enterprise version, you can always spool the XML to a file on a webserver. In the XML Publisher Enterprise setup, just set up the source as an HTTP Feed pointing to the generated file.

    I’d recommend this article to help you get started: http://www.oracle.com/technology/pub/articles/rittman-xmlpub.html

  5. Great Article!!!. I am new toXML publisher. I want to know is that how will I ahndle empty tags. I do not want empty tags to appear in my XML….How can I ignore empty tags.
    in the below eg i don’t want to publish emp_row tag.

    10
    FINANCE

    Thanks for the help..

  6. Hi,

    The dbms_xmlgen.getxml package is very good for Populate xml tags.
    Is there any other advantage from this package?

    Thanks,
    Sharan.

  7. Sharanappa,

    Well, populating tags is really all it does, but there is more you can do with the package. You can have the package check for special characters and escape them (setConvertSpecialChars), or check for invalid characters (setCheckInvalidChars). There are many ways to format your XML to your exact liking.

  8. Vivek,

    By default, DBMS_XMLGEN.getXML will leave out null tags. Take a look here.

    I’m not exactly seeing what you’re hoping to accomplish in the example you pasted. Perhaps a little more detail? You can email me if you like.

  9. Hi,

    Before starting thanks to the answer you had given me previoulsy.

    This time I have 2 questions over here.

    1. For creating reports using XML publisher I am now creating the xml file [using dbms_xmlgen] via a concurrent program and then merging it with the layout template. What do u feel ,should I really use a Data Template for generating the XML output file or will xmlgen be enough?

    2.See the below code.

    PROCEDURE MAIN ( errbuf OUT VARCHAR2
    ,retcode OUT NUMBER
    ) IS

    v_query_ref g_query_ref;

    v_handle dbms_xmlgen.ctxhandle;
    v_result clob;

    BEGIN

    OPEN v_query_ref FOR
    SELECT DECODE(ppt.system_person_type,’EMP’,’N’,’T’) action_code
    ,papf.attribute1 leg_indentifier
    , TO_CHAR(papf.attribute3 ,’DD-MON-YYYY’) retirement_date
    ;

    v_handle := dbms_xmlgen.newContext(v_query_ref);

    DBMS_XMLGEN.SETROWTAG (v_handle , ‘EMPLOYEE’);

    v_result := dbms_xmlgen.getXML(v_handle);

    fnd_file.put_line(fnd_file.output,v_result);

    EXCEPTION
    when others then
    errbuf := sqlerrm;
    retcode := sqlcode;
    fnd_file.put_line(fnd_file.log,’sqlerrm ‘||sqlerrm);

    END MAIN;

    The sql returns more than 3200 records. My problem is fnd_file(fnd_file.output,v_result) is giving me ORA -06502

    When I changed the cursor so that it would just return me 5 records fnd_file worked fine.

    How do I handle this?

    Thanks in Advance,
    Ashley

  10. Ashley,

    1. A data template is good because it will work with the concurrent manager with minimal fuss, but it sounds like you got it. I prefer generating the XML myself, because then I have greater flexibility (as you clearly show in your example code).

    2. The problem appears to be flushing. FND_FILE, like UTL_FILE, can only write 32k at a time before it must be flushed. You may want to try taking some of the recommendations found in this forum thread I found.

  11. Steve,
    Thanks for helping to bridge the gap in my understanding between the XML Publisher docs and actual reports! Maybe you can help me get over one last hurdle: I’m trying to work with the nested XML that you generate with your cursor trick. I created an .rtf template in the Word plugin using the SQL without the DBMS_XMLGEN, and that previews fine. Then, in XML Publisher Enterprise, I uploaded that template as a layout, and created a data model using the same query. However, I cannot view the report, I get an error saying that the report cannot be rendered because of an error. I have tried making the data model with the DBMS_XMLGEN wrapper and without it, and get the same error both ways. Am I still unclear on the concept of how these two pieces fit together?

  12. You don’t need the XMLGen wrapper when doing it as a data model.

    The error you’re getting is a generic one, and can be caused by ANY problem with your report. Try editing the report and making sure all your information is correct in every single section. Make sure the data source name is right in all places, and that it’s being run as a user with proper permissions to access the data.

  13. Hey Steve- long time no chat – so I am using dbms_xmlgen.getXML to try and create an xml file containing 326,000 records – and the file never gets past 11000 recs. I don’t think it is a spool issue – as I have spooled files over 40MB before – is there some internal milit with this function ? or am I missing something in my set commands ?

    set serveroutput on size 1000000
    set head off;
    set long 9999999;
    set pages 0;
    set linesize 150;
    set buffer 6000000;
    set feedback off;
    spool vast_automart.xml;

    select dbms_xmlgen.getXML(‘select… from temp table’) from dual;…

  14. Darcy,

    Hey, good to see you!

    You said your query was “select dbms_xmlgen.getXML(’select… from temp table’) from dual”, but that “…” is going to be very important.

    You set your “long” to 9,999,999. With 326,000 records, you would only be allowed a maximum of 30.67 characters per row. All it will take is a single column with a 13 character column name to use that!

    The maximum for “set long” is 2 billion…that will leave enough for 6,134.97 characters per row, which is definitely more realistic. If you’re getting 11,000 rows with a “set long” value of 9,999,999, then you’re averaging ~910 characters per row of XML, so that requires a set long of at least 296,660,000.

  15. Hi,

    I have been working with XMLP for a little while now and I am facing an hard issue here, I have to generate a report containing 3 queries and i am not able to find any documentation explaining in details or even roughly how to proceed.
    I am hoping that in the past you faced a similar issue and will be able to guide me into the right direction.

    Regards,

    Staind

  16. Great Article! I am just becoming familar with all of this so the examples are really helpful.

    I have a question regarding the tags:

    1. is there an easy way to change the tag names. when i ran a simple “select count(*)…” in this dbms_xmlgen i received the following:

    8

    is there a way i can change the tag field?

    thanks,

    Lori

  17. oops….it didn’t show on the website correctly.

    this is what i am getting:

    COUNT_x0028__x002A__x0029_ in the tag fields around the 8

    thanks

  18. Hi,
    I would like to return two query’s which have not relation themselves in the same xml. I mean, the result could be:

    How coud I perform the query and the PLSQL code?

    Thanks in advance

  19. Hi ,

    Data in one of the columns of my query has relatively more length(300 characters ).
    When I used DBMS_XMLGEN to generate xml that element is getting printed in 2 rows.
    Example:

    dataaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaa

    When my xml is fed to XML Publisher to generate report it is failed with unable to parse XML error.

    Any idea how to print the element in single line.I tried with DBMS_QUERYXML.GETXML.
    It worked for some time and failed again.

    Thanks in advance.

  20. Hi

    Similar to Sreeni’s question

    using this method great – but the elements (forest items) won’t write to individual lines in the spool. lines with multiple attributes are then wrapped, and effectively chopping the elements at the line wrap level – tried altering linesize – set it to 150 and it wraps at 100 !

    Here is my sho all
    SQL> sho all
    appinfo is OFF and set to “SQL*Plus”
    arraysize 15
    autocommit OFF
    autoprint OFF
    autorecovery OFF
    autotrace OFF
    blockterminator “.” (hex 2e)
    btitle OFF and is the first few characters of the next SELECT statement
    cmdsep OFF
    colsep ” ”
    compatibility version NATIVE
    concat “.” (hex 2e)
    copycommit 0
    COPYTYPECHECK is ON
    define “&” (hex 26)
    describe DEPTH 1 LINENUM OFF INDENT ON
    echo OFF
    editfile “afiedt.buf”
    embedded OFF
    escape OFF
    FEEDBACK ON for 6 or more rows
    flagger OFF
    flush ON
    heading OFF
    headsep “|” (hex 7c)
    instance “local”
    linesize 200
    lno 1
    loboffset 1
    logsource “”
    long 1000000000
    longchunksize 2000000000
    markup HTML OFF HEAD ” body {font:10pt Arial,Helvetica,sans-serif; color:blac
    newpage 1
    null “”
    numformat “”
    numwidth 10
    pagesize 0
    PAUSE is OFF
    pno 0
    recsep WRAP
    recsepchar ” ” (hex 20)
    release 1002000100
    repfooter OFF and is NULL
    repheader OFF and is NULL
    serveroutput ON SIZE UNLIMITED FORMAT TRUNCATED
    shiftinout INVISIBLE
    showmode OFF
    spool OFF
    sqlblanklines OFF
    sqlcase MIXED
    sqlcode 0
    sqlcontinue “> ”
    sqlnumber ON
    sqlpluscompatibility 10.2.0
    sqlprefix “#” (hex 23)
    sqlprompt “SQL> ”
    sqlterminator “;” (hex 3b)
    suffix “sql”
    tab ON
    termout ON
    timing OFF
    trimout OFF
    trimspool OFF
    ttitle OFF and is the first few characters of the next SELECT statement
    underline “-” (hex 2d)
    USER is “DERRIN”
    verify ON
    wrap : lines will be wrapped

    Thanks

  21. Are you spooling the data? Here are the ones I’ve had the most luck with:

    set long 999999999
    set linesize 32767
    set longchunksize 999999999
    set feedback off
    set verify off
    set pagesize 0
    set trimspool on long 999999999

  22. Hi there,

    I have been toying with XML file generation thru dbms_xmlgen.NewContext but have been having problems suppressing multiple tags. I have something like the following-
    dbms_xmlgen.newcontext(‘SELECT /*+ FIRST_ROWS PARALLEL(a11,16) PARALLEL(a12, 16) */ XMLELEMENT(“provider”, XMLForest(DECODE(a13.HOSPITAL_ID,”C”,110078,”E”,110010) as “provider-id”),XMLELEMENT( “patient”, XMLForest ( a111.PATIENT_FIRST_NM “first-name”, a111.PATIENT_LAST_NM “last-name” ),XMLELEMENT(“encounter”, XMLATTRIBUTES ( ”HOP-SURGERY” AS “measure-set”),
    XMLForest ( a113.DAY_ADMIT_DESC as “encounter-date”,a110.PLAN_INSURANCE_CARD_NBR “patient-HIC-Number”, a111.PATIENT_ID as “patient-id”, a12.MEDICAL_RECORD_NBR as “medical-record-nbr”)
    ))) as “provider” FROM ….’);
    When I write the XML Doc to a table, I get multiple “provider” tags in the document. I have defined setrowsettag to be “provider” but in addition to this I am getting the two embedded labels as well. How do I suppress them from showing up at all? All I want is just one tag defined in the setrowsettag.

    Please help!!

    VR

  23. Your suggestion of setting in sql*plus the following parameters

    set pages 0
    set linesize 150
    set long 9999999
    set head off

    Worked perfectly.

    Thank you for a clear and precise explanation of how to get XML data out of Oracle – Now why can Oracle not do anything this simple and efficient to show how their technologies work?

    Cheers,
    Daniel

  24. Good Post.. I have been trying in vain to remove the ‘_ROW’ tag that is displayed in the sub nodes. I know it differentiates the different rows. But my requirement is such that I need to remove the ‘_ROW’ tag.

    Can someone help me find a way ?? It’s urgent for me.

  25. Hi,

    I ran the following as an sql file :

    set serveroutput
    set long 2000000000
    set pagesize 0
    set pages 0
    set linesize 150
    set buffer 6000000
    set echo off
    set heading off
    set underline off
    set trimspool off
    spool C:\Neeraj\SQLPLUS\Exported_Data\ACE_DATA.xml
    select dbms_xmlgen.getXML(‘select * from SK101468_190.ACE_DATA’) from dual;
    spool off
    set echo on
    set heading on
    set underline on
    set trimspool on

    It produces xml that can be opened using IE..only if I run it using PL/SQL developer..when I run it using SQL*Plus..it produces xml..but IE throws an error when opening this xml..at somepoint…showing the ending tag is wrapped down..so invalid tag

  26. I thought this explanation was awesome. Straigtforward how to – what a concept!
    I am new to xml publisher and just plain old xml. I’m trying to run the following but there additional quotes makes it cough – suggestions?
    set pages 0
    set linesize 150
    set long 999999999
    set head off
    select dbms_xmlgen.getxmltype(“select distinct a.business_unit, a.account, a.operating_unit, a.monetary_amount, a.journal_date, c.category_descr
    from owner.ps_jrnl_ln a, owner.ps_sch_energy_acct b, owner.ps_sch_energy_catg c, owner.ps_jrnl_header d
    where a.business_unit = b.setid
    and a.account = ‘50102’
    and a.account = b.account
    and a.operating_unit in(‘117′,’118′,’133′,’136′,’173′,’191′,’703′,’706′,’721′,’754’)
    and b.CATEGORY_TYPE = c.CATEGORY_TYPE”) xml from dual;

  27. I want to know if RSS feeds can be directly taken into an oracle table. How would I import an RSS feed since it is XML? Would I have to put the RSS feed into a file first or could it be realtime?

  28. I am encountering a problem in that I use rpad(field.name,4, ‘ ‘) in my query and when the sql encounters the first ‘ it thinks it is at the end of the script and wants a right parenth. My sql hasa the ‘ in numerous places and I cannot seem to get around this. any suggestions?

    Thanks
    Ron

  29. Is there a max output filesize limit to XML Publisher that you have come across? I don’t see anything in the Oracle XML Publisher documentation. Thanks

  30. Hi All,

    this article is very interesting! I’m working with XMLGEN since a while but I still did not solve issue with XML when generated file have ‘big’ outputs.
    For example I created this procedure:

    CREATE OR REPLACE PROCEDURE APPS.CRV_SUPPLIER_BALANCE
    ( errbuf OUT VARCHAR2,
    retcode OUT VARCHAR2,
    p_period IN VARCHAR2
    — p_ledger_id IN NUMBER
    ) AS

    RESULT NCLOB; /**Variabili per gestire la creazione e lo split dell’output XML **/
    LEN NUMBER;
    XMLSTR VARCHAR2(32767);
    OFFSET NUMBER(10) :=32767; /** 32767 e’ la lunghezza massima del VARCHAR2 supportato da fnd_file.output **/
    RETRIEVED NUMBER(10) :=0;
    SQL_STRING VARCHAR2(4000);
    ERR_MSG VARCHAR2(2000);
    ERR_CODE VARCHAR2(2000);

    BEGIN
    SQL_STRING := ‘SELECT
    sup.vendor_name
    ,gl.segment2
    ,tl.DESCRIPTION
    ,sl.currency_code
    ,substr(sl.accounting_date,4,7)
    ,sum(sl.accounted_dr)
    ,sum(sl.accounted_cr)
    from XLA_AE_LINES sl
    ,XLA_AE_HEADERS sh
    ,XLA_EVENTS se
    ,GL_CODE_COMBINATIONS gl
    ,AP_SUPPLIERS sup
    ,FND_FLEX_VALUES fl
    ,FND_FLEX_VALUES_TL tl
    where 1=1
    and sl.ledger_id = ”’||2025||”’
    and tl.language = ”’||USERENV (‘LANG’)||”’
    and sh.balance_type_code = ”’||’A’||”’

    and sl.ae_header_id = sh.ae_header_id
    and sl.code_combination_id = gl.code_combination_id
    and sl.party_id = sup.vendor_id
    and gl.segment2 = fl.flex_value
    and tl.flex_value_id = fl.flex_value_id
    and fl.flex_value_set_id = ”1014148”
    and se.event_id = sh.event_id
    and substr(sl.accounting_date,4,7) = ”’||p_period||”’
    and sup.VENDOR_NAME = ”AGENZIA DEL TERRITORIO”
    group by
    sup.vendor_name
    ,gl.segment2
    ,tl.description
    ,sl.currency_code
    ,substr(sl.accounting_date,4,7)
    order by substr(sl.accounting_date,4,7)
    ,sup.vendor_name
    ,gl.segment2′;

    SELECT DBMS_XMLGEN.getxml(SQL_STRING) INTO RESULT FROM DUAL;
    fnd_file.put_line (fnd_file.output, RESULT);
    ERRBUF := null;
    RETCODE := 0 ;
    EXCEPTION
    WHEN OTHERS THEN
    ERR_MSG := SQLERRM;
    ERR_CODE := SQLCODE;
    fnd_file.put_line(FND_FILE.LOG, ‘ERRORMSG: ‘||ERR_MSG||’, ERRORCODE: ‘||ERR_CODE);
    RETCODE := 2 ;
    END;

    How can I split XML output??

    Thnaks,
    Michele

  31. My Table

    ID NAME SAL
    1 JOHN 23
    2 RONX

    SELECT dbms_xmlgen.getxml(‘select * from T_x’) from dual;

    1
    JOHN
    23

    2
    RONX
    <<<<<——– Sal

    Deficiency Node :S PLEASE HELP ME…

  32. Dear The Oracle Alchemist ,

    I need to generate a main query that will give record by record instruction and then i need to generate a second query where all the summation history of the main records will be displayed….

    for example:

    1
    MBB ASSET BOOK
    10309
    AA TANAMAN HIJAU ENTERPRISE
    FIXED ASSET
    800161134
    OFFICE EQUIPMENT.OE0011
    CAPITALIZED
    86111
    AC-MBB ACCOUNTING
    2
    1

    1000.00
    1000.00
    1000.00
    MYR

    2010/06/29
    1198
    2010/07/01
    1002

    0
    3506
    3
    2011/01/17
    EGL
    -1
    1
    -1

    In the above example the first ROW tag is from the main query. The last ROW tag is from the second query that is the summation of the Main query.

    How to keep the tags same as ROW query for both first and second query and how to nest this two queries in single execution to get this kind of xml format. all the tag specified are very important like ROOT and ROW…….

    Thanks in Advance.

  33. Hi Steve,

    I am trying to run the following script…

    DECLARE
    qryCtx dbms_xmlgen.ctxHandle;
    result CLOB;
    BEGIN
    qryCtx := dbms_xmlgen.newContext (‘SELECT * from Employee WHERE Emp_id = 221930 ;’);
    dbms_xmlgen.setRowSetTag(qryCtx, ‘Employee_Details’);
    dbms_xmlgen.setRowTag(qryCtx, ‘Employee’);

    result := dbms_xmlgen.getXML(qryCtx) ;
    dbms_xmlgen.closeContext(qryCtx);
    INSERT INTO temp_xml VALUES(result);
    END ;

    But getting the following error:

    ORA-19202: Error occurred in XML processing
    ORA-00911: invalid character
    ORA-06512: at “SYS.DBMS_XMLGEN”, line 7
    ORA-06512: at “SYS.DBMS_XMLGEN”, line 147
    ORA-06512: at line 12
    19202. 00000 – “Error occurred in XML processing%s”
    *Cause: An error occurred when processing the XML function
    *Action: Check the given error message and fix the appropriate problem

    I am not able to understand what the exact problem is. Could you please help me out on this?

    Best regards,
    Arijit

  34. Hi,

    Is there a way to format the date and value fields? I have some fields which came out with long lengths –

    3107.860000000000127329258248209953308105

    It would be nice to set a format flag for numbers, date fields, without explicit column names so I can have a generic function which can be used for different tables

    Thanks

  35. Hi, I am trying to create a XMl file as per below

    Need to create one generic stored procedure which will take table name as input parameter and create xml file with below format.
    Xml file name should be table name.

    AAA
    AAA

    AAA
    AAA

    AAA
    AAA

    AAA
    AAA

    AAA
    AAA

    = actual table name(case should be same as in database)
    = actual column names from table(case should be same as in database)
    Keep other tags as it is. Repeat tag for each row retrieved.

    Please help me ASAP

  36. Need to create one generic stored procedure which will take table name as input parameter and create xml file with below format.
    Xml file name should be table name.
    Use and in all text
    XML
    TABLENAME/TABLENAME
    RECORDS
    RECORD
    COLNAME AAA COLNAME
    COLNAME AAA COLNAME
    RECORD
    RECORD
    COLNAME AAA COLNAME
    COLNAME AAA COLNAME
    RECORD
    RECORD
    COLNAME AAA COLNAME
    COLNAME AAA COLNAME
    RECORD
    RECORD
    COLNAME AAA COLNAME
    COLNAME AAA COLNAME
    RECORD
    RECORD
    COLNAME AAA COLNAME
    COLNAME AAA COLNAME
    RECORD
    RECORDS
    XM
    TABLENAME = actual table name(case should be same as in database)
    COLNAME = actual column names from table(case should be same as in database)
    Keep other tags as it is. Repeat tag for each row retrieved.

  37. hi ,

    I tried the way you explained, let me know where i gone wrong.

    my xml tag element is continuing on next line

    ——————————-
    UNRECOVERABLE – COMPANY IS UNDERGOING FINANCIAL PROBLEMS
    ————————————————————————

    definitions are as below
    set serveroutput on size 1000000
    set head off;
    set long 999999999;
    set pages 0;
    SET LINESIZE 150;
    SET PAGESIZE 40000;
    set buffer 6000000;
    set feedback off;

Leave a Reply