Hi, here is the xml stored :
<?xml version="1.0" encoding="UTF-8"?>
<InvoiceTransmission xmlns="http://www.IATA.com/IATAAviationInvoiceStandard" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.IATA.com/IATAAviationInvoiceStandard
http://www.iata.org/whatwedo/finance/clearing/sis/Documents/schemas/IATA_IS_XML_Invoice_Standard_V3.0.xsd">
<TransmissionHeader>
<TransmissionDateTime>2001-12-17T09:30:47Z</TransmissionDateTime>
<Version>IATA:ISXMLInvoiceV3.0</Version>
<IssuingOrganizationID>0075</IssuingOrganizationID>
<BillingCategory>Cargo</BillingCategory>
</TransmissionHeader>
....
</InvoiceTransmission>
Here is the SQL statement that I run to get "TransmissionHeader" infos :
SELECT a.TransmissionDateTime,a.Version, a.IssuingOrganizationID,a.ReceivingOrganizationID,a.BillingCategory
FROM XMLT,
XMLTABLE('/InvoiceTransmission/TransmissionHeader'
PASSING XMLT.data COLUMNS
TransmissionDateTime VARCHAR2(50) PATH 'TransmissionDateTime',
IssuingOrganizationID VARCHAR2(25) PATH 'IssuingOrganizationID',
ReceivingOrganizationID VARCHAR2(25) PATH 'ReceivingOrganizationID',
Version VARCHAR2(50) PATH 'Version',
BillingCategory VARCHAR2(25) PATH 'BillingCategory'
)a;
The problem is that I have no rows returned ! But when I delete the attributes :
xmlns="http://www.IATA.com/IATAAviationInvoiceStandard" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.IATA.com/IATAAviationInvoiceStandard
http://www.iata.org/whatwedo/finance/clearing/sis/Documents/schemas/IATA_IS_XML_Invoice_Standard_V3.0.xsd"
from InvoiceTransmission element, I have all infos that I need.
Can someone please tell me how can I correct my SQL statement to have the same result because I'm not supposed to delete attributes and not supposed to know them.
Thanks for your help.
[Updated on: Fri, 15 October 2010 02:21]
Report message to a moderator