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

我有这个查询：

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循环。还是有更好/更优雅的方式来做到这一点？

请您参考如下方法:

您可以尝试使用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