Oracle 12c PLSQL SOAP EXTRACTVALUE from Array response

Question

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?

Answer 1

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)

db<>fiddle

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.