This content has been marked as final. Show 2 replies
You're close. You just need a correct XPath expression.
The part between square brackets is an XPath predicate, it restricts the selected ReferenceNumber elements to those having a child ReferenceNumberType = "PURCHASE_ORDER_NUMBER", then the rest of the expression selects the child named ReferenceNumber (assuming it's not an error).
SELECT x.* FROM invoice t , XMLTable( '$i/Invoice' passing i.doc as "i" columns invoice_number number path 'InvoiceHeader/invoiceNumber' , po_number number path 'ReferenceNumbers/ReferenceNumber[ReferenceNumberType="PURCHASE_ORDER_NUMBER"]/ReferenceNumber' ) x /