I tried to find solutions over the internet. Some of the stuff I looked at:https://forums.oracle.com/thread/2182669 http://www.club-oracle.com/forums/how-to-insert-data-from-xml-to-table-t2845/
In all these cases, the solution considers XML structure with only nodes and child nodes but not attributes. In fact, one of the solutions suggests transforming the XML into a canonical form with only nodes w/o attributes.
This is a sample of xml structure I am working with:
<rep type="P" title="P List"> <as> <a id="3" /> <a id="4" /> </as> </rep>
I am working with oracle client 11.2 and SQL developer
My question is: how to write XML data into a table with attributes also as column values, beside the nodes?
My question is: how to write XML data into a table with attributes also as column values, beside the nodes
The question you should be asking is : "how do I access attributes in the XPath language?"
and the answer to that is easily found in any XPath tutorial you may find over the Internet, it is not related to Oracle in particular.
Short answer : you use an "attribute::" axis before the attribute name, or more commonly a "@", e.g. @type, @id etc.
Using the method described in the first link, something like this will extract the root attributes :
passing <xmltype variable/column goes here>
columns type varchar2(1) path '@type'
, title varchar2(30) path '@title'
) x ;
For deeper levels, use additional XMLTable calls as described in the mentioned post.