Formatting XML with For XML in SQL Server

Question

I've changed to using XML PATH now which gives better results, but still not perfect.

SELECT TOP 5
    CT.ID AS [ID]
    ,CT.TITLE AS [Title]
    ,CT.TELEPHONE AS [Tel]
    ,AD.LINE1 AS [Addresses/Address/Line1]
FROM CONTACT CT
INNER JOIN METADATA MD ON CT.CONTACTID = MD.OWNERID
INNER JOIN ADDRESS AD ON MD.TOOWNERID = AD.ADDRESSID
WHERE CT.ID IS NOT NULL
FOR XML PATH ('ContactDetails'), root ('LeanerData');

Results in multiple Contact nodes as seen here ID 539091 is repeated twice :

<Records>
  <Contact>
    <ID>535317</ID>
    <Tel>7859243561</Tel>
    <Home>1</Home>
    <Addresses>
      <Address>
        <Line1>Address 1</Line1>
      </Address>
    </Addresses>
  </Contact>
  <Contact>
    <ID>539091</ID>
    <Tel>9876543231</Tel>
    <Home>0</MobileTel>
    <Addresses>
      <Address>
        <Line1>Address 3</Line1>
      </Address>
    </Addresses>
  </Contact>
  <Contact>
    <ID>539091</ID>
    <Tel>9876543231</Tel>
    <Home>0</MobileTel>
    <Addresses>
      <Address>
        <Line1>Address 4</Line1>
      </Address>
    </Addresses>
  </Contact>
</Records>

I am trying to get this particular format.

<Records>
  <Contact>
    <ID>535317</ID>
    <Tel>7859243561</Tel>
    <Home>1</Home>
    <Addresses>
      <Address>
        <Line1>Address 1</Line1>
      </Address>
    </Addresses>
  </Contact>
  <Contact>
    <ID>539091</ID>
    <Tel>9876543231</Tel>
    <Home>0</MobileTel>
    <Addresses>
      <Address>
        <Line1>Address 3</Line1>
      </Address>
      <Address>
        <Line1>Address 4</Line1>
      </Address>
    </Addresses>
  </Contact>
</Records>

Which version of SQL Server are you using? FOR XML PATH is generally much easier to use if it's available to you. — John Saunders
– John Saunders, Commented Sep 15, 2011 at 14:51
I'm using SQL 2008 R2. I will research FOR XML PATH, I've not used that yet as EXPLICIT, ROOT has usually done my schema's okay in the past. — Elarys
– Elarys, Commented Sep 15, 2011 at 14:54
You'll be shocked at how much easier it is, especially if you use FOR XML PATH,TYPE. EXPLICIT is the worst way to generate XML. — John Saunders
– John Saunders, Commented Sep 15, 2011 at 14:55
I thought I had it worked out. But it turns out, that now I get multiple entries of contact per address, instead of multiple addresses inside the contact. — Elarys
– Elarys, Commented Sep 15, 2011 at 15:49

Mikael Eriksson · Accepted Answer · 2011-09-15 16:34:30Z

2

You need an order by to get the rows in order before it is converted to xml. Try this.

SELECT TOP 5
    1 AS TAG
    ,0 AS PARENT
    ,CT.ID [Contact!1!ID!ELEMENT]
    ,CT.TELEPHONE [Contact!1!Tel!ELEMENT]
    ,CT.TEL [Contact!1!Home!ELEMENT]
    ,NULL [Addresses!2]
    ,NULL [Address!3!Line1!ELEMENT]
FROM CONTACT CT
UNION
SELECT
    2 AS TAG
    ,1 AS PARENT
    ,CT.ID [Contact!1!ID!ELEMENT]
    ,NULL [Contact!1!Tel!ELEMENT]
    ,NULL [Contact!1!Home!ELEMENT]
    ,NULL [Addresses!2]
    ,NULL [Address!3!Line1!ELEMENT]
FROM CONTACT CT
INNER JOIN METADATA MD ON CT.CONTACTID =MD.OWNERID
INNER JOIN ADDRESS AD ON MD.TOOWNERID = AD.ADDRESSID
UNION
SELECT
    3 AS TAG
    ,2 AS PARENT
    ,CT.ID [Contact!1!ID!ELEMENT]
    ,NULL [Contact!1!Tel!ELEMENT]
    ,NULL [Contact!1!Home!ELEMENT]
    ,NULL [Addresses!2]
    ,AD.LINE1 [Address!3!Line1!ELEMENT]
FROM CONTACT CT
INNER JOIN METADATA MD ON CT.CONTACTID = MD.OWNERID
INNER JOIN ADDRESS AD ON MD.TOOWNERID = AD.ADDRESSID
ORDER BY [Contact!1!ID!ELEMENT]
FOR XML EXPLICIT, ROOT('Records')

A version using for xml path:

select C.ID,
       C.TELEPHONE as Tel,
       C.TEL as Home,
       (select A.Line1
        from Metadata as M
          inner join Address as A
            on M.ToOwnerID = A.AddressID
        where M.OwnerID = C.ContactID 
        for xml path('Address'), root('Addresses'), type)
from Contact as C
for xml path('Contact'), root('Records')

edited Sep 15, 2011 at 16:34

answered Sep 15, 2011 at 15:17

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Elarys Over a year ago

I've decided to go down the XML PATH route now, however I'm still having some issues with it. I'll edit my original question to my new code.

Elarys Over a year ago

Mikael, thank you for that XML PATH example. I now fully understand how to get multiple childs nodes using this method. I can see how XML PATH can be both an easier and more powerful way to generate XML.

Mikael Eriksson Over a year ago

@Elarys - Yes it is easier. But since the nesting is done with correlated sub-queries there might be situations where XML EXPLICIT is necessary for performance reasons.

Elarys Over a year ago

@Mikael I will keep that in mind, performance should always be in one's mind. Once again thank you.

Collectives™ on Stack Overflow

Formatting XML with For XML in SQL Server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related