none
Illegal character XMl RRS feed

  • Question

  • H

     

    I am runing foolwing query

     

    ;WITH xmlnamespaces (
        default
        'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
        'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
    )
    SELECT
        NAME AS reportname,
        q.value('@Name[1]', 'VARCHAR(50)') AS datasetname,
        x.value('DataSourceName[1]', 'VARCHAR(50)') AS datasourcename,
        x.value('CommandText[1]', 'VARCHAR(50)') AS spname
    FROM
    (
        SELECT NAME,
        CAST(CAST([content] AS VARBINARY(MAX)) AS xml)  AS reportxml
        FROM reportserver.dbo.catalog
    ) a
    CROSS apply reportxml.nodes('/Report/DataSets/DataSet') d(q)
    CROSS apply q.nodes('Query') r(x

    )

     

    Msg 9420, Level 16, State 1, Line 1
    XML parsing: line 3841, character 10, illegal xml character

    Monday, November 7, 2011 9:57 PM

Answers

  • Try and work out which row is causing the error by adding a WHERE clause to the inner SELECT.  When you find the row, try just casting it to VARCHAR(MAX) and inspect the XML.
    • Marked as answer by KJian_ Monday, November 14, 2011 6:56 AM
    Monday, November 7, 2011 11:16 PM
    Answerer