Xml structure using For Xml in Sql Server

Question

I am trying to create a xml ouput for my table using this approach but it seems to duplicate multiple nodes for TopLevelItem .Ideally i was hoping the format would be like this :

<TopLevelItems>

  <TopLevelItem field1="1">
    <LowLevelItem fieldA="a" />
    <LowLevelItem fieldA="b" />
    <LowLevelItem fieldA="def" />
  </TopLevelItem>

  <TopLevelItem field1="2">
    <LowLevelItem fieldA="c" />
    <LowLevelItem fieldA="d" />
  </TopLevelItem>

</TopLevelItems>


DECLARE @sites TABLE (username varchar(50), ID INT, Name VARCHAR(50) )
INSERT  INTO @sites
VALUES  ('a', 1, 'a' ),
        ('a', 1, 'b' ),
        ('a', 2, 'c' ),
        ('a', 2, 'd' ),
        ('b', 1, 'def' )


       select 
  T.ID as '@field1',

  ((select 
      L.Name as '@fieldA'

    from @sites as L
    where T.ID = L.ID
    for xml path('LowLevelItem'), type))
from @sites as T
for xml path('TopLevelItem'), root('TopLevelItems') 

Let me know if i am missing anything in my query or the approach that i am using is not right.

Thanks in advance.

Answer 1

SELECT  T.ID AS '@field1'
      , ( (SELECT   L.Name AS '@fieldA'
           FROM     @sites AS L
           WHERE    T.ID = L.ID
        FOR
          XML PATH('LowLevelItem')
            , TYPE)
        )
FROM    (SELECT DISTINCT ID FROM @sites) AS T
FOR     XML PATH('TopLevelItem')
          , ROOT('TopLevelItems')