SQL Server XML查询仅返回1行而非9行的原因排查

我参考多篇技术资料后，使用带命名空间的T-SQL语句查询包含9个<EmbeddedImage>节点的XML数据，但执行结果仅返回1行而非预期的9行，请问这是什么原因？

XML源数据（已移除实际ImageData） #

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <EmbeddedImages>
    <EmbeddedImage Name="RioTinto">
      <MIMEType>image/jpeg</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
    <EmbeddedImage Name="Palweb">
      <MIMEType>image/jpeg</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
    <EmbeddedImage Name="SABS1">
      <MIMEType>image/png</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
    <EmbeddedImage Name="SABS2">
      <MIMEType>image/jpeg</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
    <EmbeddedImage Name="ISC">
      <MIMEType>image/png</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
    <EmbeddedImage Name="ISO_9001_ISO_14001_COL">
      <MIMEType>image/jpeg</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
    <EmbeddedImage Name="GSCMPMCHeader">
      <MIMEType>image/png</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
    <EmbeddedImage Name="ISO_9001_ISO_14001_COL1">
      <MIMEType>image/png</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
    <EmbeddedImage Name="TopEmployer2019">
      <MIMEType>image/png</MIMEType>
      <ImageData>
      </ImageData>
    </EmbeddedImage>
  </EmbeddedImages>
</Report>

执行的原查询语句 #

WITH XMLNAMESPACES
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
)
SELECT lvV.Name,
       reportdata.item.value('(./EmbeddedImage)[1]', 'VARCHAR(2000)') AS EmbeddedImages,
       reportdata.item.value('(./EmbeddedImage/@Name)[1]', 'VARCHAR(2000)') AS Name,
       reportdata.item.value('(./EmbeddedImage/MIMEType)[1]', 'VARCHAR(2000)') AS MIMEType,
       reportdata.item.value('(./EmbeddedImage/ImageData)[1]', 'VARCHAR(2000)') AS ImageData
FROM @LysvanVerslae AS lvV
    cross APPLY lvV.VerslagRDL.nodes('/Report/EmbeddedImages') AS reportdata(item);

你的查询里，nodes('/Report/EmbeddedImages')定位的是单个<EmbeddedImages>父节点，而非内部的9个<EmbeddedImage>子节点。CROSS APPLY只会返回1行对应这个父节点，后续.value()方法仅从该父节点中取第一个<EmbeddedImage>的内容，所以最终只得到1行结果。

将nodes()的路径修改为/Report/EmbeddedImages/EmbeddedImage，直接遍历每个<EmbeddedImage>子节点，同时调整.value()方法的路径为相对当前节点的写法：

WITH XMLNAMESPACES
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
)
SELECT lvV.Name,
       reportdata.item.value('@Name', 'VARCHAR(2000)') AS EmbeddedImageName,
       reportdata.item.value('(MIMEType)[1]', 'VARCHAR(2000)') AS MIMEType,
       reportdata.item.value('(ImageData)[1]', 'VARCHAR(2000)') AS ImageData
FROM @LysvanVerslae AS lvV
    CROSS APPLY lvV.VerslagRDL.nodes('/Report/EmbeddedImages/EmbeddedImage') AS reportdata(item);

关键修正点 #

• 节点定位调整：nodes('/Report/EmbeddedImages/EmbeddedImage')会生成9行记录，每行对应一个<EmbeddedImage>节点
• 路径简化：当前item已是<EmbeddedImage>节点，直接访问@Name属性和MIMEType、ImageData子节点即可，无需多余的./EmbeddedImage前缀
• 属性取值优化：属性本身是唯一的，不需要加[1]索引

内容的提问来源于stack exchange，提问作者Danie Schoeman