Grouping values under elements FOR XML
I am trying to format the output of a query using
FOR XML in SQL Server 2012.
PART_NO can have a varying number of
SUPPLIER_PART_NUMBER's mapped to it.
The table has data in the following format.
PART_NO SUPPLIER_PART_NO ------- ---------------- AAA 1 AAA 2 BBB 3 BBB 4 BBB 5
The desired output is as follows where part AAA has two supplier part numbers and part BBB has three supplier part numbers, and the supplier part numbers are nested below the part number.
<root> <item PartNo ="AAA"> <mpn>1</mpn> <mpn>2</mpn> </item> <item PartNo ="BBB"> <mpn>3</mpn> <mpn>4</mpn> <mpn>5</mpn> </item> </root>
The closest I can get is below, but this does not group the
SELECT [PART_NO] as 'item/@PartNo', [SUPPLIER_PART_NO] as 'mpn' FROM [dbo].[supplier_part_mapping2] ORDER BY PART_NO FOR XML PATH('') , ROOT('root');
Thank you in advance
SELECT p1.PART_NO as 'item/@PartNo', (SELECT SUPPLIER_PART_NO AS 'mpn' FROM [dbo].[supplier_part_mapping2] p2 WHERE p1.PART_NO = p2.PART_NO FOR XML PATH(''), TYPE) AS 'item' FROM [dbo].[supplier_part_mapping2] p1 GROUP BY PART_NO ORDER BY PART_NO FOR XML PATH('') , ROOT('root');
This should produce:
You basically need to group by the
PART_NOso that you get only one
<item>entry for each distinct
PART_NO, and you need to grab the "sub-elements" as a subquery to list them all together under one parent node.