sql - 如何获取 XML 节点的索引/位置

Question

我正在尝试在使用 sql 选择值时获取 xml 节点的索引:

这是代码:

declare @myxml xml ='
<Departments>
    <Department>
        <Employee>
            A
        </Employee>
        <Employee>
            B
        </Employee>
    </Department>

    <Department>
        <Employee>
            C
        </Employee>
        <Employee>
            D
        </Employee>
    </Department>
</Departments>'

Select Emp = m.value('.','varchar(30)') 

from @myxml.nodes('Departments/Department/Employee') X(m)

上述查询的输出:

Emp 
A
B
C
D

预期输出:

Emp  Department_Index

A         1
B         1
C         2
D         2

即我想要与部门下的每个员工对应的部门索引。
这里员工 A 和 B 属于部门 1 部门，员工 C 和 D 属于部门 2 部门。

因此，我希望将复杂的 XML 子项与不存在唯一键的父项连接起来。

Answer 1

这是一个解决方案，我声称它适用于所有场景 - 尽管不能保证绑定(bind)到 <Department> 的数字节点通过 ROW_NUMBER()将反射(reflect)其在每种情况下的真实位置(请参阅下面的备注和链接):

declare @myxml xml ='
<Departments>
    <Department>
        <Employee>
            A
        </Employee>
        <Employee>
            B
        </Employee>
    </Department>

    <Department>
        <Employee>
            C
        </Employee>
        <Employee>
            D
        </Employee>
    </Department>
</Departments>';

--查询将使用CTE将数字绑定(bind)到第一级并整体传递内部节点。最终的 SELECT 将使用传递过来的 <Department>节点并选择其员工:

WITH NumberedDepartment AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS DepInx
          ,d.query(N'.') AS DepartmentNode
    FROM @myxml.nodes(N'/Departments/Department') AS A(d)
)
SELECT DepInx 
      ,e.value(N'text()[1]','nvarchar(max)') AS Employee
FROM NumberedDepartment
CROSS APPLY DepartmentNode.nodes(N'Department/Employee') AS B(e);

如果您想了解排序顺序的保证，请 might read this thread .特别值得阅读: The chat below John Cappellettis answer .在那里，我提供了一种使用 XQuery 的方法，以及另一种方法，使用计数/数字表来选择其位置的元素。但这是相当复杂和缓慢的。
更新:一种保证排序顺序的方法
这种方法将即时创建一个计数表。如果你有一个数字表，那就更好了...... TOP子句会将这个计数限制为 <Department> 的实际计数节点。请务必使用至少包含所需行数的源表(我使用 master..spt_values)。
第一次申请将使用 .query()连同 sql:column()为每个号码获取正确的部门节点。第二个申请将读取相关员工。

WITH Tally(Nmbr) AS
(
    SELECT TOP (SELECT @myxml.value(N'count(/Departments/Department)','int'))
           ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
    FROM master..spt_values --just a pre-filled table with many rows
)
SELECT Nmbr
      ,e.value(N'text()[1]','nvarchar(max)') AS Employee
FROM Tally
OUTER APPLY(SELECT @myxml.query(N'/Departments/Department[sql:column("Nmbr")]')) AS A(ds)
OUTER APPLY ds.nodes(N'Department/Employee') AS B(e);