SQL xml multilevel structure from one table

Question

I have a table in MS SQL Server database which has a structure like this:

 | goodId | pos     | description | docType        | isKey |
 +--------+---------+-------------+----------------+-------+
 | 1417   | NULL    | List 1050   | 1050.0000.0000 | 0     |
 | 1417   | 2.1.1.1 | hgfhgf9     | 1050.0002.0000 | 1     |
 | 1417   | 2.1.1.2 | hghgfh0     | 1050.0002.0000 | 1     |
 | 1417   | 2.1.1.3 | wwwwww      | 1050.0002.0000 | 1     |
 | 1417   | NULL    | List 1030   | 1030.0000.0000 | 0     |
 | 1417   | 1.3.7.6 | tdgfdgfd    | 1030.0001.0001 | 1     |
 | 1417   | 9.2.1.2 | gdfgfdfd    | 1030.0001.0009 | 1     |
 | 1417   | 9.2.1   | dddddddd    | 1030.0002.0009 | 1     |

In the last column [docType] first 4 characters mean the number of the list, next 4 characters mean the number of the part. I need to take positions where isKey = 1, but description for the list should be taken from the row where only 4 first charcters are filled(it will be description for that list)

I want to get an XML structure from this table using SQL XML like that:

 <good Id="1417">
  <list num="1050" description="List 1050"> 
    <part num="2">
      <pos num = "2.1.1.1"/>
      <pos num= "2.1.1.2"/>
      <pos num= "2.1.1.3"/>
    </part>
  </list>
  <list num="1030" description="List 1030">
    <part num="1">
      <pos num = "1.3.7.6"/>
      <pos num = "9.2.1.2"/>
    </part>
    <part num="2">
      <pos num = "9.2.1"/>
    </part>
  </list>
</good>

What query should I write to get this XML structure?

Answer 1

Monstrous query.

declare @MyTable table (goodId int, pos varchar(100), description varchar(100), docType varchar(100), isKey bit)

insert into @MyTable (goodId, pos, description, docType, isKey) values
(1417,  NULL,     'List 1050', '1050.0000.0000', 0),
(1417, '2.1.1.1', 'hgfhgf9',   '1050.0002.0000', 1),
(1417, '2.1.1.2', 'hghgfh0',   '1050.0002.0000', 1),
(1417, '2.1.1.3', 'wwwwww',    '1050.0002.0000', 1),
(1417,  NULL,     'List 1030', '1030.0000.0000', 0),
(1417, '1.3.7.6', 'tdgfdgfd',  '1030.0001.0001', 1),
(1417, '9.2.1.2', 'gdfgfdfd',  '1030.0001.0009', 1),
(1417, '9.2.1',   'dddddddd',  '1030.0002.0009', 1)

select g.goodId as '@Id'
    , (
        select l.num as '@num'
            , l.description as '@description'
            , (
                select cast(pa.num as int) as '@num'
                    , (
                        select po.pos as '@num'
                        from @MyTable po
                        where g.goodId = po.goodId and po.pos is not null and l.num = parsename(po.docType, 3) and pa.num = parsename(po.docType, 2)
                        for xml path('pos'), type
                    ) as [*]
                from (
                    select distinct parsename(pa.docType, 2) num
                    from @MyTable pa
                    where g.goodId = pa.goodId and pa.pos is not null and l.num = parsename(pa.docType, 3)
                ) pa
                for xml path('part'), type
            ) as [*]
        from (
            select distinct parsename(l.docType, 3) num, l.description
            from @MyTable l
            where g.goodId = l.goodId and l.pos is null
        ) l
        order by l.num
        for xml path('list'), type
    ) as [*]
from (
    select distinct goodId
    from @MyTable
) g
for xml path('good'), type