Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Create nested hierachy XML

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

Print | posted on Thursday, November 06, 2008 4:57 PM | Filed Under [ SQL Server 2008 SQL Server 2005 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET