SOAP服务XML响应解析问题：使用Oracle EXTRACTVALUE时遇ORA系列错误

Let's break down your issue and fix it step by step:

First, your errors ORA-31011: XML parsing failed and LPX-00601: Invalid token in: '//diffgr:diffgram/text()' stem from two key problems:

1. Incorrect XPath usage: The text() function in your XPath tries to pull plain text from the diffgr:diffgram node, but this node contains nested elements (NewDataSet, Tracking) — not raw text. This confuses the XML parser.
2. Missing namespace declaration: You only included the msdata namespace in your EXTRACTVALUE call, but didn't declare the diffgr namespace. Oracle can't resolve the diffgr prefix without it.

Also, an important heads-up: EXTRACTVALUE is deprecated in Oracle 12c and later. It’s designed to return a single scalar value, which makes it a poor fit for extracting multiple Tracking records from your XML. The modern, recommended approach is to use XMLTABLE, which converts XML elements into relational rows and columns.

Solution with XMLTABLE (Recommended) #

This query will extract all Tracking records into a clean, table-like format:

WITH xml_data AS (
    SELECT 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>') AS xml_doc
    FROM dual
)
SELECT x.rowId, x.awbNo, x."Date", x.Activity, x.Details, x.Location
FROM xml_data,
     XMLTABLE(
         XMLNAMESPACES(
             'http://schemas.xmlsoap.org/soap/envelope/' AS "soap",
             'http://track.smsaexpress.com/secom/' AS "secom",
             'urn:schemas-microsoft-com:xml-diffgram-v1' AS "diffgr"
         ),
         '//soap:Envelope/soap:Body/secom:getShipUpdatesResponse/secom:getShipUpdatesResult/diffgr:diffgram/NewDataSet/Tracking'
         PASSING xml_doc
         COLUMNS
             rowId NUMBER PATH 'rowId',
             awbNo VARCHAR2(50) PATH 'awbNo',
             "Date" VARCHAR2(50) PATH 'Date', -- Date is an Oracle reserved word, so wrap in quotes
             Activity VARCHAR2(100) PATH 'Activity',
             Details VARCHAR2(200) PATH 'Details',
             Location VARCHAR2(100) PATH 'Location'
     ) x;

Key details about this solution:

• XMLNAMESPACES: Declares all required namespaces (soap, secom, diffgr) so the XPath can correctly resolve prefixes.
• XPath: Directly navigates to each Tracking element in the XML hierarchy.
• XMLTABLE: Converts each Tracking element into a row, mapping child nodes to columns with appropriate data types.
• Reserved word handling: Date is an Oracle reserved word, so we wrap it in double quotes to avoid conflicts.

Alternative: Fixed EXTRACTVALUE Query (Not Recommended) #

If you’re stuck using an older Oracle version where XMLTABLE isn’t available, you can fix your original query. Note this will only return a single value (e.g., the first awbNo):

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/NewDataSet/Tracking[1]/awbNo',
           'xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"'
       ) AS first_awb_no
FROM dual;

Fixes in this query:

• Removed text() from the XPath, targeting the actual awbNo node instead of trying to pull text from the parent diffgram.
• Added the diffgr namespace declaration so Oracle recognizes the prefix.

Final Notes #

Always use XMLTABLE over EXTRACTVALUE for extracting multiple records or complex XML structures. It’s more flexible, maintainable, and aligns with Oracle’s current best practices.

内容的提问来源于stack exchange，提问作者Adeel Aslam