T-SQL XML result with Listnode and structure

Question

I have the following table structure:

Customers:

CustomerId  Name            City
1           Richie Rich     MyCity
2           Bernie Bertel   MyTown

Contacts:

ContactId   CustomerId  Name    Telephone
1           1           Test    123123

I want to get the result in an XML Structure like the following:

<Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Customers>
    <Name>Richie Rich</Name>
    <City>MyCity</City>
    <Contacts>
      <Contact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <Name>Test</Name>
        <Telephone>123123</Telephone>
      </Contact>
    </Contacts>
  </Customers>
  <Customers>
    <Name>Bernie Bertel</Name>
    <City>MyTown</City>
    <Contacts xsi:nil="true" />
  </Customers>
</Customers>

The corresponding T-SQL Query is:

SELECT 
    Name,
    City,
    (
        SELECT 
            Name,
            Telephone
        FROM Contacts
        WHERE (Customers.CustomerId = Contacts.CustomerId)
        FOR XML PATH ('Contact'), TYPE, ELEMENTS XSINIL
    ) AS Contacts
FROM Customers
FOR XML AUTO, ROOT('Customers'), TYPE, ELEMENTS XSINIL

For the further processing, I must know the structure of the list-nodes (Contact). So if the Customers have no Contacts (like in the second entry), I must know which fields/columns the Customer node has.

Does anyone know how to resolve this?

Rhys Jones · Accepted Answer · 2016-06-16 10:13:36Z

If I've understood you correctly then you will have to create a dummy row if no contacts exist;

SELECT 
    Name,
    City,
    (
        SELECT * FROM (
        SELECT 
            Name,
            Telephone
        FROM Contacts
        WHERE (Customers.CustomerId = Contacts.CustomerId)
        UNION ALL
        SELECT 
            NULL AS Name,
            NULL AS Telephone
        WHERE NOT EXISTS (SELECT 1 FROM Contacts WHERE Customers.CustomerId = Contacts.CustomerId)
        ) x
        FOR XML PATH ('Contact'), TYPE, ELEMENTS XSINIL
    ) AS Contacts
FROM Customers
FOR XML AUTO, ROOT('Customers'), TYPE, ELEMENTS XSINIL

Which produces this;

<Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Customers>
    <Name>Richie Rich</Name>
    <City>MyCity</City>
    <Contacts>
      <Contact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <Name>Test</Name>
        <Telephone>123123</Telephone>
      </Contact>
    </Contacts>
  </Customers>
  <Customers>
    <Name>Bernie Bertel</Name>
    <City>MyTown</City>
    <Contacts>
      <Contact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <Name xsi:nil="true" />
        <Telephone xsi:nil="true" />
      </Contact>
    </Contacts>
  </Customers>
</Customers>

Collectives™ on Stack Overflow

T-SQL XML result with Listnode and structure

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