Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
In the last days I've been playing with XML files using SQL Developer and other tools and i noticed the following interesting change in the behavior of SQL Developer when it comes to exporting the data from a table in XML format:
until version 1.5.0 build 5338, the elements in the XML file when exporting table were (in XPath notation format):
results/row/COLUMN NAME (note the mixture of lower and upper case, where COLUMN NAME was the relevant column name.
From version 1.5.1 build 5440, the element names have been changed and are now more consistent:
RESULTS/ROW/COLUMN@NAME="COLUMN NAME" (where COLUMN is now a fixed element whose NAME attribute contains the actual column name.
I've been lazily checking the release notes but could not find anything on this interesting change, but it could be that i didn't notice it.
Note that this new format is not the DBMS_XMLGEN canonical format (as it wasn't the previous one either).
DBMS_XMLGEN canonical format is:
ROWSET/ROW/COLUMN NAME
You can download and use the following XSL stylesheet (SQLDEV2APEX.XSL) to convert from this new format to the DBMS_XMLGEN canonical format, in case you wish to use the exported file to load a table using DBMS_XMLSTORE or through Oracle Application Express (Apex) Data Load page.
2 comments:
i found your blog while searching for answers to why when i now export my sql results to a xml format, the resulting file is now formatted differently than it was last year. your blog was from 2008 so it doesnt explain why this has changed. i have played around with the export window settings but i keep getting results where each data item now has a '<COLUMN NAME="....' preceeding each item name. I didnt get this last year and I dont want it now because the people who i am creating this for expect it in the same format as last year. can you help/
trying to create an xml export without the label '<COLUMN NAME='.
same time last year this label did not appear in the result file, only the actual column/item names. i have tried changing the export window settings but keep coming up with the same results. what can i do to change the xml format back to what it was last year....
ie:
<item1><![cdata.....
<item2><![cdata.....
etc
Thanks
Post a Comment