Solved

XML QUERY FROM CLOB

Posted on 2016-09-14
2
23 Views
Last Modified: 2016-10-03
Hi All,

I am looking to extract information from a CLOB that has XML data in it.

<ORDER FW="1005_201609120336807">
  <HEADER VENDOR_ID="222500" VENDOR_Name="COMPANY NATURAL" ORDER_NUM="156456465" ORDER_DATE="09/12/2016" SHIP_DATE="09/13/2016" Total_Cases="15" Total_Pieces="0" DETAILS="ORGANICS" DEPT="058" STORE="N/A" ACCOUNT="N/A" PO_Number="336807" Notes="N/A" BillTo_Name="FOODCOMPANY PARAMUS LLC" BillTo_Address="30 BOSTON AVE STORE #8, ROUTE 17" BillTo_City="CUTYONE" BillTo_State="AL" BillTo_Zip="07652" ShipTo_Name="FOODCOMPANY PARAMUS LLC" ShipTo_Address="30 BOSTON AVE STORE #8, ROUTE 17" ShipTo_City="CUTYONE" ShipTo_State="AL" ShipTo_Zip="07652">
    <DETAIL UPC="093966033660" ProductCode="2290" QTY="6" size="81" MEASURE="FOZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
    <DETAIL UPC="857682003054" ProductCode="3163" QTY="1" size="3.5" MEASURE="OZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
    <DETAIL UPC="857682333047" ProductCode="3162" QTY="1" size="3.5" MEASURE="OZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
    <DETAIL UPC="857682333016" ProductCode="3161" QTY="1" size="3.5" MEASURE="OZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
    <DETAIL UPC="857682333009" ProductCode="3160" QTY="1" size="3.5" MEASURE="OZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
    <DETAIL UPC="857682333153" ProductCode="3164" QTY="1" size="3.5" MEASURE="OZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
    <DETAIL UPC="857732332310" ProductCode="1330" QTY="2" size="14" MEASURE="FOZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
    <DETAIL UPC="8576820333122" ProductCode="3165" QTY="1" size="3.5" MEASURE="OZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
    <DETAIL UPC="093966335301" ProductCode="2803" QTY="1" size="11" MEASURE="FOZ" CASE="1" CasePack="12" SHIP_DATE="09/13/2016" />
  </HEADER>
</ORDER>

Open in new window


above is sample data


below are desired results
ORDER_NUM   |     PO_NUMBER     | ORDER_DATE | DELIVERY_DATE | BILLTO_CITY   | DEPT   | ProductCode | QTY
----------------------------------------------------------------------------------------------------------
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          2290        6
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          3163        1
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          3162        1
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          3161        1
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          3160        1
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          3164        1
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          1330        2
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          3165        1
156456465         336807          09/12/2016   09/13/2016       CUTYONE        058          2806        1

Open in new window

0
Comment
Question by:FutureDBA-
2 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points (awarded by participants)
ID: 41798564
SELECT x.order_num,
       x.po_number,
       x.order_date,
       x.delivery_date,
       x.billto_city,
       x.dept,
       y.product_code,
       y.qty
  FROM yourtable,
       XMLTABLE(
           '/ORDER/HEADER'
           PASSING xmltype(yourclob)
           COLUMNS order_num VARCHAR2(10) PATH '@ORDER_NUM',
                   po_number VARCHAR2(10) PATH '@PO_Number',
                   order_date VARCHAR2(10) PATH '@ORDER_DATE',
                   delivery_date VARCHAR2(10) PATH '@SHIP_DATE',
                   billto_city VARCHAR2(20) PATH '@BillTo_City',
                   dept VARCHAR2(3) PATH '@DEPT',
                   xml XMLTYPE PATH '.'
       ) x,
       XMLTABLE(
           '//DETAIL'
           PASSING x.xml
           COLUMNS product_code NUMBER PATH '@ProductCode', qty NUMBER PATH '@QTY'
       ) y
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now