Formatting XML with For XML in SQL Server

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>
    <Ho开发者_开发问答me>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>

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')