Hi Tom,
I want to get the values for INQORDERSTAT_SYS in different rows.Right now i'm getting only the first value (ADS).Can you please help?
<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Body xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<inqorderstatus:msg_RESPONSE xmlns:inqorderstatus="http://www.abc.com/OrderStatus" xmlns:abc="http://www.abc.com/9" xmlns:abcws="http://www.abc.com">
<NS2:INQORDERSTAT_RESPONSE_MESSAGE xmlns:NS2="http://www.abc.com/OrderStatus">
<NS2:INQORDERSTAT_INFO>
<NS2:INQORDERSTAT_SYS>ADS</NS2:INQORDERSTAT_SYS>
</NS2:INQORDERSTAT_INFO>
<NS2:INQORDERSTAT_INFO>
<NS2:INQORDERSTAT_SYS>OUS</NS2:INQORDERSTAT_SYS>
</NS2:INQORDERSTAT_INFO>
</NS2:INQORDERSTAT_RESPONSE_MESSAGE>
</inqorderstatus:msg_RESPONSE>
</soapenv:Body>
And how exactly are you getting the value?
To extract attributes with the same name as rows, you can use XMLTable.
This searches the path you supply. Then returns the values of the elements matching paths in the COLUMNS clause:
with x as (
select xmltype (
'<?xml version="1.0" encoding="UTF-8"?>
<Body xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" >
<msg_RESPONSE xmlns:inqorderstatus="http://www.abc.com/OrderStatus" xmlns:abc="http://www.abc.com/9" xmlns:abcws="http://www.abc.com" >
<INQORDERSTAT_RESPONSE_MESSAGE xmlns:NS2="http://www.abc.com/OrderStatus" >
<INQORDERSTAT_INFO>
<INQORDERSTAT_SYS>ADS</INQORDERSTAT_SYS>
</INQORDERSTAT_INFO>
<INQORDERSTAT_INFO>
<INQORDERSTAT_SYS>OUS</INQORDERSTAT_SYS>
</INQORDERSTAT_INFO>
</INQORDERSTAT_RESPONSE_MESSAGE>
</msg_RESPONSE>
</Body>'
)
doc
from dual
)
select inqorderstat_sys
from x x, xmltable (
xmlnamespaces (
'http://schemas.xmlsoap.org/soap/envelope/' as "soapenv",
'http://www.abc.com/OrderStatus' as "inqorderstatus",
'http://www.abc.com/9' as "abc",
'http://www.abc.com' as "abcws",
'http://www.abc.com/OrderStatus' as "NS2"
),
'/Body/msg_RESPONSE/INQORDERSTAT_RESPONSE_MESSAGE/INQORDERSTAT_INFO'
passing x.doc
columns
INQORDERSTAT_SYS varchar2(10) path 'INQORDERSTAT_SYS'
);
INQORDERSTAT_SYS
ADS
OUS