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

2uluyalo 于 2023-06-04 发布在 SQL Server

关注(0)|答案(1)|浏览(146)

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:DefaultFontFamilySegoe 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

sql-server

来源：https://stackoverflow.com/questions/76350485/cannot-parse-sql-server-xml-column-when-reportdefinition-is-defaultfontfamily

1条答案

按热度按时间

mwngjboj1#

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 )

赞(0）回复(0）举报 2023-06-04

我来回答

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

1条答案

相关问题

热门标签

最新问答