Trying to select some values from an XML with namespaces from a CLOB field in a table, in Oracle 10.2g, ends in
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in '/*/par:PartItemAlternateIdTypeCode'
More, replacing the prefixes of the namespaces with wildcard, rose same error.
The similar select upon an XML without namespace, works.
The error that appears in the path clause of the XMLTABLE, denote that Oracle 10.2 isn't able to deal with namespaces.
This works in Oracle 11 and above.. perhaps is a bug in 10.2g
Here is the query
create table efactura (efxml clob);
insert into efactura values (
'<mes:QueryPartItemRevisionResponseMessage xmlns:mes="http://xml.namespaces.test.com/im/dsp/services/managepartitem/message" xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
<par:PartItem xmlns:par="http://xml.namespaces.test.com/im/dsp/cdm/data/partitem">
<par:PartItemAlternateIdentifier>
<par:AlternateIdentifierSourceId>XYZ</par:AlternateIdentifierSourceId>
<par:PartItemAlternateIdTypeCode>Type Code1</par:PartItemAlternateIdTypeCode>
<par:PartItemAlternateIdVal/>
</par:PartItemAlternateIdentifier>
<par:PartItemAlternateIdentifier>
<par:AlternateIdentifierSourceId>XYZ</par:AlternateIdentifierSourceId>
<par:PartItemAlternateIdTypeCode>Type Code2</par:PartItemAlternateIdTypeCode>
<par:PartItemAlternateIdVal/>
</par:PartItemAlternateIdentifier>
<par:PartItemAlternateIdentifier>
<par:AlternateIdentifierSourceId>ABC</par:AlternateIdentifierSourceId>
<par:PartItemAlternateIdTypeCode>Type Code3</par:PartItemAlternateIdTypeCode>
<par:PartItemAlternateIdVal>00123456</par:PartItemAlternateIdVal>
</par:PartItemAlternateIdentifier>
</par:PartItem>
</mes:QueryPartItemRevisionResponseMessage>'
);
commit;
SELECT x.*
FROM efactura t,
XMLTable(
XMLNamespaces(
'http://xml.namespaces.test.com/im/dsp/services/managepartitem/message' AS "mes",
'http://schemas.xmlsoap.org/soap/envelope/' AS "env",
'http://xml.namespaces.test.com/im/dsp/cdm/data/partitem' AS "par"
),
'/mes:QueryPartItemRevisionResponseMessage/par:PartItem/par:PartItemAlternateIdentifier'
PASSING xmltype(t.efxml)
COLUMNS
attrType VARCHAR2(25) PATH 'par:PartItemAlternateIdTypeCode',
attrVal VARCHAR2(10) PATH 'par:AlternateIdentifierSourceId'
) x
;