Read XML into PL/SQL table

Read XML into PL/SQL table

When you get passed an XML type variable and you need to split this into a PL/SQL table (or an actual table) you can use code like this.

First we have a cursor that queries my XML Type.


CURSOR C_XML_lines (cp_xml_lines XMLType)
     IS
     SELECT EXTRACTVALUE(VALUE(P),'/LINE/LINE_NUMBER/text()') AS LINE_NUMBER,
            EXTRACTVALUE(VALUE(P),'/LINE/ITEM_NUMBER/text()') AS ITEM_NUMBER,
            EXTRACTVALUE(VALUE(P),'/LINE/QUANTITY/text()') AS QUANTITY,
            EXTRACTVALUE(VALUE(P),'/LINE/ARTICLE/text()') AS ARTICLE,
            EXTRACTVALUE(VALUE(P),'/LINE/LIST_PRICE/text()') AS LIST_PRICE,
            EXTRACTVALUE(VALUE(P),'/LINE/UNIT_PRICE/text()') AS UNIT_PRICE,
            EXTRACTVALUE(VALUE(P),'/LINE/TOTAL_PRICE/text()') AS TOTAL_PRICE,
            EXTRACTVALUE(VALUE(P),'/LINE/TAX_PERC/text()') AS TAX_PERC,
            EXTRACTVALUE(VALUE(P),'/LINE/TAX_AMOUNT/text()') AS TAX_AMOUNT    FROM   TABLE(XMLSEQUENCE(EXTRACT(cp_xml_lines,'/LINES/LINE'))) P;

Then we look through our lines and put the lines in a PL/SQL table.

l_index :=1;
  l_orderlines_tbl.delete;

  <<lines>>
  FOR r in c_xml_lines (cp_xml_lines => l_xml_lines)
  loop

  l_orderlines_Tbl(l_index).line_number := to_number(r.line_number);
  l_orderlines_Tbl(l_index).item_number := trim (r.item_number);
  l_orderlines_Tbl(l_index).quantity    := to_number(r.quantity);
  l_orderlines_Tbl(l_index).article        := r.article;
  l_orderlines_Tbl(l_index).list_price  := to_number(r.list_price);
  l_orderlines_Tbl(l_index).unit_price  := to_number(r.unit_price);
  l_orderlines_Tbl(l_index).total_price := to_number(r.total_price);
  l_orderlines_Tbl(l_index).tax_perc    := to_number(r.tax_perc);
  l_orderlines_Tbl(l_index).tax_amount  := to_number(r.tax_amount);

  l_index := l_index+1;

END LOOP Lines;

But note ....
TABLE(XMLSequence.... and ExtractValue are deprecated functionality and were replaced in 10g by XMLTABLE functionality).

So our new query would be something like this

with myxmltable as (select xmltype('<?xml version="1.0" encoding="UTF-8"?>
    <order>
      <order_number>1</order_number>
   <cust_number>1001</cust_number>
   <customer_name>My Customer</customer_name>
   <lines>
     <line_number>1</line_number>
  <item_number>A1000</item_number>
  <item_description>Some test item</item_description> 
   </lines>
   <lines>
     <line_number>2</line_number>
  <item_number>B1000</item_number>
  <item_description>Some other test item</item_description> 
   </lines>
 </order>') xml
 from dual)
select  orders.order_number
,  orders.cust_number
,  orders.customer_name
,  lines.line_number
,  lines.item_number
,  lines.item_description
from  myxmltable t
,xmltable('/order'
                   passing t.xml
                  columns order_number    number     path './order_number'
                          ,cust_number     number     path './cust_number'
                          ,customer_name        varchar2(240)    path './customer_name'
                          ,lines      xmltype        path '.'
                  ) orders
,xmltable('/order/lines'
                   passing orders.lines
                  columns line_number    number     path './line_number'
                          ,item_number     varchar2(35)   path './item_number'
                          ,item_description     varchar2(240)    path './item_description'                         
                  ) lines
;