How to write XML attributes as columns to a table in Oracle?

Question

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?

The following worked for me for the temp table, eventually I am going to normalize it into more than one table..

-- Temp table
CREATE TABLE TEMP_Temp1
  (rtype varchar(10),
  title varchar(200),
  aid varchar(10),
  description varchar(100),
  flag varchar(50),
  po char(1));

x is varchar variable of the xml snippet above.

INSERT INTO TEMP_Temp1
    SELECT *
    FROM xmltable( '/rep' passing xmltype (x) columns rtype VARCHAR(10) path '@type' , title VARCHAR(200) path '@title')                                                                   AS xmlr ,
      xmltable( '/rep/a' passing xmltype (x) columns aid    VARCHAR(10) path '@id' , description VARCHAR(100) path '@description' , flag VARCHAR(50) path '@flag' , po CHAR(1) path '@po') AS xmla ;
  END;

Can you update your question with a description of the table that the XML needs to be inserted into? — Ian Carpenter
– Ian Carpenter, Commented Aug 22, 2013 at 21:15
-- Temp table CREATE TABLE TEMP_Temp1 (rtype varchar(10), title varchar(200), aid varchar(10), description varchar(100), flag varchar(50), po char(1)); — Vijayanand Settin
– Vijayanand Settin, Commented Aug 22, 2013 at 22:45

A.B.Cade · Accepted Answer · 2013-08-22 21:30:26Z

3

Maybe you can use INSERT-SELECT with a xmltable.
For example, you can select the data from your xml with a query like this:

Here is a sqlfiddle demo

select X.* 
from xmltable(
'for $i in //rep/as/a return <row>
  {$i/../../@type}
  {$i/../../@title}
  {$i/@id}</row>' passing xmltype('<rep type="P" title="P List">
<as>
<a id="3" />
<a id="4" />
</as>
</rep>')
  columns 
  type varchar2(1) path '@type',
  title varchar2(10) path '@title',
  id number path '@id'
) X

edited Aug 22, 2013 at 21:30

answered Aug 22, 2013 at 21:20

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to write XML attributes as columns to a table in Oracle?

Sample XML

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Sample XML

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related