SQL Query for XML nesting/grouping

Question

I need to produce an XML fragment such as the following:

<buldings>
  <building>
    <id>126433</id>
    <flats>
      <flat>
        <flat_id>ПК-01-15-01-072</flat_id>
      </flat>
      <flat>
        <flat_id>ПК-01-17-01-082</flat_id>
      </flat>
    </flats>
  </building>
</buldings>

I'm writing this sql:

select la.tisa_idcorpusdomclick [id]
    ,(
        select a.tisa_code [flat/flat_id] 
         from tisa_Article a 
         where 
               a.tisa_LayoutId = la.tisa_LayoutId 
           and a.tisa_ArticleId = la.tisa_ArticleId
         for xml path('flats'), type
      )
from (
       select l.tisa_idcorpusdomclick
             ,l.tisa_LayoutId
             ,a.tisa_ArticleId
         from tisa_layout l left join 
              tisa_article a on a.tisa_LayoutId = l.tisa_LayoutId 
        where l.tisa_idcorpusdomclick is not null 
          and a.statuscode = 4 
          and a.tisa_ArticleTypeCode = 2) la
for xml path('building'), root('buldings')

thats returns me incorrect xml. I need to put all flats into node building - > flats. Any ideas?

Answer 1

Try this in SSMS and see if it gets you moving in the right direction.

DECLARE @building TABLE ( id VARCHAR(10) );
INSERT INTO @building ( id ) VALUES ( '126433' );

DECLARE @flats TABLE ( id VARCHAR(10), flat_id VARCHAR(50) );
INSERT INTO @flats ( id, flat_id ) VALUES ( '126433', 'NK-01-15-01-072' ), ( '126433', 'NK-01-17-01-082' );

SELECT
    bldg.id, flats.flats AS 'flats'
FROM @building bldg
CROSS APPLY (
    SELECT CAST( (
        SELECT flat.flat_id FROM @flats flat WHERE flat.id = bldg.id ORDER BY flat.flat_id FOR XML PATH( 'flat' )
    ) AS XML ) AS flats
) AS flats
ORDER BY bldg.id
FOR XML PATH( 'building' ), ROOT( 'buildings' );

Returns

<buildings>
  <building>
    <id>126433</id>
    <flats>
      <flat>
        <flat_id>NK-01-15-01-072</flat_id>
      </flat>
      <flat>
        <flat_id>NK-01-17-01-082</flat_id>
      </flat>
    </flats>
  </building>
</buildings>