How do you parse a simple XML snippet in Oracle PL/SQL and load it in a global temp table?

Question

In SQL Server it is easy to parse a vachar variable that contains a simple XML snippet constructed with attributes and load it into a temp table - see 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 that does the equivalent of this in Oracle PL/SQL?

In Oracle there is an DBMS_XMLSTORE package but it wants the XML snippet in a specific canonical format using ROWSET and ROW elements. DBMS_XMLSTORE does not appear to work with XML attributes.

Also, I am not 100% sure if I need to create an XSD of my XML snippet and register that on the Oracle database before I can use any of the other PL/SQL XML tools/queries.

Thanks!

Answer 1

Oracle's XML DB implementation has a frankly bewildering number of options, and it is not always clear (at least to me) which one applies in any given scenario. In this particular case the one you want is XMLTable(), which turns an XQuery into a set of rows.

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 populate 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 we prove it worked....

SQL> select * from t23
  2  /

   FIELD01    FIELD02 F
---------- ---------- -
       130       1700 C
       131       1701 C
       132       1702 C

SQL>