sql-server - 自连接数据到分层 XML

Question

我在 SQL Server 2012 中有一个表，其中包含这样的自连接数据:

 ID   | ChildA_ID | ChildB_ID
(int) |   (int)   |   (int)
-----------------------------
 1    |           |     
 2    | 1         |     
 3    | 1         |     
 4    | 3         |     
 6    | 2         | 4
 7    | 6         |     

使用 T-SQL(或者，在紧要关头，使用 CLR 代码)，我想将其转换为如下所示的 XML:

<row>
  <ID>7</ID>
  <ChildA>
    <row>
      <ID>6</ID>
      <ChildA>
        <row>
          <ID>2</ID>
          <ChildA>
            <row>
              <ID>1</ID>
            </row>
          </ChildA>
        </row>
      </ChildA>
      <ChildB>
        <row>
          <ID>4</ID>
          <ChildA>
            <row>
              <ID>3</ID>
              <ChildA>
                <row>
                  <ID>1</ID>
                </row>
              </ChildA>
            </row>
          </ChildA>
        </row>
      </ChildB>
    </row>
  </ChildA>
</row>

我对 SQL Server 中的递归 CTE 和 FOR XML 子句有相当多的经验，但这个让我很困惑。 (我不排除我只是在装傻的可能性。)

欢迎提出任何建议，包括为实现预期结果而构建数据的替代方法。 (但是请注意，“ChildA”和“ChildB”代表不同类型的父子关系，而不是反规范化。)

编辑:

当用我的示例数据尝试 Mitch 的解决方案时，我遇到了一些重复的行:

<row>
  <ID>7</ID>
  <ChildA>
    <row>
      <ID>6</ID>
      <ChildA>
        <row>
          <ID>2</ID>
          <ChildA>
            <row>
              <ID>1</ID>
            </row>
          </ChildA>
        </row>
<!--This is the first duplicate row:-->
        <row>
          <ID>2</ID>
          <ChildA>
            <row>
              <ID>1</ID>
            </row>
          </ChildA>
        </row>
      </ChildA>
      <ChildB>
        <row>
          <ID>4</ID>
          <ChildA>
<!--This is the second "duplicate" row:-->
            <row>
              <ID>3</ID>
            </row>
            <row>
              <ID>3</ID>
              <ChildA>
                <row>
                  <ID>1</ID>
                </row>
              </ChildA>
            </row>
          </ChildA>
        </row>
      </ChildB>
    </row>
  </ChildA>
</row>

编辑 2:

这是我用来生成上述结果的代码(Mitch 解决方案的略微修改版本):

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Nodes') 
    CREATE TABLE Nodes (id int, leftid int, rightid int);

DELETE FROM Nodes

INSERT INTO Nodes
VALUES (1, null, null),
       (2, 1, null),
       (3, 1, null),
       (4, 3, null), 
       (6, 2, 4),
       (7, 6, null);

CREATE TABLE #xml (id int, depth int, data xml);

DECLARE @rootNode int = 7;

WITH cte (cid, depth, child) as 
(
  -- anchor
  SELECT id, 1, leftid
  FROM nodes
  WHERE id = @rootNode and leftid is not null
  UNION ALL 
  SELECT id, 1, rightid
  FROM nodes
  WHERE id = @rootNode and rightid is not null
  UNION ALL 
  -- recursive
  SELECT n.id, depth + 1, leftid
  FROM cte c
  INNER JOIN nodes n on c.child = n.id
  WHERE leftid is not null or (rightid is null and leftid is null)
  UNION ALL 
  SELECT n.id, depth + 1, rightid
  FROM cte c
  INNER JOIN nodes n on c.child = n.id
  WHERE rightid is not null
)
INSERT INTO #xml (id, depth, data)
SELECT DISTINCT cid, depth, null
FROM CTE;

DECLARE @maxDepth int;
SET @maxDepth = (SELECT MAX(depth) FROM #xml)

WHILE @MaxDepth > 0
BEGIN
  UPDATE xu
  SET data = (
    SELECT n.ID, xl.data as ChildA, xr.data as ChildB
    FROM Nodes n
    LEFT OUTER JOIN #xml xl on n.leftid = xl.id
    LEFT OUTER JOIN #xml xr on n.rightid = xr.id
    WHERE xu.id = n.id
    FOR XML PATH('row'), TYPE
  )
  FROM #xml xu
  WHERE xu.Depth = @MaxDepth

  SET @MaxDepth = @MaxDepth - 1
END

SELECT data FROM #xml WHERE id = @RootNode

DROP TABLE #xml

Answer 1

您可以通过深度优先搜索来进行此类翻译。使用递归 CTE 计算深度，然后循环转换为 XML:

Example SQL (Fiddle):

CREATE TABLE Nodes (id int, leftid int, rightid int, name varchar(256));

INSERT INTO Nodes
VALUES (1, 2, 3, 'abcd'),
       (2, 4, 5, 'ab'),
       (4, null, null, 'a'), 
       (5, null, null, 'b'),
       (3, 6, 7, 'cd'),
       (6, null, null, 'c'),
       (7, null, null, 'd');

CREATE TABLE #xml (id int, depth int, data xml, parent int);

DECLARE @rootNode int = 1;

WITH cte (cid, depth, child, parent) as 
(
  -- anchor
  SELECT id, 1, leftid, cast(null as int)
  FROM nodes
  WHERE id = @rootNode and leftid is not null
  UNION ALL 
  SELECT id, 1, righted, null
  FROM nodes
  WHERE id = @rootNode and rightid is not null
  UNION ALL 
  -- recursive
  SELECT n.id, depth + 1, leftid, c.cid
  FROM cte c
  INNER JOIN nodes n on c.child = n.id
  WHERE leftid is not null or (rightid is null and leftid is null)
  UNION ALL 
  SELECT n.id, depth + 1, rightid, c.cid
  FROM cte c
  INNER JOIN nodes n on c.child = n.id
  WHERE rightid is not null
)
INSERT INTO #xml (id, depth, data, parent)
SELECT DISTINCT cid, depth, null, parent
FROM CTE;

DECLARE @maxDepth int;
SET @maxDepth = (SELECT MAX(depth) FROM #xml)

WHILE @MaxDepth > 0
BEGIN
  UPDATE xu
  SET data = (
    SELECT n.Name, xl.data as [Left], xr.data as [Right]
    FROM Nodes n
    LEFT OUTER JOIN #xml xl on n.leftid = xl.id and xl.parent = n.id
    LEFT OUTER JOIN #xml xr on n.rightid = xr.id and xr.parent = n.id
    WHERE xu.id = n.id
    FOR XML PATH('TreeNode'), TYPE
  )
  FROM #xml xu
  WHERE xu.Depth = @MaxDepth

  SET @MaxDepth = @MaxDepth - 1
END

SELECT data FROM #xml WHERE id = @RootNode

产生:

<TreeNode>
  <Name>abcd</Name>
  <Left>
    <TreeNode>
      <Name>ab</Name>
      <Left>
        <TreeNode>
          <Name>a</Name>
        </TreeNode>
      </Left>
      <Right>
        <TreeNode>
          <Name>b</Name>
        </TreeNode>
      </Right>
    </TreeNode>
  </Left>
  <Right>
    <TreeNode>
      <Name>cd</Name>
      <Left>
        <TreeNode>
          <Name>c</Name>
        </TreeNode>
      </Left>
      <Right>
        <TreeNode>
          <Name>d</Name>
        </TreeNode>
      </Right>
    </TreeNode>
  </Right>
</TreeNode>