Peter Larsson Blog

Patron Saint of Lost Yaks

Create nested hierachy XML

by Peter Larsson in  ,

-- Prepare sample data
DECLARE @Master TABLE
        (
                ID INT,
                Name VARCHAR(20)
        )

INSERT  @Master
SELECT  1, 'Peso' UNION ALL
SELECT  2, 'SQLTeam' UNION ALL
SELECT  3, 'SQL'

DECLARE @Child TABLE
        (
                MasterID INT,
                ID INT,
                Value VARCHAR(20)
        )

INSERT  @Child
SELECT  1, 1, 'Row 1 for Peso' UNION ALL
SELECT  1, 2, 'Row 2 for Peso' UNION ALL
SELECT  2, 3, 'Row 1 for SQLTeam'

-- Display the XML
SELECT          ePurchaseOrder.ID AS MasterID,
                ePurchaseOrder.Name AS MasterName,
                ePurchaseOrderLine.ID AS ChildID,
                ePurchaseOrderLine.Value AS ChildValue
FROM            @Master AS ePurchaseOrder
LEFT JOIN       @Child AS ePurchaseOrderLine ON ePurchaseOrderLine.MasterID = ePurchaseOrder.ID
FOR XML         AUTO,
                ROOT('POSystem'),
                ELEMENTS

SELECT      m.ID AS MasterID,
            m.Name AS MasterName,
            (
                        SELECT      c.ID AS ChildID,
                                    c.Value AS ChildValue
                        FROM        @Child AS c
                        WHERE       c.MasterID = m.ID
                        FOR XML     PATH('ePurchaseOrderLine'),
                                    TYPE
            )
FROM        @Master AS m
FOR XML     PATH('ePurchaseOrder'),
            ROOT('POSystem')