sql - 在 SQL 中从 XML 中提取元素

Question

我正在尝试找出如何提取嵌套的 XML 元素并将其转换为 SQL 中的表结果。一个 WorkItem 总是有一个组，但是组字段可以嵌套 N 次。有没有一种简单的方法可以将字段从 XML 字段中提取出来，如下所示:

declare @xml XML = '
<WorkItem>
  <Group Name="Base" >
    <Field FieldId="361" Name="Assigned To"  />
    <Field FieldId="362" Name="Stuff"  />
    <Group Name="Detail">
      <Field FieldId="363" Name="Assigned To 2" />
    </Group>
  </Group>
</WorkItem>'

declare @handle int
declare @status int

exec @status = sp_xml_preparedocument @handle output, @xml
select *
from openxml(@handle, 'WorkItem/Group/Field')
with (
    FieldId int,
    Name varchar(max)
)
exec sp_xml_removedocument @handle

我得到的:

361,Assigned To
362,Stuff

我的期望:

361,Assigned To
362,Stuff
363,Assigned To 2

谢谢!

Answer 1

我觉得用XQuery更好:

DECLARE @xml XML = '
<WorkItem>
  <Group Name="Base" >
    <Field FieldId="361" Name="Assigned To"  />
    <Field FieldId="362" Name="Stuff"  />
    <Group Name="Detail">
      <Field FieldId="363" Name="Assigned To 2" />
    </Group>
  </Group>
</WorkItem>';


SELECT 
    n.value('@FieldId', 'int') FieldId, 
    n.value('@Name', 'varchar(250)') Name
FROM 
    @xml.nodes('/WorkItem//Field') xml(n);

输出:

FieldId     Name
----------- --------------------
361         Assigned To
362         Stuff
363         Assigned To 2