Trick #4 - Pull XML with DBMS_XMLGEN
September 23rd, 2006 by The Oracle AlchemistWouldn’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:
SQL> select employee_id, first_name, last_name, phone_number
2 from employees where rownum < 6
EMPLOYEE_ID FIRST_NAME LAST_NAME PHONE_NUMBER
----------- -------------------- ------------------------- --------------------
100 Steven King 515.123.4567
101 Neena Kochhar 515.123.4568
102 Lex De Haan 515.123.4569
103 Alexander Hunold 590.423.4567
104 Bruce Ernst 590.423.4568
We get our standard output with no frills. Behold as we transform this into XML!
set pages 0
set linesize 150
set long 9999999
set head off
SQL> select dbms_xmlgen.getxml('select employee_id, first_name,
2 last_name, phone_number from employees where rownum < 6') xml
3 from dual
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPLOYEE_ID>100</EMPLOYEE_ID>
<FIRST_NAME>Steven</FIRST_NAME>
<LAST_NAME>King</LAST_NAME>
<PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
</ROW>
<ROW>
<EMPLOYEE_ID>101</EMPLOYEE_ID>
<FIRST_NAME>Neena</FIRST_NAME>
<LAST_NAME>Kochhar</LAST_NAME>
<PHONE_NUMBER>515.123.4568</PHONE_NUMBER>
</ROW>
<ROW>
<EMPLOYEE_ID>102</EMPLOYEE_ID>
<FIRST_NAME>Lex</FIRST_NAME>
<LAST_NAME>De Haan</LAST_NAME>
<PHONE_NUMBER>515.123.4569</PHONE_NUMBER>
</ROW>
<ROW>
<EMPLOYEE_ID>103</EMPLOYEE_ID>
<FIRST_NAME>Alexander</FIRST_NAME>
<LAST_NAME>Hunold</LAST_NAME>
<PHONE_NUMBER>590.423.4567</PHONE_NUMBER>
</ROW>
<ROW>
<EMPLOYEE_ID>104</EMPLOYEE_ID>
<FIRST_NAME>Bruce</FIRST_NAME>
<LAST_NAME>Ernst</LAST_NAME>
<PHONE_NUMBER>590.423.4568</PHONE_NUMBER>
</ROW>
</ROWSET>
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!
SQL> select department_id, department_name,
2 cursor(select first_name, last_name
3 from employees e
4 where e.department_id = d.department_id) emp_row
5 from departments d
6* where rownum < 4
DEPARTMENT_ID DEPARTMENT_NAME EMP_ROW
------------- ------------------------------ --------------------
10 Administration CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
FIRST_NAME LAST_NAME
-------------------- -------------------------
Jennifer Whalen
20 Marketing CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
FIRST_NAME LAST_NAME
-------------------- -------------------------
Michael Hartstein
Pat Fay
30 Purchasing CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
FIRST_NAME LAST_NAME
-------------------- -------------------------
Den Raphaely
Alexander Khoo
Shelli Baida
Sigal Tobias
Guy Himuro
Karen Colmenares
6 rows selected.
The results don’t look too impressive at the SQL prompt. However, watch as we surround it with a call to DBMS_XMLGEN.GETXML:
SQL> select dbms_xmlgen.getxml('
2 select department_id, department_name,
3 cursor(select first_name, last_name
4 from employees e
5 where e.department_id = d.department_id) emp_row
6 from departments d
7 where rownum < 4
8* ') from dual
<?xml version="1.0"?>
<ROWSET>
<ROW>
<DEPARTMENT_ID>10</DEPARTMENT_ID>
<DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
<EMP_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Jennifer</FIRST_NAME>
<LAST_NAME>Whalen</LAST_NAME>
</EMP_ROW_ROW>
</EMP_ROW>
</ROW>
<ROW>
<DEPARTMENT_ID>20</DEPARTMENT_ID>
<DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
<EMP_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Michael</FIRST_NAME>
<LAST_NAME>Hartstein</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Pat</FIRST_NAME>
<LAST_NAME>Fay</LAST_NAME>
</EMP_ROW_ROW>
</EMP_ROW>
</ROW>
<ROW>
<DEPARTMENT_ID>30</DEPARTMENT_ID>
<DEPARTMENT_NAME>Purchasing</DEPARTMENT_NAME>
<EMP_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Den</FIRST_NAME>
<LAST_NAME>Raphaely</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Alexander</FIRST_NAME>
<LAST_NAME>Khoo</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Shelli</FIRST_NAME>
<LAST_NAME>Baida</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Sigal</FIRST_NAME>
<LAST_NAME>Tobias</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Guy</FIRST_NAME>
<LAST_NAME>Himuro</LAST_NAME>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<FIRST_NAME>Karen</FIRST_NAME>
<LAST_NAME>Colmenares</LAST_NAME>
</EMP_ROW_ROW>
</EMP_ROW>
</ROW>
</ROWSET>
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.
November 2nd, 2006 at 9:37 am
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
November 3rd, 2006 at 2:45 am
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
November 3rd, 2006 at 10:05 am
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?
November 3rd, 2006 at 10:14 am
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
November 15th, 2006 at 2:50 am
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..
November 16th, 2006 at 2:00 am
Hi,
The dbms_xmlgen.getxml package is very good for Populate xml tags.
Is there any other advantage from this package?
Thanks,
Sharan.
November 16th, 2006 at 2:18 am
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.
November 16th, 2006 at 2:20 am
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.
November 16th, 2006 at 6:38 am
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
November 18th, 2006 at 12:43 am
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.
November 28th, 2006 at 12:10 pm
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?
November 28th, 2006 at 10:18 pm
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.
January 5th, 2007 at 10:01 am
Great article. Found very useful.
Thanks.
April 5th, 2007 at 4:05 pm
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;…
April 5th, 2007 at 4:26 pm
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.
July 10th, 2007 at 12:23 pm
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
August 9th, 2007 at 1:26 pm
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
August 9th, 2007 at 1:49 pm
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
January 15th, 2008 at 10:25 pm
[...] #3 is more in-line with your original needs. Personally I’m a fan of #1 and #2 because of the capabilities of returning a refcursor as a function, like passing it into DBMS_XMLGEN.GETXML. [...]
April 17th, 2008 at 3:02 pm
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
May 23rd, 2008 at 12:33 am
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.
June 30th, 2008 at 1:43 pm
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
June 30th, 2008 at 1:52 pm
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
August 4th, 2008 at 12:14 pm
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
August 20th, 2008 at 6:48 am
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
September 18th, 2008 at 2:35 pm
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.