對于具有基于遞回CTE后級別的嵌套元素的XML路徑

我有撰寫遞回 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|

我想在 SQL Server 中撰寫一個 T-SQL 查詢來回傳如下資料：

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

我已經嘗試過XML PATH，但無法根據下面的級別獲取 XML 中的嵌套部分

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

這是我用來獲取上面顯示的結果表的遞回 CTE 查詢

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

uj5u.com熱心網友回復：

在 SQL Server 中，按行遞回非常容易。另一方面，您要做的是分組遞回：在每個級別上，您都希望將資料分組并將其放在其父級中。這要困難得多。

我發現的最簡單的方法是使用（恐怖的恐怖！）標量 UDF。

不幸的是，我無法對此進行測驗，因為您沒有為所有表格提供正確的樣本資料。還不清楚需要哪些連接。

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;

標籤：sql服务器 xml 公共表表达式 递归查询 for-xml

上一篇：SQLXML自動輸出到檔案中

下一篇：將不同的XML讀取到pandas資料幀