时间戳

首页 > 解决方案 > 通过 XQuery 在嵌套元素中遍历/迭代

sql-server - 通过 XQuery 在嵌套元素中遍历/迭代

问题描述

我需要在名为 Group 的子元素中进行迭代。

DECLARE @XMLData XML = N'<Nodes>
  <Sheet FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Model1">
    <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Consensus Model">
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1225" NodeText="Net Revenue" />
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1226" NodeText="Cost of Revenue">
        <BM FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" BMID="01" NodeText="As % of Net Revenue" />
        <BM FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" BMID="02" NodeText="Year over Year Growth" />
      </LineItem>
      <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Test Group1">
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1225" NodeText="Test_Group_LI" />
      </Group>
      <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Test Group2"/>

    </Group>
    <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Segment Details">
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1227" NodeText="Cost of Revenue-GAAP" />
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1228" NodeText="Gross Profit" />
    </Group>
  </Sheet>
  <Sheet FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Model2">
    <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Key Financials">
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1235" NodeText="Total Operating Expenses-GAAP" />
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1236" NodeText="EBITDA">
            <BM FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" BMID="01" NodeText="BM_Test1" />
            <BM FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" BMID="02" NodeText="BM_Test2" />
     </LineItem>
    </Group>
  </Sheet>
</Nodes>';

我在 MSDN 论坛上发布了这个问题，Yitzhak Khabinsky 先生在那里回答了我的情况。哪个有效，但是当有嵌套组时，代码不会插入嵌套组数据。

我的 XML 将包含 Sheet、Group、Lineitem 和 BM 元素。只有组可以嵌套。一个组可以有多个嵌套的子组，这些子组可能有 Lineitem 或 BM 元素。

所以层次结构是：

  1. 工作表可能有多个组。sheet 不会被嵌套。
  2. 组可以有 lineitems，组也可以有多个子组。
  3. lineitem 将 BM 元素作为子元素。

所以只有 Group 可能有嵌套的第 N 个子元素。

现在在这里我分享一个我从 MSDN 论坛获得的代码，该代码正在运行，但是当有嵌套组时，那些嵌套组数据不会被插入。我的xquery知识并不好。我认为代码的微小变化将使代码适用于嵌套组。

示例代码

DECLARE @XMLData XML = N'<Nodes>
  <Sheet FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Model1">
    <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Consensus Model">
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1225" NodeText="Net Revenue" />
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1226" NodeText="Cost of Revenue">
        <BM FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" BMID="01" NodeText="As % of Net Revenue" />
        <BM FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" BMID="02" NodeText="Year over Year Growth" />
      </LineItem>
      <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Test Group1">
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1225" NodeText="Test_Group_LI" />
      </Group>
      <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Test Group2"/>

    </Group>
    <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Segment Details">
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1227" NodeText="Cost of Revenue-GAAP" />
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1228" NodeText="Gross Profit" />
    </Group>
  </Sheet>
  <Sheet FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Model2">
    <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" NodeText="Key Financials">
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1235" NodeText="Total Operating Expenses-GAAP" />
      <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" LineItemID="1236" NodeText="EBITDA">
            <BM FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" BMID="01" NodeText="BM_Test1" />
            <BM FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor="" BMID="02" NodeText="BM_Test2" />
     </LineItem>
    </Group>
  </Sheet>
</Nodes>';

DECLARE @tblCSM_Details TABLE
(
    [CSM_ID] [int] NOT NULL,
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ParentID] [int] NULL,
    [Type] [varchar](30) NULL,
    [DisplayInCSM] [varchar](200) NULL,
    [FontName] [varchar](max) NULL,
    [FontStyle] [varchar](max) NULL,
    [FontSize] [varchar](max) NULL,
    [UnderLine] [varchar](max) NULL,
    [BGColor] [varchar](max) NULL,
    [LineItemID] [int] NULL,
    [BMID] [int] NULL,
    [ColOrder] [int] NULL
);


DECLARE @SheetID INT,@GroupID INT,@LineItemID INT, @BMID INT;

DECLARE @SheetStartIndex INT, @SheetCount INT;
DECLARE @GroupStartIndex INT, @GroupCount INT;   
DECLARE @LineitemStartIndex INT, @LineitemCount INT;   
DECLARE @BMStartIndex INT, @BMCount INT;

