Extracting Data from Xml Using XMLTable Function

Question

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

Answer 1

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 *.