Cannot parse SQL Server XML column when reportdefinition is "defaultfontfamily"

I am using an XML column in a SQL Server table. There are certain rows where the XML is not properly parsed, and I have identified why - for rows where it cannot be read, the XML is using 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily'. I have tried adding it as a namespace, but it did not help.

Those rows with no problem have xml namespaces as:

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <AutoRefresh>0</AutoRefresh>
  <DataSources>

...

Those rows with a problem have xml namespaces as:

<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">
  <df:DefaultFontFamily>Segoe UI</df:DefaultFontFamily>
  <AutoRefresh>0</AutoRefresh>
  <DataSources>

...

You can see when it works when xmlcolumn.values are assigned - these do not contain the additional line <df:DefaultFontFamily>Segoe UI</df:DefaultFontFamily>

Here is the SQL I am using. Thank you in advance.

WITH XMLNAMESPACES 
( DEFAULT 
  'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
, 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS ReportDefinition
, 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily' AS df)
SELECT  
CATDATA.Name AS ReportName
,CATDATA.Path AS ReportPathLocation
,xmlcolumn.value('(@Name)[1]', 'VARCHAR(250)') AS DataSetName  
,xmlcolumn.value('(Query/DataSourceName)[1]','VARCHAR(250)') AS DataSourceName 
,xmlcolumn.value('(Query/CommandText)[1]','VARCHAR(2500)') AS CommandText
,reportXML
FROM (  
	SELECT C.Name
	,c.Path
	,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
	FROM  ReportServer.dbo.Catalog C
	WHERE  C.Content is not null
	AND  C.Type = 2
	) CATDATA 
outer APPLY reportXML.nodes('/Report/DataSets/DataSet') xmltable ( xmlcolumn )
--WHERE xmlcolumn.value('(Query/CommandText)[1]','VARCHAR(250)') LIKE '%2_%'
ORDER BY CATDATA.Name

Your exact XML is unclear as you haven't shown us a full sample, so I've had to guess. It seems Report has a different default namespace.

You have three options:

• Either ignore the namespace completely using *:

  WITH XMLNAMESPACES 
  ( DEFAULT 
    'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
  , 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS ReportDefinition
  , 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily' AS df
  )
  SELECT  
    C.Name AS ReportName
   ,C.Path AS ReportPathLocation
   ,xmlcolumn.value('(@Name)[1]', 'VARCHAR(250)') AS DataSetName  
   ,xmlcolumn.value('(*:Query/*:DataSourceName/text())[1]','VARCHAR(250)') AS DataSourceName 
   ,xmlcolumn.value('(*:Query/*:CommandText/text())[1]','VARCHAR(2500)') AS CommandText
   ,reportXML
  FROM ReportServer.dbo.Catalog C
  CROSS APPLY (
      SELECT
        CONVERT(XML, CONVERT(VARBINARY(MAX), C.Content)) AS reportXML
  ) CATDATA 
  OUTER APPLY reportXML.nodes('/*:Report/*:DataSets/*:DataSet') xmltable ( xmlcolumn )
  WHERE C.Content is not null
    AND C.Type = 2
  ORDER BY C.Name
  

• Or you can use multiple .nodes

  WITH XMLNAMESPACES 
  ( DEFAULT 
    'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
  , 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition' AS r2016
  , 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS ReportDefinition
  , 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily' AS df)
  SELECT  
    C.Name AS ReportName
   ,C.Path AS ReportPathLocation
   ,ISNULL(
      xmlcolumn1.value('(@Name)[1]', 'VARCHAR(250)'),
      xmlcolumn2.value('(@Name)[1]', 'VARCHAR(250)')) AS DataSetName  
   ,ISNULL(
      xmlcolumn1.value('(Query/DataSourceName/text())[1]','VARCHAR(250)'),
      xmlcolumn2.value('(r2016:Query/r2016:DataSourceName/text())[1]','VARCHAR(250)')) AS DataSourceName 
   ,ISNULL(
      xmlcolumn1.value('(Query/CommandText/text())[1]','VARCHAR(2500)'),
      xmlcolumn2.value('(r2016:Query/r2016:CommandText/text())[1]','VARCHAR(2500)')) AS CommandText
   ,reportXML
  FROM ReportServer.dbo.Catalog C
  CROSS APPLY (
      SELECT
        CONVERT(XML, CONVERT(VARBINARY(MAX), C.Content)) AS reportXML
  ) CATDATA 
  OUTER APPLY reportXML.nodes('/Report/DataSets/DataSet') xmltable1 ( xmlcolumn1 )
  OUTER APPLY reportXML.nodes('/r2016:Report/r2016:DataSets/r2016:DataSet') xmltable2 ( xmlcolumn2 )
  WHERE C.Content is not null
    AND C.Type = 2
  ORDER BY C.Name
  

It's hard to say which version is faster. Ignoring namespaces is slow, but so is querying twice.

Note other improvements:

• Use of CROSS APPLY for the conversion to avoid nesting.
• Use of /text() to increase performance when doing XQuery.
• If you can change your column to actually be xml and avoid the conversion that would be good.

As a side note, if you actually wanted that WHERE, the best way to do it would be to put it inside the .nodes (note the use of CROSS APPLY here, not OUTER APPLY.

CROSS APPLY reportXML.nodes('
  /*Report/*:DataSets/*:DataSet
  [*:Query/*:CommandText/text()[contains(., "2_")]
  ') xmltable ( xmlcolumn )