By: Arif Khan
Hi there. In one of my blog you learned about parsing simple XML by Oracle Query. In this blog you will learn about parsing the XML document that uses Namespaces.
Have a look at below example XML document having namespaces.
XML Example:
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Body>
<tfResponse xmlns="http://tempuri.org/">
<Result xmlns:a="http://schemas.datacontract.org/2004/07/example.DataContracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<a:Acct>Adward Jons</a:Acct>
<a:res>000000</a:res>
<a:name>example organisation</a:name>
<a:code>00111</a:code>
<a:city>L.A</a:city>
<a:resp>00</a:resp>
<a:msg>COMMIT</a:msg>
<a:ID>5231125</a:ID>
</Result>
</tfResponse>
</s:Body>
</s:Envelope>
Let’s discus what is what in above XML document.
As you can see some hyperlinks are there in above code. Let’s understand them first.
xmlns:s=http://schemas.xmlsoap.org/soap/envelope/ Above XML document is using Soap Envelop, if one is using soap envelop in XML document then it must be the outer most element in XML document, it also means that the above XML document is received against some request made over the internet to retrieve some data that is why it is wrapped in an envelope. Mostly SOAP envelopes refer to XML schema defined by the W3C (World Wide Web Consortium they develop WEB standards).
xmlns=http://tempuri.org/ Microsoft development products e.g: Visual Studio using it as a default namespace, it is also available for Under development XML Web Services. As each XML Web service needs unique namespace in order to distinguish from other services on the WEB. Developed and published XML Web services should use indefinite namespace. It just used as a wildcard and it’s all instances should be replaced with meaningful URI in core systems.
xmlns:a=http://schemas.datacontract.org/2004/07/example.DataContracts There are predefined sys XML schema collection in the sys relational schemas. Predefined schemas are reserved in it, any other user created XML schema collection can be access them. In XQuery there are meaningful prefixes used in these predefined schemas. Prefix xml is reserved.
xmlns:i=http://www.w3.org/2001/XMLSchema-instance This is a namespace used within XML Instance documents to provide additional data to the XML Parser that is processing it. It describes the attributes xsi:schemalocation, xsi:noSchemalocation, xsi:type and xsi:nil which the XML parser can use to assist it with validation.
As introduction of all namespaces has been given above. Now let’s write a query which will parse the XML tag data for you.
Oracle SQL Query:
SELECT TITLE,RES,NAME,CODE,CITY,RESP,MSG,ID
FROM
XMLTABLE (XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS "s",'http://schemas.datacontract.org/2004/07/example.DataContracts' As "a"),
'/s:Envelope/s:Body/*:tfResponse/*:Result'
PASSING XMLTYPE('
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Body>
<tfResponse xmlns="http://tempuri.org/">
<Result xmlns:a="http://schemas.datacontract.org/2004/07/example.DataContracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<a:Acct>Adward Jons</a:Acct>
<a:res>000000</a:res>
<a:name>example organisation</a:name>
<a:code>00111</a:code>
<a:city>L.A</a:city>
<a:resp>00</a:resp>
<a:msg>COMMIT</a:msg>
<a:ID>5231125</a:ID>
</Result>
</tfResponse>
</s:Body>
</s:Envelope>
')
COLUMNS
title PATH 'a:Acct',
res PATH 'a:res',
name PATH 'a:name',
code PATH 'a:code',
city PATH 'a:city',
resp PATH 'a:resp',
msg PATH 'a:msg',
id PATH 'a:ID');
Query output will be as below.
TITLE RES NAME CODE CITY RESP MSG ID
Adward Jons 000000 example organisation 00111 L.A 00 COMMIT 5231125
Hearing from you, will be my pleasure, If you have any comments or questions, please post below.
Enjoy and happy Coding.
