Converting XML Nodes to Table in SQL Server – Andrey Zavadskiy – SQL and .NET developer, trainer, speaker

Recently I’ve faced with a task to extract client names and phone numbers from XML document and return a table like a standard SELECT query. This can be made with a nodes() method.

This method shreds the XML document and extracts nodes from it. The description is available on MSDN. Let’s look at the example how it works.

DECLARE @xml XML;
SET @xml = '
  <Clients>
    <Client>
      <ClientID>12503</ClientID>
      <ClientName>Mark</ClientName>
      <Phone>
        <CountryCode>+1</CountryCode>
        <PhoneNumber>1234567890</PhoneNumber>
      </Phone>
      <Phone>
        <CountryCode>+49</CountryCode>
        <PhoneNumber>123123123</PhoneNumber>
      </Phone>
    </Client>
    <Client>
      <ClientID>146223</ClientID>
      <ClientName>Laura</ClientName>
      <Phone>
        <CountryCode>+55</CountryCode>
        <PhoneNumber>1122334455</PhoneNumber>
      </Phone>
    </Client>
  </Clients>';

SELECT
  n.query('.').value('(/Client/ClientName/node())[1]','nvarchar(max)') AS CLientName,
  n.query('.').value('(/Client/Phone/PhoneNumber/node())[1]','nvarchar(max)') AS FirstPhoneNumber,
  n.query('.') AS ContactXML
FROM @xml.nodes('/Clients/Client') a(n);

DECLARE @xml XML;

SET @xml = '

<Phone>

</Phone>

<Phone>

</Phone>

</Client>

<ClientName>Laura</ClientName>

<Phone>

</Phone>

</Client>

</Clients>';

SELECT

n.query('.').value('(/Client/ClientName/node())[1]','nvarchar(max)') AS CLientName,

n.query('.').value('(/Client/Phone/PhoneNumber/node())[1]','nvarchar(max)') AS FirstPhoneNumber,

n.query('.') AS ContactXML

FROM @xml.nodes('/Clients/Client') a(n);

The nodes() method extracts Client node from XML and makes 2 rows from which we can get values.

We can also extract phone numbers from Phone node. In this case we should use common table expression (CTE) to extract Client nodes, then we can shred the Phone nodes.

WITH Clients_CTE
AS
(
  SELECT
    x.query('.').value('(/Client/ClientName/node())[1]','nvarchar(max)') AS CLientName,
    x.query('.').value('(/Client/Phone/PhoneNumber/node())[1]','nvarchar(max)') AS FirstPhoneNumber,
    x.query('.') AS ClientXML
  FROM @xml.nodes('/Clients/Client') a(x)
)
SELECT
  ClientName,
  ph.CountryCode,
  ph.PhoneNumber,
  ph.PhoneXML
FROM Clients_CTE
CROSS APPLY (
  SELECT
    n.query('.').value('(/Phone/CountryCode/node())[1]','nvarchar(max)') AS CountryCode,
    n.query('.').value('(/Phone/PhoneNumber/node())[1]','nvarchar(max)') AS PhoneNumber,
    n.query('.') AS PhoneXML
  FROM ClientXML.nodes('/Client/Phone') p(n)
) ph;

WITH Clients_CTE

(

SELECT

x.query('.').value('(/Client/ClientName/node())[1]','nvarchar(max)') AS CLientName,

x.query('.').value('(/Client/Phone/PhoneNumber/node())[1]','nvarchar(max)') AS FirstPhoneNumber,

x.query('.') AS ClientXML

FROM @xml.nodes('/Clients/Client') a(x)

)

SELECT

ClientName,

ph.CountryCode,

ph.PhoneNumber,

ph.PhoneXML

FROM Clients_CTE

CROSS APPLY (

SELECT

n.query('.').value('(/Phone/CountryCode/node())[1]','nvarchar(max)') AS CountryCode,

n.query('.').value('(/Phone/PhoneNumber/node())[1]','nvarchar(max)') AS PhoneNumber,

n.query('.') AS PhoneXML

FROM ClientXML.nodes('/Client/Phone') p(n)

) ph;

The result is shown below.

Leave a Reply Cancel reply