Sunday 14 October 2012

Parsing SOAP response with Oracle PL/SQL

 

About 2 years ago I posted “how to invoke web-service from PL/SQL” post Post is here.

Since then the technology has changed and I will show a new technique to parse SOAP response with Oracle XMLTable.

So, suppose you got the following response from the server

clip_image002

 

And you want to show “Value” attribute of each Lov node.

If you check my previous post you will find that result XML is stoted into CLOB object.

Lets assume that I put it into a table called my_clob_table into a column named “xml”.

The following SQL will return the desired result

select VAL
from my_clob_table,
xmltable(xmlnamespaces(
--namespaces that are defined in
'http://schemas.xmlsoap.org/soap/envelope/' as "soap",
'http://siebel.com/TevaOfflineBatchSync/Order' as "ns"
),
--XPath to specific node
'//ns:QueryEntity_Output/ns:QueryResult/ns:Lov' passing
--XPath from above should work on my_clob_table.xml
--which holds XML text inside CLOB column
XMLType(my_clob_table.xml)
columns VAL
varchar2(1000) PATH '@Value'
) header_data

Maybe it looks complicated, but it is really very simple. In the from clause you put your “my_clob_table” (it is obvious because you want to parse the data in “xml” column).


Next you create a result set (similar to ref cursor), by using preserved function xmltable.


It get several parameters.


You need to provide namespaces used in the source XML. In case of SOAP message it will always be at least 'http://schemas.xmlsoap.org/soap/envelope/' and I also have my custom namespace 'http://siebel.com/TevaOfflineBatchSync/Order'. It is very important to give them alias so you can refer them later in Xpath expression.


Next you specify the Xpath expression that will return a result node set from the source XML.


How do you connect it XML ? Note the key word “passing”  and immediately after it we access XML data which is stored as CLOB in “XML” column in “my_clob_table” table. Note also that we convert it implicitly to XMLType by XMLType(my_clob_table.xml)


Now we need to define what data do we want to present as column in the result set.


You do it by specifying columns key word.


In my case I wanted to present column named “VAL” and the value that I want this column to have is accessed by Xpath expression which specified just after a keyword PATH. In my case it is the value of “Value” attribute (Note the source XML structure in the attached image)

No comments:

Post a Comment