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!

All children of <obj...> are in the namespace xmlns="urn:vim25". — zx485
– zx485, Commented Aug 17, 2017 at 17:45

Gottfried Lesigang · Accepted Answer · 2017-08-17 17:47:00Z

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)

Collectives™ on Stack Overflow

Parsing XML using T-SQL in SQL Server 2012

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related