I need to consume SOAP api and extract values from response. I had no problems doing it with named fields, but this time response is array. This is example response:
<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<getDhcpForPortResponse
xmlns="urn:DHCPProv">
<soapenc:Array
soapenc:arrayType="soapenc:Array[2]"
xsi:type="soapenc:Array">
<item
soapenc:arrayType="xsd:string[5]"
xsi:type="soapenc:Array">
<item
xsi:type="xsd:string">
qbtp8482tv
</item>
<item
xsi:type="xsd:string">
172.28.223.117
</item>
<item
xsi:type="xsd:string">
bc644ba2501c
</item>
<item
xsi:type="xsd:string">
MA5600T_AMD-Vitez atm 0/13/0/49:0.40
</item>
<item
xsi:type="xsd:string"/>
</item>
<item
soapenc:arrayType="xsd:string[5]"
xsi:type="soapenc:Array">
<item
xsi:type="xsd:string">
qbtp8482tv
</item>
<item
xsi:type="xsd:string">
172.28.223.126
</item>
<item
xsi:type="xsd:string">
704fb8f3e4e1
</item>
<item
xsi:type="xsd:string">
MA5600T_AMD-Vitez atm 0/13/0/49:0.40
</item>
<item
xsi:type="xsd:string"/>
</item>
</soapenc:Array>
</getDhcpForPortResponse>
</soap:Body>
</soap:Envelope>
Is there a way to extract Array fields with EXTRACTVALUE or some other method? Thanks in advance!
EDIT: My soap wrapper returns this xml:
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<getDhcpForUsernameResponse xmlns="urn:DHCPProv">
<soapenc:Array soapenc:arrayType="soapenc:Array[2]" xsi:type="soapenc:Array">
<item soapenc:arrayType="xsd:string[5]" xsi:type="soapenc:Array">
<item xsi:type="xsd:string">qbtp8482tv</item>
<item xsi:type="xsd:string">172.28.223.117</item>
<item xsi:type="xsd:string">bc644ba2501c</item>
<item xsi:type="xsd:string">MA5600T_AMD-Vitez atm 0/13/0/49:0.40</item>
<item xsi:type="xsd:string"/>
</item>
<item soapenc:arrayType="xsd:string[5]" xsi:type="soapenc:Array">
<item xsi:type="xsd:string">qbtp8482tv</item>
<item xsi:type="xsd:string">172.28.223.126</item>
<item xsi:type="xsd:string">704fb8f3e4e1</item>
<item xsi:type="xsd:string">MA5600T_AMD-Vitez atm 0/13/0/49:0.40</item>
<item xsi:type="xsd:string"/>
</item>
</soapenc:Array>
</getDhcpForUsernameResponse>
</soap:Body>
</soap:Envelope>
But when I use that xml in select statement it does not get any values from it. What is the difference compared to the first one that I posted which was copied from wireshark?
Your XML is incomplete; assuming you really still have the SOAP envelope and body around that, you can use XMLTable with XMLNamespaces, as shown before (and extractvalue is still deprecated):
select item
from XMLTable(
XMLNamespaces (
default 'urn:DHCPProv',
'http://schemas.xmlsoap.org/soap/envelope/' as "soap",
'http://schemas.xmlsoap.org/soap/encoding/' as "soapenc"
),
'/soap:Envelope/soap:Body/getDhcpForPortResponse/soapenc:Array/item/item'
passing XMLType(xml_string)
columns item varchar2(4000) path '.'
)
So with your data, and SOAP bits added:
select item
from XMLTable(
XMLNamespaces (
default 'urn:DHCPProv',
'http://schemas.xmlsoap.org/soap/envelope/' as "soap",
'http://schemas.xmlsoap.org/soap/encoding/' as "soapenc"
),
'/soap:Envelope/soap:Body/getDhcpForPortResponse/soapenc:Array/item/item'
passing XMLType('<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<getDhcpForPortResponse
xmlns="urn:DHCPProv">
<soapenc:Array
soapenc:arrayType="soapenc:Array[2]"
xsi:type="soapenc:Array">
<item
soapenc:arrayType="xsd:string[5]"
xsi:type="soapenc:Array">
<item
xsi:type="xsd:string">
qbtp8482tv
</item>
<item
xsi:type="xsd:string">
111.11.111.111
</item>
<item
xsi:type="xsd:string">
bc644ba2501c
</item>
<item
xsi:type="xsd:string">
MF5601T_AMD-NDF
</item>
<item
xsi:type="xsd:string"/>
</item>
<item
soapenc:arrayType="xsd:string[5]"
xsi:type="soapenc:Array">
<item
xsi:type="xsd:string">
qbtp8482tv
</item>
<item
xsi:type="xsd:string">
222.22.222.222
</item>
<item
xsi:type="xsd:string">
704fb8f3e4e1
</item>
<item
xsi:type="xsd:string">
MF5601T_AMD-NDF
</item>
<item
xsi:type="xsd:string"/>
</item>
</soapenc:Array>
</getDhcpForPortResponse>
</soap:Body>
</soap:Envelope>')
columns item varchar2(4000) path '.'
)
which gets:
ITEM
--------------------------------------------------
qbtp8482tv
111.11.111.111
bc644ba2501c
MF5601T_AMD-NDF
(null)
qbtp8482tv
222.22.222.222
704fb8f3e4e1
MF5601T_AMD-NDF
(null)
which is a bit of mess because of the newlines and leading spaces in your example. If those really exist you can trim them off:
select rtrim(ltrim(item, chr(32)||chr(10)), chr(10)||chr(32)) as item
from XMLTable...
which gives:
ITEM
--------------------
qbtp8482tv
111.11.111.111
bc644ba2501c
MF5601T_AMD-NDF
(null)
qbtp8482tv
222.22.222.222
704fb8f3e4e1
MF5601T_AMD-NDF
(null)
And you can exclude the null values if you don't want those.
If you don't have the SOAP wrapper then it's more complicated; you can use wildcards in the XPath but the XML will be invalid and won't parse properly, so you would need to remove the namespace prefixes, or add the wrapper back in. Based on your earlier question I don't think that is the case though.