How do you parse a simple XML fragment in Oracle PL / SQL and load it into a global temporary table?

Question

In SQL Server, it is easy to parse a vachar variable containing a simple XML fragment built with attributes and load it into a temp table - see the example below:

declare @UpdateXML VARCHAR(8000) set @UpdateXML='<ArrayOfRecords> <Record Field01="130" Field02="1700" Field03="C" /> <Record Field01="131" Field02="1701" Field03="C" /> <Record Field01="132" Field02="1702" Field03="C" /> </ArrayOfRecords>' DECLARE @hdoc int EXEC sp_xml_preparedocument @hdoc OUTPUT, @UpdateXML INSERT INTO #tblTemp( [Field01], [Field02], [Field03] ) SELECT * FROM OPENXML(@hdoc, '//ArrayOfRecords/Record') WITH ( Field01 int, Field02 int, Field03 char(1) ) EXEC sp_xml_removedocument @hdoc 

Is there a simple example equivalent to this in Oracle PL / SQL?

Oracle has the DBMS_XMLSTORE package, but it needs an XML fragment in a specific canonical format using the ROWSET and ROW elements. DBMS_XMLSTORE does not work with XML attributes.

Also, I'm not 100% sure if I need to create an XSD of my XML fragment and register it in the Oracle database before I can use any of the other PL / SQL XML tools / queries.

Thanks!

Answer 1

The Oracle XML DB implementation has an overtly confusing number of options, and this is not always clear (at least for me) that applies in any given scenario. In this particular case, you want XMLTable () , which turns XQuery into a series of strings.

First we create a table.

 SQL> create table t23 2 (field01 number 3 , field02 number 4 , field03 char(1) 5 ) 6 / Table created. SQL> 

Then we fill it ...

 SQL> declare 2 x varchar2(2000) := '<ArrayOfRecords> 3 <Record Field01="130" Field02="1700" Field03="C" /> 4 <Record Field01="131" Field02="1701" Field03="C" /> 5 <Record Field01="132" Field02="1702" Field03="C" /> 6 </ArrayOfRecords>'; 7 begin 8 insert into t23 9 select * 10 from xmltable 11 ( '/ArrayOfRecords/Record' 12 passing xmltype (x) 13 columns f1 number path '@Field01' 14 , f2 number path '@Field02' 15 , f3 char(1) path '@Field03' 16 ) 17 ; 18 end; 19 / PL/SQL procedure successfully completed. SQL> 

Finally, prove that it worked ....

 SQL> select * from t23 2 / FIELD01 FIELD02 F ---------- ---------- - 130 1700 C 131 1701 C 132 1702 C SQL>