SQL SERVER XML query returns nothing

Question

Every or some ssrs reports have following tags:

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"

This query doesn't return any data unless I remove the attributes specified above. In the given xml, attributes can't be removed.

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)

Is there any error in that query?

Add WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition' ) before the SELECT — Martin Smith
– Martin Smith, Commented Jan 23, 2020 at 10:33

Misery · Accepted Answer · 2020-01-29 08:23:25Z

1

Take a look at 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)

edited Jan 29, 2020 at 8:23

answered Jan 23, 2020 at 10:56

Misery

4954 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL SERVER XML query returns nothing

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related