SET @SheetStartIndex = 1;
SET @SheetCount = @XMLData.value('count(/Nodes/Sheet)', 'INT');     
WHILE @SheetStartIndex <= @SheetCount BEGIN --Inserting sheet data 
    INSERT INTO @tblCSM_Details(CSM_ID,[ParentID],[Type],[DisplayInCSM],[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[LineItemID],[BMID],[ColOrder])
    SELECT 1,0, c.value('local-name(.)','VARCHAR(30)') --'SHEET'
        , c.value('@NodeText', 'VARCHAR(MAX)')
        , c.value('@FontName', 'VARCHAR(MAX)')
        , c.value('@FontStyle', 'VARCHAR(MAX)')
        , c.value('@FontSize', 'VARCHAR(MAX)')
        , c.value('@UnderLine', 'VARCHAR(MAX)')
        , c.value('@BGColor', 'VARCHAR(MAX)')
        , 0,0, @SheetStartIndex
    FROM @XMLData.nodes('/Nodes/Sheet[position() = sql:variable("@SheetStartIndex")]') AS t(c);

    SELECT @SheetID = SCOPE_IDENTITY();   

    --Inserting Group data
    SET @GroupStartIndex = 1;
    SET @GroupCount = @XMLData.value('count(/Nodes/Sheet[position() = sql:variable("@SheetStartIndex")]/Group)', 'INT');
    WHILE @GroupStartIndex <= @GroupCount BEGIN --Inserting Group data 
        INSERT INTO @tblCSM_Details(CSM_ID,[ParentID],[Type],[DisplayInCSM],[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[LineItemID],[BMID],[ColOrder])
        SELECT 1,@SheetID, c.value('local-name(.)','VARCHAR(30)') --'GROUP'
            , c.value('@NodeText', 'VARCHAR(MAX)')
            , c.value('@FontName', 'VARCHAR(MAX)')
            , c.value('@FontStyle', 'VARCHAR(MAX)')
            , c.value('@FontSize', 'VARCHAR(MAX)')
            , c.value('@UnderLine', 'VARCHAR(MAX)')
            , c.value('@BGColor', 'VARCHAR(MAX)')
            , 0,0, @GroupStartIndex
        FROM @XMLData.nodes('/Nodes/Sheet[position() = sql:variable("@SheetStartIndex")]/Group[position() = sql:variable("@GroupStartIndex")]') AS t(c);

        SELECT @GroupID = SCOPE_IDENTITY();   

        --Inserting LineItem data
        SET @LineitemStartIndex = 1;
        SET @LineitemCount = @XMLData.value('count(/Nodes/Sheet[position() = sql:variable("@SheetStartIndex")]/Group[position() = sql:variable("@GroupStartIndex")]/LineItem)', 'INT');
        WHILE @LineitemStartIndex <= @LineitemCount BEGIN 
            INSERT INTO @tblCSM_Details(CSM_ID,[ParentID],[Type],[DisplayInCSM],[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[LineItemID],[BMID],[ColOrder])
            SELECT 1,@GroupID, c.value('local-name(.)','VARCHAR(30)') --'LINEITEM'
                , c.value('@NodeText', 'VARCHAR(MAX)')
                , c.value('@FontName', 'VARCHAR(MAX)')
                , c.value('@FontStyle', 'VARCHAR(MAX)')
                , c.value('@FontSize', 'VARCHAR(MAX)')
                , c.value('@UnderLine', 'VARCHAR(MAX)')
                , c.value('@BGColor', 'VARCHAR(MAX)')
                , c.value('@LineItemID', 'INT')
                , 0, @LineitemStartIndex
            FROM @XMLData.nodes('/Nodes/Sheet[position() = sql:variable("@SheetStartIndex")]/Group[position() = sql:variable("@GroupStartIndex")]/LineItem[position() = sql:variable("@LineitemStartIndex")]') AS t(c);

            SELECT @LineItemID = SCOPE_IDENTITY();

            --Inserting BM data
            SET @BMStartIndex = 1;
            SET @BMCount = @XMLData.value('count(/Nodes/Sheet[position() = sql:variable("@SheetStartIndex")]/Group[position() = sql:variable("@GroupStartIndex")]/LineItem[position() = sql:variable("@LineitemStartIndex")]/BM)', 'INT');
            WHILE @BMStartIndex <= @BMCount BEGIN --Inserting sheet data 
                INSERT INTO @tblCSM_Details(CSM_ID,[ParentID],[Type],[DisplayInCSM],[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[LineItemID],[BMID],[ColOrder])
                SELECT 1,@LineItemID, c.value('local-name(.)','VARCHAR(30)') --BM
                    , c.value('@NodeText', 'VARCHAR(MAX)')
                    , c.value('@FontName', 'VARCHAR(MAX)')
                    , c.value('@FontStyle', 'VARCHAR(MAX)')
                    , c.value('@FontSize', 'VARCHAR(MAX)')
                    , c.value('@UnderLine', 'VARCHAR(MAX)')
                    , c.value('@BGColor', 'VARCHAR(MAX)')
                    , 0
                    , c.value('@BMID', 'INT')
                    , @BMStartIndex
                FROM @XMLData.nodes('/Nodes/Sheet[position() = sql:variable("@SheetStartIndex")]/Group[position() = sql:variable("@GroupStartIndex")]/LineItem[position() = sql:variable("@LineitemStartIndex")]/BM[position() = sql:variable("@BMStartIndex")]') AS t(c);

                SET @BMStartIndex += 1;
            END;

            SET @LineitemStartIndex += 1;
        END;

        SET @GroupStartIndex += 1;      
    END;

    SET @SheetStartIndex += 1;      
END;

SELECT * FROM @tblCSM_Details;

请有人告诉我要在代码中更改什么，因此它应该可以处理嵌套的组元素。

编辑

DECLARE @XMLData XML = 
N'<Nodes>
    <Sheet FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
           NodeText="Model1">
        <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
               NodeText="Consensus Model">
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1225" NodeText="Net Revenue"/>
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1226" NodeText="Cost of Revenue">
                <BM FontName="" FontStyle="" FontSize="" UnderLine="false"
                    BGColor="" BMID="01" NodeText="As % of Net Revenue"/>
                <BM FontName="" FontStyle="" FontSize="" UnderLine="false"
                    BGColor="" BMID="02" NodeText="Year over Year Growth"/>
            </LineItem>
            <Group FontName="" FontStyle="" FontSize="" UnderLine="false"
                   BGColor="" NodeText="Test Group1">
                <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                          BGColor="" LineItemID="1225" NodeText="Test_Group_LI"/>
            </Group>
            <Group FontName="" FontStyle="" FontSize="" UnderLine="false"
                   BGColor="" NodeText="Test Group2"/>
        </Group>
        <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
               NodeText="Segment Details">
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1227"
                      NodeText="Cost of Revenue-GAAP"/>
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1228" NodeText="Gross Profit"/>
        </Group>
    </Sheet>
    <Sheet FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
           NodeText="Model2">
        <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
               NodeText="Key Financials">
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1235"
                      NodeText="Total Operating Expenses-GAAP"/>
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1236" NodeText="EBITDA">
                <BM FontName="" FontStyle="" FontSize="" UnderLine="false"
                    BGColor="" BMID="01" NodeText="BM_Test1"/>
                <BM FontName="" FontStyle="" FontSize="" UnderLine="false"
                    BGColor="" BMID="02" NodeText="BM_Test2"/>
            </LineItem>
        </Group>
    </Sheet>
</Nodes>';



WITH Sheets AS
(
    SELECT 'Sheet' AS [Type]
          ,ROW_NUMBER() OVER(ORDER BY A.sh) AS Id
          ,REPLACE(STR(ROW_NUMBER() OVER(ORDER BY A.sh),2),' ','0') AS SortString
          ,sh.query('.') Content
    FROM @XMLData.nodes('/Nodes/Sheet') A(sh)
)
,SheetGroups AS
(
    SELECT sh.* FROM Sheets sh
    UNION ALL
    SELECT 'Group'
          ,ROW_NUMBER() OVER(PARTITION BY sh.SortString ORDER BY A.gr)
          ,CONCAT(sh.SortString,REPLACE(STR(ROW_NUMBER() OVER(PARTITION BY sh.SortString ORDER BY A.gr),2),' ','0')) 
          ,gr.query('.') 
    FROM Sheets sh
    OUTER APPLY Content.nodes('Sheet/Group') A(gr)
)
,LineItems AS
(
    SELECT shgr.* FROM SheetGroups shgr
    UNION ALL
    SELECT 'LineItem'
          ,ROW_NUMBER() OVER(PARTITION BY shgr.SortString ORDER BY A.li)
          ,CONCAT(shgr.SortString,REPLACE(STR(ROW_NUMBER() OVER(PARTITION BY shgr.SortString ORDER BY A.li),2),' ','0')) 
          ,li.query('.') 
    FROM SheetGroups shgr
    OUTER APPLY Content.nodes('Group/LineItem') A(li)
)
,BMs AS
(
    SELECT li.* FROM LineItems li
    UNION ALL
    SELECT 'BM'
          ,ROW_NUMBER() OVER(PARTITION BY li.SortString ORDER BY A.bm)
          ,CONCAT(li.SortString,REPLACE(STR(ROW_NUMBER() OVER(PARTITION BY li.SortString ORDER BY A.bm),2),' ','0')) 
          ,bm.query('.') 
    FROM LineItems li
    OUTER APPLY Content.nodes('LineItem/BM') A(bm)
)
,FinalList AS
(
    SELECT 1 AS CSM_ID
          ,ROW_NUMBER() OVER(ORDER BY BMs.SortString) AS ID
          ,BMs.SortString
          ,BMs.[Type]
          ,BMs.Content.value('(/*/@NodeText)[1]','varchar(max)') As DisplayInCSM
          ,BMs.Content.value('(/*/@FontName)[1]','varchar(max)') As FontName
          ,BMs.Content.value('(/*/@FontStyle)[1]','varchar(max)') AS FontStyle
          ,BMs.Content.value('(/*/@FontSize)[1]','varchar(max)') AS FontSize
          ,BMs.Content.value('(/*/@UnderLine)[1]','varchar(max)') AS UnderLine
          ,BMs.Content.value('(/*/@BGColor)[1]','varchar(max)') AS BGColor
          ,BMs.Content.value('(/*/@LineItemID)[1]','varchar(max)') AS LineItemID
          ,BMs.Content.value('(/*/@BMID)[1]','varchar(max)') AS BMID
          ,BMs.Id AS ColOrder
    FROM BMs
    WHERE Content IS NOT NULL
)
SELECT f1.CSM_ID
      ,f1.ID
      ,(SELECT f2.ID FROM FinalList f2 WHERE f2.SortString=LEFT(f1.SortString,LEN(f1.SortString)-2))
      ,f1.[Type]
      ,f1.DisplayInCSM
      ,f1.FontName
      ,f1.FontStyle
      ,f1.FontSize
      ,f1.UnderLine
      ,f1.BGColor
      ,f1.LineItemID
      ,f1.BMID
      ,f1.ColOrder
FROM FinalList f1
ORDER BY SortString;

--从表格中选择 *

@Shnugo 当我使用嵌套组测试您的代码时，它无法考虑那些嵌套组。xml 中有一个嵌套组，称为Group1 和 Group2

    <Group FontName="" FontStyle="" FontSize="" UnderLine="false"
           BGColor="" NodeText="Test Group1">
        <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                  BGColor="" LineItemID="1225" NodeText="Test_Group_LI"/>
    </Group>
    <Group FontName="" FontStyle="" FontSize="" UnderLine="false"
           BGColor="" NodeText="Test Group2"/>

一个组可以有多个嵌套组，每个嵌套组可以有或没有 lineitem，每个 lineitem 可以有或没有 BM 元素。

标签： sql-serverxmlxquery

解决方案

这是另一种处理方式。没有循环。

该方法使用递归 CTE。这样任何元素都可以在任何地方。您可以取消注释最后一个WHERE子句以获取偶数属性。

此链接上的所有功劳都归于人们：如何从 SQL Server 中的 XML 值中获取元素名称列表

SQL

DECLARE @xml XML = 
N'<Nodes>
    <Sheet FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
           NodeText="Model1">
        <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
               NodeText="Consensus Model">
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1225" NodeText="Net Revenue"/>
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1226" NodeText="Cost of Revenue">
                <BM FontName="" FontStyle="" FontSize="" UnderLine="false"
                    BGColor="" BMID="01" NodeText="As % of Net Revenue"/>
                <BM FontName="" FontStyle="" FontSize="" UnderLine="false"
                    BGColor="" BMID="02" NodeText="Year over Year Growth"/>
            </LineItem>
            <Group FontName="" FontStyle="" FontSize="" UnderLine="false"
                   BGColor="" NodeText="Test Group1">
                <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                          BGColor="" LineItemID="1225" NodeText="Test_Group_LI"/>
            </Group>
            <Group FontName="" FontStyle="" FontSize="" UnderLine="false"
                   BGColor="" NodeText="Test Group2"/>
        </Group>
        <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
               NodeText="Segment Details">
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1227"
                      NodeText="Cost of Revenue-GAAP"/>
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1228" NodeText="Gross Profit"/>
        </Group>
    </Sheet>
    <Sheet FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
           NodeText="Model2">
        <Group FontName="" FontStyle="" FontSize="" UnderLine="false" BGColor=""
               NodeText="Key Financials">
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1235"
                      NodeText="Total Operating Expenses-GAAP"/>
            <LineItem FontName="" FontStyle="" FontSize="" UnderLine="false"
                      BGColor="" LineItemID="1236" NodeText="EBITDA">
                <BM FontName="" FontStyle="" FontSize="" UnderLine="false"
                    BGColor="" BMID="01" NodeText="BM_Test1"/>
                <BM FontName="" FontStyle="" FontSize="" UnderLine="false"
                    BGColor="" BMID="02" NodeText="BM_Test2"/>
            </LineItem>
        </Group>
    </Sheet>
</Nodes>';

-- Solution # 2.
-- a thing of beauty.
WITH cte AS (  
SELECT 1 AS lvl,  
        x.value('local-name(.)','NVARCHAR(MAX)') AS Name,  
        CAST(NULL AS NVARCHAR(MAX)) AS ParentName, 
        CAST(1 AS INT) AS ParentPosition, 
        CAST(N'Element' AS NVARCHAR(20)) AS NodeType,  
        x.value('local-name(.)','NVARCHAR(MAX)') AS FullPath,  
        x.value('local-name(.)','NVARCHAR(MAX)')  
        + N'[' 
        + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR)  
        + N']' AS XPath,  
        ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS Position, 
        x.value('local-name(.)','NVARCHAR(MAX)') AS Tree,  
        x.value('text()[1]','NVARCHAR(MAX)') AS Value,  
        x.query('.') AS this,         
        x.query('*') AS t,  
        CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort,  
        CAST(1 AS INT) AS ID  
FROM @xml.nodes('/*') a(x)  
UNION ALL 
SELECT p.lvl + 1 AS lvl,  
        c.value('local-name(.)','NVARCHAR(MAX)') AS Name,  
        CAST(p.Name AS NVARCHAR(MAX)) AS ParentName, 
    CAST(p.Position AS INT) AS ParentPosition, 
        CAST(N'Element' AS NVARCHAR(20)) AS NodeType,  
        CAST(p.FullPath + N'/' + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS FullPath,

        CAST(p.XPath + N'/'+ c.value('local-name(.)','NVARCHAR(MAX)')
        + N'['
        + CAST(ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)') ORDER BY (SELECT 1)) AS NVARCHAR)
        + N']' AS NVARCHAR(MAX)) AS XPath,  

        ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
        ORDER BY (SELECT 1)) AS Position, 
        CAST( SPACE(2 * p.lvl - 1) + N'|' + REPLICATE(N'-', 1) + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS Tree,  
        CAST( c.value('text()[1]','NVARCHAR(MAX)') AS NVARCHAR(MAX) ) AS Value, c.query('.') AS this,  
        c.query('*') AS t,  
        CAST(p.Sort + CAST( (lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4)) AS VARBINARY(MAX) ) AS Sort,  
        CAST((lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS INT)  
FROM cte p  
CROSS APPLY p.t.nodes('*') b(c))
, cte2 AS (  
SELECT lvl AS Depth,  
        Name AS NodeName,  
        ParentName, 
        ParentPosition, 
        NodeType,  
        FullPath,  
        XPath,  
        Position, 
        Tree AS TreeView,  
        Value,  
        this AS XMLData,  
        Sort, ID  
        FROM cte  
UNION ALL 
SELECT p.lvl,  
        x.value('local-name(.)','NVARCHAR(MAX)'),  
        p.Name, 
        p.Position, 
        CAST(N'Attribute' AS NVARCHAR(20)),  
        p.FullPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
        p.XPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
        1, 
        SPACE(2 * p.lvl - 1) + N'|' + REPLICATE('-', 1)  
        + N'@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
        x.value('.','NVARCHAR(MAX)'),  
        NULL,  
        p.Sort,  
        p.ID + 1  
FROM cte p  
CROSS APPLY this.nodes('/*/@*') a(x)  
)  
SELECT ROW_NUMBER() OVER(ORDER BY Sort, ID) AS ID,  
    ParentName, ParentPosition,Depth, NodeName, Position,   
    NodeType, FullPath, XPath, TreeView, Value, XMLData
    , XMLData.value('*[1]/@NodeText','VARCHAR(30)') AS NodeText
    , XMLData.value('*[1]/@FontName','VARCHAR(30)') AS FontName
    , XMLData.value('*[1]/@FontStyle','VARCHAR(30)') AS FontStyle
FROM cte2
WHERE cte2.NodeType = 'Element';

推荐阅读