查询XML列中的一对多关系

Question

我在一个表中有一个XML列。

该表看起来有两列：

• ID
• 部门XML

DepartmentXML通常如下所示：

<Root>
<Department>
<dID>100</dID>
<DName>Engineering</DName>
</Department>
<Employee>
<EmployeeID>999</EmployeeID>
<EName>AAA BBB</EName>
</Employee>
<Employee>
<EmployeeID>888</EmployeeID>
<EName>XXX YYY</EName>
</Employee>
</Root>

如何查询这个XML，得到这样的结果？

+------------------------------------------+
|dID|DepartmentName|EmployeeID|EmployeeName|
+------------------------------------------+
|100|Engineering   |999       |AAA BBB     |
|100|Engineering   |888       |XXX YYY     |
+------------------------------------------+

我知道可能必须使用CROSS APPLY，但这个特定场景的语法对我来说很难理解。

谢谢。

Answer 1

这样试试：

首先是模型表来模拟您的问题：

DECLARE @tbl TABLE(ID INT IDENTITY, DepartmentXml XML);
INSERT INTO @tbl VALUES
(N'<Root>
<Department>
<dID>100</dID>
<DName>Engineering</DName>
</Department>
<Employee>
<EmployeeID>999</EmployeeID>
<EName>AAA BBB</EName>
</Employee>
<Employee>
<EmployeeID>888</EmployeeID>
<EName>XXX YYY</EName>
</Employee>
</Root>');

--查询

SELECT t.ID
,t.DepartmentXml.value('(/Root/Department/dID/text())[1]','int') AS DepartmentId
,t.DepartmentXml.value('(/Root/Department/DName/text())[1]','nvarchar(max)') AS DepartmentName
,A.e.value('(EmployeeID/text())[1]','int') AS EmployeeId
,A.e.value('(EName/text())[1]','nvarchar(max)') AS EmployeeName
FROM @tbl t
OUTER APPLY t.DepartmentXml.nodes('/Root/Employee') A(e);

简而言之：

• 我们可以直接选择行的ID
• 我们可以直接从XML中读取部门信息(不重复(
• 我们可以使用APPLY和.nodes()来检索重复节点
• 我们可以对A.e使用相对XPath来获取员工的数据