Parsing XML using T-SQL in SQL Server 2012

Question

I've parsed XML using namespaces in SQL before but I'm having a real bear with this one. I'm trying to extract the ip addresses from the xml but having no luck. I'm thinking it has something to do with the xsd namespace, but can't seem to find the right combination of namespace declaration and node pathing.

Here's a sample of the xml:

<obj xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:vim25" versionId="6.0" xsi:type="ArrayOfGuestStackInfo">
  <GuestStackInfo xsi:type="GuestStackInfo">
    <dnsConfig>
      <dhcp>false</dhcp>
      <hostName>SXVCUP05</hostName>
      <domainName>corp.dtcc.com</domainName>
      <ipAddress>172.18.18.13</ipAddress>
      <ipAddress>172.18.18.20</ipAddress>
      <ipAddress>172.22.43.132</ipAddress>
      <ipAddress>172.22.43.148</ipAddress>
      <ipAddress>172.22.37.68</ipAddress>
      <ipAddress>172.22.37.84</ipAddress>
      <searchDomain>corp.dtcc.com</searchDomain>
      <searchDomain>dtcc.com</searchDomain>
      <searchDomain>backup.dtcc.com</searchDomain>
    </dnsConfig>
    <ipRouteConfig>
      <ipRoute>
        <network>0.0.0.0</network>
        <prefixLength>0</prefixLength>
        <gateway>
          <ipAddress>172.28.224.1</ipAddress>
          <device>0</device>
        </gateway>
      </ipRoute>
      <ipRoute>
        <network>172.18.8.133</network>
        <prefixLength>32</prefixLength>
        <gateway>
          <ipAddress>172.28.224.10</ipAddress>
          <device>0</device>
        </gateway>
      </ipRoute>
      <ipRoute>
        <network>172.22.8.14</network>
        <prefixLength>32</prefixLength>
        <gateway>
          <ipAddress>172.28.224.10</ipAddress>
          <device>0</device>
        </gateway>
      </ipRoute>
      <ipRoute>
        <network>172.28.224.0</network>
        <prefixLength>24</prefixLength>
        <gateway>
          <device>0</device>
        </gateway>
      </ipRoute>
      <ipRoute>
        <network>172.28.224.112</network>
        <prefixLength>32</prefixLength>
        <gateway>
          <device>0</device>
        </gateway>
      </ipRoute>
      <ipRoute>
        <network>172.28.224.255</network>
        <prefixLength>32</prefixLength>
        <gateway>
          <device>0</device>
        </gateway>
      </ipRoute>
      <ipRoute>
        <network>224.0.0.0</network>
        <prefixLength>4</prefixLength>
        <gateway>
          <device>0</device>
        </gateway>
      </ipRoute>
      <ipRoute>
        <network>255.255.255.255</network>
        <prefixLength>32</prefixLength>
        <gateway>
          <device>0</device>
        </gateway>
      </ipRoute>
    </ipRouteConfig>
  </GuestStackInfo>
</obj>

Using the following code to parse the xml

WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema' as xsd)
    SELECT a.b.value('ipAddress[1]', 'varchar(100)') AS ipaddress
    FROM @sam_xml.nodes('/obj/GuestStackInfo/dnsConfig') a(b)

Thanks!

Answer 1

The values you are looking for are living in the default namespace xmlns. This is the only namespace you need to declare:

DECLARE @sam_xml XML=
N'<obj xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:vim25" versionId="6.0" xsi:type="ArrayOfGuestStackInfo">
  <GuestStackInfo xsi:type="GuestStackInfo">
    <dnsConfig>
      <dhcp>false</dhcp>
      <hostName>SXVCUP05</hostName>
      <domainName>corp.dtcc.com</domainName>
      <ipAddress>172.18.18.13</ipAddress>
      <ipAddress>172.18.18.20</ipAddress>
      <ipAddress>172.22.43.132</ipAddress>
      <ipAddress>172.22.43.148</ipAddress>
      <ipAddress>172.22.37.68</ipAddress>
      <ipAddress>172.22.37.84</ipAddress>
      <searchDomain>corp.dtcc.com</searchDomain>
      <searchDomain>dtcc.com</searchDomain>
      <searchDomain>backup.dtcc.com</searchDomain>
    </dnsConfig>
    <ipRouteConfig>
      <ipRoute>
        <network>0.0.0.0</network>
        <prefixLength>0</prefixLength>
        <gateway>
          <ipAddress>172.28.224.1</ipAddress>
          <device>0</device>
        </gateway>
      </ipRoute>
      <ipRoute>
        <network>172.18.8.133</network>
        <prefixLength>32</prefixLength>
        <gateway>
          <ipAddress>172.28.224.10</ipAddress>
          <device>0</device>
        </gateway>
      </ipRoute>
      <ipRoute>
        <network>172.22.8.14</network>
        <prefixLength>32</prefixLength>
        <gateway>
          <ipAddress>172.28.224.10</ipAddress>
          <device>0</device>
        </gateway>
      </ipRoute>
      <ipRoute>
        <network>172.28.224.0</network>
        <prefixLength>24</prefixLength>
        <gateway>
          <device>0</device>
        </gateway>
      </ipRoute>
      <ipRoute>
        <network>172.28.224.112</network>
        <prefixLength>32</prefixLength>
        <gateway>
          <device>0</device>
        </gateway>
      </ipRoute>
      <ipRoute>
        <network>172.28.224.255</network>
        <prefixLength>32</prefixLength>
        <gateway>
          <device>0</device>
        </gateway>
      </ipRoute>
      <ipRoute>
        <network>224.0.0.0</network>
        <prefixLength>4</prefixLength>
        <gateway>
          <device>0</device>
        </gateway>
      </ipRoute>
      <ipRoute>
        <network>255.255.255.255</network>
        <prefixLength>32</prefixLength>
        <gateway>
          <device>0</device>
        </gateway>
      </ipRoute>
    </ipRouteConfig>
  </GuestStackInfo>
</obj>';

--the query will use .nodes() to dive one level deeper then your query in order to get all IP-addresses from within <dnsConfig>:

WITH XMLNAMESPACES(DEFAULT 'urn:vim25')
SELECT a.b.value('text()[1]', 'varchar(100)') as ipaddress
FROM @sam_xml.nodes('/obj/GuestStackInfo/dnsConfig/ipAddress') a(b)