Hi everyone,
I recently found out extractValue is deprecated and am trying to convert my code to XMLTABLE.
From examples I have found on this forum and other places I think I have the syntax right, but it doesn't return any values from the XMLTABLE call.
My version is: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
SELECT xt.po_num, xt.vendor,
extractValue(p_t.po_xml_data, '/PurchaseOrder/PO_Num', 'xmlns="http://tempuri.org/"')
FROM po_table p_t,
XMLTable(XMLNamespaces(default 'http://tempuri.org/'),
'/PurchaseOrder'
PASSING po_table.po_xml_data
COLUMNS
po_num VARCHAR2(12) PATH 'PO_Num',
vendor VARCHAR2(12) PATH 'PO_Vendor') xt
where p_t.po_xml_data_id = '366000000008143';
The "extractValue" returns the PO number, for that record. The other two columns are null.
The XML data in the XMLTYPE column po_xml_data in this record looks like this:
<PurchaseOrder xmlns="http://tempuri.org/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<PO_Num xmlns:ns4="http://tempuri.org/" ns4:IsFlagged="false">123454</PO_Num>
<PO_Vendor xmlns:ns4="http://tempuri.org/" ns4:IsFlagged="false">HP</PO_Vendor>
</PurchaseOrder>
I appreciate any help you can offer.
Thank you,
Jack