sql - SQL-为多个XML结果返回xpath

Question

我有这个查询：

select XMLMetadata from taObjectMetadata where ObjectMetadataTypeId = 1

返回3000多行，每行包含XML：

<objectMetaData>
  <fileLocation fileName="CM63951.mxf06092018233409;21.png" />
</objectMetaData>

我需要拉出该文件名。我可以使用XQuery轻松地对任何给定的行执行此操作：

declare @x XML = ('<objectMetaData>
  <fileLocation fileName="CM63951.mxf06092018233409;21.png" />
</objectMetaData>')

select x.value(N'@fileName', N'nvarchar(100)') as Filename
from @x.nodes(N'/objectMetaData/fileLocation') t(x)

这正是我所需要的。但是，对于表中的每个XML，我都需要此属性。尝试将查询放入声明XML中失败，因为它当然会返回多个结果。

我是否需要在这里使用WHILE循环。还是有更好/更优雅的方式来做到这一点？

Answer 1

您可以尝试使用CROSS APPLY

select x.value('@fileName', N'nvarchar(100)')  as Filename
FROM yourTable CROSS APPLY yourTable.data.nodes(N'objectMetaData/fileLocation') as t(x)

注意： yourTable可以代替您当前的结果集。

您的查询可能是这样的。

select x.value('@fileName', N'nvarchar(100)')  as Filename
FROM (
  select XMLMetadata 
  from taObjectMetadata 
  where ObjectMetadataTypeId = 1
)  t1 CROSS APPLY t1.XMLMetadata.nodes(N'objectMetaData/fileLocation') as t(x)

这是一个示例： sqlfiddle