Reputation: 21

convert xml into table

I need to convert an oracle table into xml and then return it to table form.

I converted a table using xmlgen, but I don't know how to reverse the conversion. I'm looking for an example of converting an xml file into a table.

Upvotes: 2

Answers (4)

Sid Patel

Reputation: 173

You can create a relational view over xml table using XMLTABLE syntax.

e.g.

SELECT warehouse_name warehouse,
   warehouse2."Water", warehouse2."Rail"
   FROM warehouses,
   XMLTABLE('/Warehouse'
      PASSING warehouses.warehouse_spec
      COLUMNS 
         "Water" varchar2(6) PATH '/Warehouse/WaterAccess',
         "Rail" varchar2(6) PATH '/Warehouse/RailAccess') 
      warehouse2;

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions228.htm

Upvotes: 2

kurosch

Reputation: 2312

Depending on what you're ultimately trying to do, a simple way is to use XMLSequence() and Table() functions in SQL to convert a nodeset from an Xpath into a rowsource:

SELECT
    t.extract( '/bar/@id' ).getNumberVal() ID
FROM
    TABLE( XMLSEQUENCE( 
        XMLTYPE( 
            '<foo><bar id="1" /><bar id="2" /></foo>' 
        ).extract( '//bar' ) 
    ) ) t

Upvotes: 0