For XML Path with nesting elements based on level after recursive CTE

Question

I have data that looks like after writing the recursive CTE:

| EPC | ParentEPC | SerialEventId| Level| @ItemName|
|--------|--------------|--------------:|:----------:|--------------|
| a| NULL|5557|0|[PALLET] - 7 UNITS|
| b| a|5557|1|[CARTON] - 1 UNIT|
| c| a|5557|1|[CASE] - 3 UNITS|
| d| c|5557|2|[CARTON] - 1 UNIT|
| e| c|5557|2|[CARTON] - 1 UNIT|
| f| c|5557|2|[CARTON] - 1 UNIT|

I want to write a T-SQL query in SQL Server to return the data like this:

<Items>
  <Item ItemName="[PALLET] - 7 UNITS">
    <Item ItemName="[CARTON] - 1 UNIT" />
    <Item ItemName="[CASE] - 3 UNITS">
      <Item ItemName="[CARTON] - 1 UNIT" />
      <Item ItemName="[CARTON] - 1 UNIT" />
      <Item ItemName="[CARTON] - 1 UNIT" />
    </Item>
  </Item>
</Items>

I have tried XML PATH but couldn't able to get the nesting part in XML based on the level like below

SELECT *
    ,(
        SELECT epc."@ItemName"
        FROM #EPC_items epc
        WHERE epc.SerialEventID = se.SerialEventID
        FOR XML PATH('Item')
            ,ROOT('Items')
            ,TYPE
        )
FROM #SerialEvents se

Here is the recursive CTE query that I used to get the result table shown above

IF OBJECT_ID('tempdb..#SerialEvents') IS NOT NULL DROP TABLE #SerialEvents
GO
IF OBJECT_ID('tempdb..#EPC_items') IS NOT NULL DROP TABLE #EPC_items
GO

SELECT DISTINCT se.SerialEventID, se.OrderType, se.ASWRefNum, se.ASWLineNum
into #SerialEvents
FROM dbo.SerialEvent se 
WHERE 1=1
    AND SerialEventTypeId not in (1,13,8,9,10)
    AND SerialEventDateTime >= DATEADD(d,-2, GETDATE())
ORDER BY 1 DESC;

;WITH CTE 
    as (

    SELECT  
                et.EPC,et.ParentEPC,
                et.SerialEventId ,
                 0 AS [Level],
                ' [' + UPPER(e.UnitType) + '] - ' 
                + CAST(e.ChildQuantity as VARCHAR) 
                + 
                CASE 
                    WHEN e.ChildQuantity > 1 THEN ' UNITS' 
                    ELSE ' UNIT'
                END AS "@ItemName" 
        FROM        dbo.EPCTRansaction et
        INNER JOIN  dbo.SerialEvent se on et.SerialEventId = se.SerialEventId
        INNER JOIN dbo.vwEPC e ON et.EPC = e.EPC
        INNER JOIN #SerialEvents sep on et.SerialEventId = sep.SerialEventId and et.SerialEventID =5557
                WHERE   
                1=1
                AND et.ParentEPC IS NULL


        UNION ALL

    SELECT  
                et.EPC,             
                CTE.EPC as ParentEPC,
                et.SerialEventId,
                cte.Level + 1,
                ' [' + UPPER(e.UnitType) + '] - ' 
                + CAST(e.ChildQuantity as VARCHAR) 
                + 
                CASE 
                    WHEN e.ChildQuantity > 1 THEN ' UNITS' 
                    ELSE ' UNIT'
                END AS "@ItemName" 
        FROM        dbo.EPCTRansaction et
        INNER JOIN  dbo.SerialEvent se on et.SerialEventId = se.SerialEventId
        INNER JOIN dbo.vwEPC e ON et.EPC = e.EPC
        INNER JOIN CTE on et.ParentEPC = CTE.EPC and et.SerialEventId  = CTE.SerialEventId
                WHERE   
                1=1
    )
    select * into #EPC_items from CTE 

    select * from #EPC_items

Answer 1

Recursing in rows is very easy in SQL Server. On the other hand, what you are trying to do is grouped recursion: on each level you want to group up the data and place it inside its parent. This is much harder.

The easiest method I have found is to use (horror of horrors!) a scalar UDF.

Unfortunately I can't test this as you haven't given proper sample data for all your tables. It's also unclear which joins are needed.

CREATE FUNCTION dbo.GetXml (@ParentEPC int)
RETURNS xml
AS
BEGIN
    RETURN (
        SELECT
          CONCAT(
            '[',
            UPPER(e.UnitType),
            '] - ',
            e.ChildQuantity,
            CASE 
                WHEN e.ChildQuantity > 1 THEN ' UNITS' 
                ELSE ' UNIT'
            END
           ) AS [@ItemName],
          dbo.GetXml(et.EPC)    -- do not name this column
        FROM        dbo.EPCTRansaction et
        INNER JOIN  dbo.SerialEvent se on et.SerialEventId = se.SerialEventId
        INNER JOIN dbo.vwEPC e ON et.EPC = e.EPC
        INNER JOIN #SerialEvents sep on et.SerialEventId = sep.SerialEventId and et.SerialEventID = 5557
        WHERE   
            EXISTS (SELECT et.ParentEPC INTERSECT SELECT @ParentEPC)    -- nullable compare
        FOR XML PATH('Item'), TYPE
    );
END;

SELECT
  dbo.GetXml(NULL)
FOR XML PATH('Items'), TYPE;