1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Extracting list data from XML CLOB

Discussion in 'SQL PL/SQL' started by baggettms01, Oct 27, 2013.

  1. baggettms01

    baggettms01 Guest

    Our company database is Oracle based and we use SQL Developer to pull out needed data.

    Using a snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query (and create/insert statements, to build a sample table) as it stands right now is below.

    Code (SQL):

    CREATE TABLE ws_transactions
      (Wtr_Service_Tag VARCHAR(11),
       Wtr_Tran_Origin CHAR(1),
       Wtr_Send_Date DATE,
       Wtr_Receive_Date DATE,
       Wtr_Req_XML CLOB
      );

    INSERT ALL
      INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('12345678901','D',TO_DATE('10/12/13', 'mm/dd/yy'), TO_DATE('12/10/13', 'mm/dd/yy'),'<MessageEnvelope><PayloadList><PayloadGroup Primary="1"><Payload Type="WorkOrderChange" Sequence="1"><Message><WorkOrderChange><MessageHeader><MessageID>e495e616-3762-48ea-8ab5-7816b8b02b30</MessageID><MessageTimeStamp>2013-10-26T08:02:26.370-05:00</MessageTimeStamp><SenderID>SENDER</SenderID><ReceiverID>RECEIVER</ReceiverID><MessageType>WOChange</MessageType><CorrelationID/></MessageHeader><OrderID>12345678901</OrderID><RequestTimeStamp>2013-10-26T08:02:26.370-05:00</RequestTimeStamp><RequestedChange>RELEASE</RequestedChange><OrderPriority/><ShippingInformation><ShipCode/><ServiceLevel/><TransportationMode/><CarrierCode/><CarrierAccount/><FOBCode/><FreightTerms/><CommercialResidential/></ShippingInformation><ShipToFacilityID/><ShipToLocation><CompanyName/><ContactName/><ContactPhone/><ContactEmail/><Address><Line1/><Line2/><Line3/><City/><State/><CountryCode/><PostalCode/></Address></ShipToLocation><DeliveryDate nil="true"/><Instructions><ShippingInstruction/><PackingInstruction/></Instructions></WorkOrderChange></Message></Payload><Payload Type="CSMOrderAttachment" Sequence="2"><Message><CFIDATA><SERVICELIST><SERVICE TYPE="SYSTEM"><ORDER NUMBER="123456789" TIE="1" QUANTITY="2"><ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">ABC1234</VALUE><VALUE SID="2">CBA4321</VALUE></LIST></ASSETTAGDATA></ORDER></SERVICE></SERVICELIST></CFIDATA></Message></Payload></PayloadGroup></PayloadList></MessageEnvelope>')
      INTO ws_transactions (Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, Wtr_Req_XML) VALUES ('23456789012','D',TO_DATE('10/13/13', 'mm/dd/yy'), TO_DATE('10/14/13', 'mm/dd/yy'),'<MessageEnvelope><PayloadList><PayloadGroup Primary="1"><Payload Type="WorkOrderChange" Sequence="1"><Message><WorkOrderChange><MessageHeader><MessageID>e495e616-3762-48ea-8ab5-7816b8b02b30</MessageID><MessageTimeStamp>2013-10-26T08:02:26.370-05:00</MessageTimeStamp><SenderID>SENDER</SenderID><ReceiverID>RECEIVER</ReceiverID><MessageType>WOChange</MessageType><CorrelationID/></MessageHeader><OrderID>12345678901</OrderID><RequestTimeStamp>2013-10-26T08:02:26.370-05:00</RequestTimeStamp><RequestedChange>RELEASE</RequestedChange><OrderPriority/><ShippingInformation><ShipCode/><ServiceLevel/><TransportationMode/><CarrierCode/><CarrierAccount/><FOBCode/><FreightTerms/><CommercialResidential/></ShippingInformation><ShipToFacilityID/><ShipToLocation><CompanyName/><ContactName/><ContactPhone/><ContactEmail/><Address><Line1/><Line2/><Line3/><City/><State/><CountryCode/><PostalCode/></Address></ShipToLocation><DeliveryDate nil="true"/><Instructions><ShippingInstruction/><PackingInstruction/></Instructions></WorkOrderChange></Message></Payload><Payload Type="CSMOrderAttachment" Sequence="2"><Message><CFIDATA><SERVICELIST><SERVICE TYPE="SYSTEM"><ORDER NUMBER="123456789" TIE="1" QUANTITY="3"><ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">12XYZ34567</VALUE><VALUE SID="2">12WXY34567</VALUE><VALUE SID="3">112VWX34567</VALUE></LIST></ASSETTAGDATA></ORDER></SERVICE></SERVICELIST></CFIDATA></Message></Payload></PayloadGroup></PayloadList></MessageEnvelope>')

    SELECT Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
    FROM ws_transactions
    WHERE Wtr_Service_Tag IN ('20458749610')
      AND Wtr_Req_Xml LIKE ('%CSM%')
    ORDER BY Wtr_Receive_Date DESC
    This query is only able to pull the first value in the list.

    I have two things that I am hoping I can get some help with.

    1. How can I edit this query to pull all of the list items when there are more than 1?
    2. This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths.

    Below is the sort of output I would hope to receive. It would obvously vary depending on what value was placed in the where statement

    Code (Text):
    Wtr_Service_Tag    Wtr_Tran_Origin    Wtr_Send_Date    Wtr_Receive Date    Asset_Tag
    -------------------------------------------------------------------------------------------------------------
    12345678901        D                  12-Oct-13        12-Oct-13           186037, 186038
    Thank you in advance for your help. I hope I have provided enough information. If have I have not, I will gladly respond back with anything else that is needed.

    -Matt
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    For a start : Oracle® XML DB Developer's Guide http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb01int.htm#ADXDB0100


    Simple example:

    Code (SQL):

    WITH
    my_table AS
    (
    SELECT 1 id ,xmltype.createxml('<MessageEnvelope><PayloadList><PayloadGroup Primary="1"><Payload Type="WorkOrderChange" Sequence="1"><Message><WorkOrderChange><MessageHeader><MessageID>e495e616-3762-48ea-8ab5-7816b8b02b30</MessageID><MessageTimeStamp>2013-10-26T08:02:26.370-05:00</MessageTimeStamp><SenderID>SENDER</SenderID><ReceiverID>RECEIVER</ReceiverID><MessageType>WOChange</MessageType><CorrelationID/></MessageHeader><OrderID>12345678901</OrderID><RequestTimeStamp>2013-10-26T08:02:26.370-05:00</RequestTimeStamp><RequestedChange>RELEASE</RequestedChange><OrderPriority/><ShippingInformation><ShipCode/><ServiceLevel/><TransportationMode/><CarrierCode/><CarrierAccount/><FOBCode/><FreightTerms/><CommercialResidential/></ShippingInformation><ShipToFacilityID/><ShipToLocation><CompanyName/><ContactName/><ContactPhone/><ContactEmail/><Address><Line1/><Line2/><Line3/><City/><State/><CountryCode/><PostalCode/></Address></ShipToLocation><DeliveryDate nil="true"/><Instructions><ShippingInstruction/><PackingInstruction/></Instructions></WorkOrderChange></Message></Payload><Payload Type="CSMOrderAttachment" Sequence="2"><Message><CFIDATA><SERVICELIST><SERVICE TYPE="SYSTEM"><ORDER NUMBER="123456789" TIE="1" QUANTITY="2"><ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">ABC1234</VALUE><VALUE SID="2">CBA4321</VALUE></LIST></ASSETTAGDATA></ORDER></SERVICE></SERVICELIST></CFIDATA></Message></Payload></PayloadGroup></PayloadList></MessageEnvelope>') my_xml FROM dual
    UNION ALL
    SELECT 2  ,xmltype.createxml('<MessageEnvelope><PayloadList><PayloadGroup Primary="1"><Payload Type="WorkOrderChange" Sequence="1"><Message><WorkOrderChange><MessageHeader><MessageID>e495e616-3762-48ea-8ab5-7816b8b02b30</MessageID><MessageTimeStamp>2013-10-26T08:02:26.370-05:00</MessageTimeStamp><SenderID>SENDER</SenderID><ReceiverID>RECEIVER</ReceiverID><MessageType>WOChange</MessageType><CorrelationID/></MessageHeader><OrderID>12345678901</OrderID><RequestTimeStamp>2013-10-26T08:02:26.370-05:00</RequestTimeStamp><RequestedChange>RELEASE</RequestedChange><OrderPriority/><ShippingInformation><ShipCode/><ServiceLevel/><TransportationMode/><CarrierCode/><CarrierAccount/><FOBCode/><FreightTerms/><CommercialResidential/></ShippingInformation><ShipToFacilityID/><ShipToLocation><CompanyName/><ContactName/><ContactPhone/><ContactEmail/><Address><Line1/><Line2/><Line3/><City/><State/><CountryCode/><PostalCode/></Address></ShipToLocation><DeliveryDate nil="true"/><Instructions><ShippingInstruction/><PackingInstruction/></Instructions></WorkOrderChange></Message></Payload><Payload Type="CSMOrderAttachment" Sequence="2"><Message><CFIDATA><SERVICELIST><SERVICE TYPE="SYSTEM"><ORDER NUMBER="123456789" TIE="1" QUANTITY="3"><ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">12XYZ34567</VALUE><VALUE SID="2">12WXY34567</VALUE><VALUE SID="3">112VWX34567</VALUE></LIST></ASSETTAGDATA></ORDER></SERVICE></SERVICELIST></CFIDATA></Message></Payload></PayloadGroup></PayloadList></MessageEnvelope>')  FROM dual
    )
    SELECT * FROM my_table mt ,
    xmltable(
                     'for $i in /MessageEnvelope/PayloadList/PayloadGroup/Payload/Message/CFIDATA/SERVICELIST/SERVICE/ORDER/ASSETTAGDATA
                      return $i /LIST/VALUE'

                      passing mt.my_xml
                      --
                      COLUMNS col2  varchar2(100) path '/VALUE',
                              col1  varchar2(100)       path '/VALUE/@SID'
                     ) z

     

    Sample example 2 parsing xml :
    Code (SQL):
    SELECT  rownum,
            ExtractValue(VALUE(t),'/FilterParamItem') col1,
            ExtractValue(VALUE(t),'/FilterParamItem/@Value') col2,
            VALUE(t).getStringVal() sv
                       
    FROM TABLE(xmlsequence(EXTRACT(xmltype('<FilterParamItems>
                                                <FilterParamItem Value = "Test 1">
                                                  Item1
                                                 </FilterParamItem>
                                            </FilterParamItems>'
    ),
                                          '/FilterParamItems/FilterParamItem'))
                                    ) t

     


    If there are questions, it is possible to ask....

    p.s. For aggregation of lines through a separator it is possible to use the list_agg function.