Skip to content

Generate xml file from xmlType column in Oracle

14 April 2011

I was asked recently to generate a XML file from Oracle database (10g). The column I had to use was of XMLType. XML file had to be created with a single SELECT statement.

The table (this is a sample just for testing purposes) structure was like this:

CREATE TABLE testtbl(xdata XMLTYPE);
INSERT INTO testtbl VALUES('<Field><Label>End Date</Label><Value Name="END_DATE">01.06.2016</Value></Field>');
INSERT INTO testtbl VALUES('<Field><Label>type</Label><Value Name="TYPE">Example</Value></Field>');
INSERT INTO testtbl VALUES('<Field><Label>Name</Label><Value Name="CUST_NAME">Smith</Value></Field>');
INSERT INTO testtbl VALUES('<Field><Label>Date of birth</Label><Value Name="CUST_BIRTH_DATE">27.08.1972</Value></Field>');

After few iterations I’ve finished with

SELECT dbms_xmlgen.getxml(' SELECT * from testtbl') from dual

and the result:

<?xml version="1.0"?>
<ROWSET>
 <ROW>
 <XDATA>
 <Field>
 <Label>End Date</Label>
 <Value Name="END_DATE">01.06.2016</Value>
</Field>
 </XDATA>
 </ROW>
 <ROW>
 <XDATA>
 <Field>
 <Label>type</Label>
 <Value Name="TYPE">Example</Value>
</Field>
 </XDATA>
 </ROW>
 <ROW>
 <XDATA>
 <Field>
 <Label>Name</Label>
 <Value Name="CUST_NAME">Smith</Value>
</Field>
 </XDATA>
 </ROW>
 <ROW>
 <XDATA>
 <Field>
 <Label>Date of birth</Label>
 <Value Name="CUST_BIRTH_DATE">27.08.1972</Value>
</Field>
 </XDATA>
 </ROW>
</ROWSET>

Hope to be useful.

Advertisements

From → Oracle

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: