SQL Server XML列解析失败求助：含defaultfontfamily命名空间

在SQL Server的Catalog表中处理报表XML时，部分行无法正常解析。排查发现这些异常行的XML使用了http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily命名空间，且包含额外的<df:DefaultFontFamily>节点，添加该命名空间后问题仍未解决。

正常行XML示例 #

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

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

用户原SQL代码 #

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

问题原因与解决方案 #

问题核心是默认命名空间不匹配：异常行使用2016版报表定义命名空间http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition，而原代码仅默认指定2010版命名空间，导致2016版XML节点无法被识别。

方案1：显式兼容两个版本命名空间 #

适合需要区分报表版本的场景，兼容性更强：

WITH XMLNAMESPACES 
( 
    'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' AS ns2010,
    'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition' AS ns2016,
    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd,
    'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily' AS df
)
SELECT  
    CATDATA.Name AS ReportName,
    CATDATA.Path AS ReportPathLocation,
    COALESCE(
        xmlcolumn2010.value('(@Name)[1]', 'VARCHAR(250)'),
        xmlcolumn2016.value('(@Name)[1]', 'VARCHAR(250)')
    ) AS DataSetName,
    COALESCE(
        xmlcolumn2010.value('(ns2010:Query/ns2010:DataSourceName)[1]','VARCHAR(250)'),
        xmlcolumn2016.value('(ns2016:Query/ns2016:DataSourceName)[1]','VARCHAR(250)')
    ) AS DataSourceName,
    COALESCE(
        xmlcolumn2010.value('(ns2010:Query/ns2010:CommandText)[1]','VARCHAR(2500)'),
        xmlcolumn2016.value('(ns2016:Query/ns2016: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('/ns2010:Report/ns2010:DataSets/ns2010:DataSet') xmltable2010 ( xmlcolumn2010 )
OUTER APPLY reportXML.nodes('/ns2016:Report/ns2016:DataSets/ns2016:DataSet') xmltable2016 ( xmlcolumn2016 )
WHERE xmlcolumn2010.exist('.') = 1 OR xmlcolumn2016.exist('.') = 1
ORDER BY CATDATA.Name

方案2：使用通配符忽略命名空间差异 #

适合仅需提取节点内容、不关注版本的场景，代码更简洁：

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 )
ORDER BY CATDATA.Name

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