Grouping values under elements FOR XML

I am trying to format the output of a query using FOR XML in SQL Server 2012.

Each 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 mpn under PartNo:

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

1 answer

  • answered 2018-04-14 19:58 marc_s

    Try this:

    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:

    enter image description here

    You basically need to group by the PART_NO so 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.