Generate xml file from xmlType column in Oracle
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.
Leave a Comment