This discussion is archived
1 Reply Latest reply: Aug 22, 2013 8:46 PM by odie_63 RSS

How to write XML with attributes to a table in Oracle?

VijayanandSettin Newbie
Currently Being Moderated

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:

Sample XML

<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?

  • 1. Re: How to write XML with attributes to a table in Oracle?
    odie_63 Guru
    Currently Being Moderated
    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 :

     

    SELECT x.*

    FROM XMLTable(

           '/rep'

           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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points