June 4, 2013 at 9:17 am
Hi
I'm trying to execute a script in SSMS using SQL Server 2008 R2, but it's returning the following error message:"Msg 9420, Level 16, State 1, Line 1
XML parsing: line 1, character 3, illegal xml character".
I suspect I need to set encoding to '<?xml version="1.0" encoding="iso-8859-1" ?>'. However, I don't know how to do this and I can't waste the rest of the day on it. Can anyone help please?
The code is as follows:
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
name,
x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,
x.value('CommandText[1]','VARCHAR(50)') AS CommandText
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/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(MAX)') = 'StoredProcedure'
Many thanks.
C
June 5, 2013 at 3:36 am
Does the simple cast to xml in the subquery work as a standalone query? (Tried it on my test box and not having any issues).
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 5, 2013 at 3:45 am
Hi Matthew
Sorry, I should have mentioned that. No it doesn't execute OK - error message is:
Msg 9420, Level 16, State 1, Line 1
XML parsing: line 1, character 3, illegal xml character
....which made me think there must be a way around the illegal character (whatever that is)...
Cheers
C
June 5, 2013 at 4:05 am
Ok, well, this may or may not work, but you could try changing the encoding in the content column with a replace statement (you may need to check that it's down as utf-8 in your case first; simply cast to varchar(max) will do it:-
SELECT name
, CAST(REPLACE(CAST(CAST(content AS VARBINARY(MAX)) AS varchar(max)), '<?xml version="1.0" encoding="utf-8"?>' , '<?xml version="1.0" encoding="iso-8859-1"?>') AS xml)
FROM ReportServer.dbo.Catalog
Alternatively, you could go the more laborious route of casting to varchar(max) and then searching for invalid xml characters?
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 5, 2013 at 4:59 am
Hi Matthew
Thanks for that, but I get the same error as before.
I shall come back to this later, but thanks for your help - it's pointed me (or our dba) in the right direction.
Cheers.
C
June 5, 2013 at 5:10 am
No worries; I think it's a case of finding out which rows contain the invalid character and then excluding them. Though I'd also be interested how they got in there in the first place (SSRS isn't my biggest skillset).
I'll see if I can do some more digging around later; but if you do get it sorted I'd be interested to know how and where the problem originated from.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 5, 2013 at 5:18 am
The XML being retrieved is actually the content of reporting services' .rdl files. I suspect there may be some wierd image reference or somesuch in there, although I'm guessing of course.
For now I shall try and exclude the affected rows, but I think it may be quicker to actually go through the .rdl code in an editor than to retrieve it this way.
If I (ever) find out the root of the problem I shall come back to you.
Thanks again for your help.
Cheers.
C
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply