Structure SQL multiniveau XML à partir d'une table

J'ai une table dans la database MS SQL Server qui a une structure comme celle-ci:

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

Dans la dernière colonne [docType] les 4 premiers caractères désignent le numéro de la list, les 4 caractères suivants désignent le numéro de la pièce. Je dois prendre des positions où isKey = 1, mais la description de la list devrait être prise à partir de la rangée où seulement 4 premiers caractères sont remplis (ce sera une description pour cette list)

Je veux get une structure XML de cette table en utilisant SQL XML comme ça:

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

Quelle requête dois-je écrire pour get cette structure XML?

Requête monstrueuse.

 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

Ne me souviens pas s'il est possible de faire sans lourdement imbriquer comme ceci:

 ;with cte as ( select goodId, pos, description, left(docType, 4) as list_num, cast(subssortingng(docType, 6, 4) as int) as part_num, left(docType, 9) as full_part_num from Table1 ) select t1.goodId as Id, ( select t2.list_num as num, t2.description, ( select t3.part_num as num, ( select t4.pos as num from cte as t4 where t4.full_part_num = t3.full_part_num for xml raw('pos'), type ) from cte as t3 where t3.goodId = t1.goodId and t3.list_num = t2.list_num and t3.pos is not null group by t3.part_num, t3.full_part_num for xml raw('part'), type ) from cte as t2 where t2.goodId = t1.goodId and t2.pos is null for xml raw('list'), type ) from cte as t1 group by t1.goodId for xml raw('good');

sql fiddle démo