parsing xml response and inserting into table

Adeel Aslam 0
Viewed 28

I am consuming a soap base service which is returning xml datset as response , here is the below sample response

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <soap:Body>
      <getShipUpdatesResponse xmlns="http://track.smsaexpress.com/secom/">
         <getShipUpdatesResult>
            <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
               <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
                  <xs:complexType>
                     <xs:choice minOccurs="0" maxOccurs="unbounded">
                        <xs:element name="Tracking">
                           <xs:complexType>
                              <xs:sequence>
                                 <xs:element name="rowId" type="xs:long" minOccurs="0"/>
                                 <xs:element name="awbNo" type="xs:string" minOccurs="0"/>
                                 <xs:element name="Date" type="xs:string" minOccurs="0"/>
                                 <xs:element name="Activity" type="xs:string" minOccurs="0"/>
                                 <xs:element name="Details" type="xs:string" minOccurs="0"/>
                                 <xs:element name="Location" type="xs:string" minOccurs="0"/>
                              </xs:sequence>
                           </xs:complexType>
                        </xs:element>
                     </xs:choice>
                  </xs:complexType>
               </xs:element>
            </xs:schema>
            <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
               <NewDataSet xmlns="">
                  <Tracking diffgr:id="Tracking1" msdata:rowOrder="0">
                     <rowId>99438814</rowId>
                     <awbNo>290012097109</awbNo>
                     <Date>12 Nov 2017 15:47</Date>
                     <Activity>DATA RECEIVED</Activity>
                     <Details>Online Data Submitted</Details>
                     <Location>Riyadh</Location>
                  </Tracking>
                  <Tracking diffgr:id="Tracking2" msdata:rowOrder="1">
                     <rowId>99438812</rowId>
                     <awbNo>290012097092</awbNo>
                     <Date>12 Nov 2017 15:47</Date>
                     <Activity>DATA RECEIVED</Activity>
                     <Details>Online Data Submitted</Details>
                     <Location>Riyadh</Location>
                  </Tracking>
               </NewDataSet>
            </diffgr:diffgram>
         </getShipUpdatesResult>
      </getShipUpdatesResponse>
   </soap:Body>
</soap:Envelope>

I am trying to parse the response via EXTRACTVALUE however i am facing below issue

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '//diffgr:diffgram/text()'

Below is the query i am trying to get all the values from Tracking element

SELECT EXTRACTVALUE(XMLTYPE('<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <soap:Body>
      <getShipUpdatesResponse xmlns="http://track.smsaexpress.com/secom/">
         <getShipUpdatesResult>
            <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
               <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
                  <xs:complexType>
                     <xs:choice minOccurs="0" maxOccurs="unbounded">
                        <xs:element name="Tracking">
                           <xs:complexType>
                              <xs:sequence>
                                 <xs:element name="rowId" type="xs:long" minOccurs="0"/>
                                 <xs:element name="awbNo" type="xs:string" minOccurs="0"/>
                                 <xs:element name="Date" type="xs:string" minOccurs="0"/>
                                 <xs:element name="Activity" type="xs:string" minOccurs="0"/>
                                 <xs:element name="Details" type="xs:string" minOccurs="0"/>
                                 <xs:element name="Location" type="xs:string" minOccurs="0"/>
                              </xs:sequence>
                           </xs:complexType>
                        </xs:element>
                     </xs:choice>
                  </xs:complexType>
               </xs:element>
            </xs:schema>
            <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
               <NewDataSet xmlns="">
                  <Tracking diffgr:id="Tracking1" msdata:rowOrder="0">
                     <rowId>99438814</rowId>
                     <awbNo>290012097109</awbNo>
                     <Date>12 Nov 2017 15:47</Date>
                     <Activity>DATA RECEIVED</Activity>
                     <Details>Online Data Submitted</Details>
                     <Location>Riyadh</Location>
                  </Tracking>
                  <Tracking diffgr:id="Tracking2" msdata:rowOrder="1">
                     <rowId>99438812</rowId>
                     <awbNo>290012097092</awbNo>
                     <Date>12 Nov 2017 15:47</Date>
                     <Activity>DATA RECEIVED</Activity>
                     <Details>Online Data Submitted</Details>
                     <Location>Riyadh</Location>
                  </Tracking>
               </NewDataSet>
            </diffgr:diffgram>
         </getShipUpdatesResult>
      </getShipUpdatesResponse>
   </soap:Body>
</soap:Envelope>'), '//diffgr:diffgram/text()','xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"') AS mydata
            FROM dual
oracle-apex plsql xml oracle
1 Answers

I see at least 2 issues here:

  1. You need the diffgr namespace declared in your namespace statement. You can add it along with the existing one, like

    'xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"'

  2. Your example diffgram nodes don't have any text subnodes. If they did, they'd look more like <diffgr:diffgram>Here's a text node</diffgr:diffgram>. Instead, they only contain XML nodes. So text() will be null. If you're trying to view the XML contents of the diffgram nodes, use EXTRACT instead. This example will extract all of the subnodes:

    select EXTRACT(XMLTYPE('...your xml...'), 
      '//diffgr:diffgram/*',
      'xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"'
    ) AS mydata
    FROM dual

Also, as a side note, Oracle has deprecated EXTRACT and EXTRACTVALUE, and recommends that you use XMLTABLE or one of their other functions instead. Just something to be aware of for the future.

kfinity0
Related