Home » Developer & Programmer » JDeveloper, Java & XML » How to extract namespace from XML document
How to extract namespace from XML document [message #479491] Mon, 18 October 2010 01:32 Go to next message
czinsou
Messages: 23
Registered: August 2009
Junior Member
Hi,
I'm trying to run xqueries on xmltable :
<?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>

So I write SQLs looks like :
SELECT a.TransmissionDateTime,a.Version, a.IssuingOrganizationID,a.ReceivingOrganizationID,a.BillingCategory
FROM XMLT,
XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.IATA.com/IATAAviationInvoiceStandard'),
   	'/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;
Result : 
TransmissionDateTime   IssuingOrganizationID    ReceivingOrganizationID   Version    BillingCategory
2001-12-17T09:30:47Z   IATA:ISXMLInvoiceV3.0     0075                      null      Cargo                    


This is correct, that's what I want, but when I run this query without namespace :
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;
Result:
No rows returned.

The problem is that I'm not supposed to know the namespaces of the xml file, so I'd like to know if there's a way
to ignore the namespaces in the query OR a way retreive the namespace dynamicly. Something like that :
SELECT a.TransmissionDateTime,a.Version, a.IssuingOrganizationID,a.ReceivingOrganizationID,a.BillingCategory
FROM XMLT,
XMLTABLE(XMLNAMESPACES(DEFAULT select extract(xmlns) from xmlt ),
   	'/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;


Thanks for your help.

[Updated on: Mon, 18 October 2010 01:33]

Report message to a moderator

Re: How to extract namespace from XML document [message #479494 is a reply to message #479491] Mon, 18 October 2010 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I told you many times:
Keep your lines in 80 character width.

Regards
Michel
Re: How to extract namespace from XML document [message #479507 is a reply to message #479491] Mon, 18 October 2010 02:11 Go to previous message
czinsou
Messages: 23
Registered: August 2009
Junior Member
Sorry for inconvenience
I'm trying to run xqueries on xmltable :
<?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>

So I write SQLs looks like :
SELECT a.TransmissionDateTime,
a.IssuingID,
a.ReceivingID,
a.BillingCategory
FROM XMLT,
XMLTABLE
(XMLNAMESPACES(DEFAULT 'http://www.IATA.com/IATAAviationInvoiceStandard'),
'/InvoiceTransmission/TransmissionHeader'
PASSING XMLT.data COLUMNS
TransmissionDateTime VARCHAR2(50) PATH 'TransmissionDateTime',
IssuingID VARCHAR2(25) PATH 'IssuingOrganizationID',
ReceivingID VARCHAR2(25) PATH 'ReceivingOrganizationID',
Version VARCHAR2(50) PATH 'Version',
BillingCategory VARCHAR2(25) PATH 'BillingCategory'
)a;
Result : 
TransmissionDateTime IssuingID             ReceivingID BillingCategory
2001-12-17T09:30:47Z IATA:ISXMLInvoiceV3.0  0075       Cargo   

This is correct, that's what I want,
but when I run this query without namespace :
SELECT a.TransmissionDateTime,
a.IssuingID,
a.ReceivingID,
a.BillingCategory
FROM XMLT,
XMLTABLE
('/InvoiceTransmission/TransmissionHeader'
PASSING XMLT.data COLUMNS
TransmissionDateTime VARCHAR2(50) PATH 'TransmissionDateTime',
IssuingID VARCHAR2(25) PATH 'IssuingOrganizationID',
ReceivingID VARCHAR2(25) PATH 'ReceivingOrganizationID',
Version VARCHAR2(50) PATH 'Version',
BillingCategory VARCHAR2(25) PATH 'BillingCategory'
)a;
Result :
No rows returned

The problem is that I'm not supposed to know the namespaces of the xml file,
so I'd like to know if there's a way
to ignore the namespaces in the query
OR a way to retreive the namespace dynamicly. Something like that :
SELECT a.TransmissionDateTime,
a.IssuingID,
a.ReceivingID,
a.BillingCategory
FROM XMLT,
XMLTABLE
(
XMLNAMESPACES(DEFAULT select extract(xmlns) from xmlt
'/InvoiceTransmission/TransmissionHeader'
PASSING XMLT.data COLUMNS
TransmissionDateTime VARCHAR2(50) PATH 'TransmissionDateTime',
IssuingID VARCHAR2(25) PATH 'IssuingOrganizationID',
ReceivingID VARCHAR2(25) PATH 'ReceivingOrganizationID',
Version VARCHAR2(50) PATH 'Version',
BillingCategory VARCHAR2(25) PATH 'BillingCategory'
)a;

Thanks a lot for your help.





Previous Topic: Using global temporary tables with java jdbc
Next Topic: I cannot extract xmlns value
Goto Forum:
  


Current Time: Thu Mar 28 06:59:40 CDT 2024