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
CodePudding user response:
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;