时间戳

首页 > 解决方案 > SQL SERVER XML 查询不返回任何内容

sql-server - SQL SERVER XML 查询不返回任何内容

问题描述

每个或一些 ssrs 报告都有以下标签：

xmlns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"
xmlns:df="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily"
MustUnderstand="df"

除非我删除上面指定的属性，否则此查询不会返回任何数据。在给定的 xml 中，不能删除属性。

declare @content xml =
'<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:df="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily" MustUnderstand="df">
  <DataSets>
    <DataSet Name="DS">
      <Query>
        <CommandText>rep.my_proc</CommandText>
      </Query>
    </DataSet>
  </DataSets>
</Report>'

  select 
      'DB growth' as ReportName, 
      CommandText = x.value('(Query/CommandText)[1]','VARCHAR(250)')
  from  @content.nodes('Report/DataSets/DataSet') r (x)

该查询是否有任何错误？

标签： sql-server

解决方案

看一眼WITH XMLNAMESPACES

declare @content xml =
'<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition"     xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:df="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily" MustUnderstand="df">
  <DataSets>
    <DataSet Name="DS">
      <Query>
        <CommandText>rep.my_proc</CommandText>
      </Query>
    </DataSet>
  </DataSets>
</Report>'

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition')
select 
    'DB growth' as ReportName, 
    CommandText = x.value('(Query/CommandText)[1]','VARCHAR(250)')
from @content.nodes('Report/DataSets/DataSet') r (x)

推荐阅读