xmloracleplsql

Extracting Data from Xml Using XMLTable Function


I have just started learning handling XML data in PLSQL , Here is my Problem.

Codes necessary to create tables used in this Questions.

 create table purchase_order 
(
data XMLType
);

insert into purchase_order
values(XMLType('<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:noNamespaceSchemaLocation=
    "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
  <Reference>SBELL-2002100912333601PDT</Reference>
  <Actions>
    <Action>
      <User>SVOLLMAN</User>
    </Action>
  </Actions>
  <Reject/>
  <Requestor>Sarah J. Bell</Requestor>
  <User>SBELL</User>
  <CostCenter>S30</CostCenter>
  <ShippingInstructions>
    <name>Sarah J. Bell</name>
    <address>400 Oracle Parkway
      Redwood Shores
      CA
      94065
      USA</address>
    <telephone>650 506 7400</telephone>
  </ShippingInstructions>
  <SpecialInstructions>Air Mail</SpecialInstructions>
  <LineItems>
    <LineItem ItemNumber="1">
      <Description>A Night to Remember</Description>
      <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="2">
      <Description>The Unbearable Lightness Of Being</Description>
      <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="3">
      <Description>Sisters</Description>
      <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
    </LineItem>
  </LineItems>
</PurchaseOrder>'));

I want to extract values like ItemNumber , Description, Part Id , UnitPrice, Quantity and show them as Relational Table. But I am getting the Error ORA-19279. Here is my Code.

select x.*
from purchase_order t,
xmltable('/PurchaseOrder'
passing t.data 
columns Reference varchar2(300) path 'Reference',
Usr varchar2(20) path '//Action',
Requestor varchar2(20) path '//Requestor',
CostCenter varchar2(20) path '//CostCenter',
ShippingInstructions varchar2(500) path '//ShippingInstructions',
SpecialInstructions varchar2(50) path '//SpecialInstructions',
ItemNumber varchar(10) path '//LineItems/LineItem/@ItemNumber',
Description varchar(100) path '//Description'

 ) x

Solution

  • You need to pass multi-item XML elements from one level into a second XMLTable() call:

    select x.Usr, -- other x columns, but not LineItems
      y.ItemNumber, y.Description
    from purchase_order t,
      xmltable('/PurchaseOrder'
        passing t.data 
        columns Reference varchar2(300) path 'Reference',
        Usr varchar2(20) path '//Action',
        Requestor varchar2(20) path '//Requestor',
        CostCenter varchar2(20) path '//CostCenter',
        ShippingInstructions varchar2(500) path '//ShippingInstructions',
        SpecialInstructions varchar2(50) path '//SpecialInstructions',
        LineItems XMLType path '//LineItems'
      ) x,
      xmltable('/LineItems/LineItem'
        passing x.LineItems
        columns ItemNumber varchar(10) path '//LineItem/@ItemNumber',
        Description varchar(100) path '//Description'
      ) y;
    
    USR                  ITEMNUMBER DESCRIPTION                             
    -------------------- ---------- ----------------------------------------
    SVOLLMAN             1          A Night to Remember                      
    SVOLLMAN             2          The Unbearable Lightness Of Being        
    SVOLLMAN             3          Sisters                                  
    

    I've only shown one column from x to prevent scrolling, but you can include all of them except the LineItems column passed on to the second XMLTable() call; so you can't use select *.