Home > SSRS Expert > SSRS #45 – There is an error in XML document

SSRS #45 – There is an error in XML document

I’ve been working lots of hours lately because our project is just about to go into production. One of my ETL processes has gone through many changes recently because business users are now very actively sending us their late minutes requirements.

After making some changes to my ETL process, and re-loading all the historical data, I discovered that one of my SSRS reports was no longer working.

The Issue

Here is the error message from the reporting server (the report has no problem running in BIDS):

There is an error in XML document (1, 6407).
”, hexadecimal value 0x16, is an invalid character. Line 1, position 6407.

clip_image002

 

This is my first time encountering this kind of error. Not knowing the root cause, I spent the next hour or so trying to “fix” it. I tried all the possible ways I could think of, such as trying to find that unprintable character in the RDL/XML file, re-loading the RDL, renaming the RDL file, etc. No luck.

The Root Cause

Next morning, I started searching on Internet, and found many info on this error. The conclusion is that this is caused by a bug on the Reporting Services 2005. A service pack needs to be installed.

But it’s this article from Microsoft that revealed the root cause of the error.   

Error message when you try to browse a report in SQL Server 2005 Reporting Services: "The input XML is not well-formed"

image

This problem occurs because a nonprintable character is populated into a report parameter that you define in the report.

Knowing my data and report very well, it didn’t take me long to find the report parameter that has a nonprintable character in it.

image

The Solution

I would not wait for the architecture team to apply the service pack. The Microsoft support article also gave you workaround in SSRS. I don’t want to do that either. I am a SQL developer so it’s natural for me to resolve it in SQL.

I decided to replace the non printable character by an empty space. Something like this:

REPLACE(myCOLUMNS, CHAR(9), ”)

If you are in the SQL world for awhile, you know that the CHAR function with the right number (which is the ASCII value for the character) will give you special non printable characters.

Some common special non printable characters:

Tab: char(9)
Line feed: char(10)
Carriage return: char(13)

What about the one in my data? What kind of non printable character it is? I tried the above three common ones, no luck.

I need to use another SQL function ASCII to tell me that. It turned that the ASCII value for it is 22.

image

Here comes to my final solution:

REPLACE(myCOLUMNS, CHAR(22), ”)

or I could just simply use:

REPLACE(myCOLUMNS, ”, ”)

Final notes before I close this post:

1. The SQL functions CHAR and ASCII are similar to the Chr and Asc functions within VBScript.

2. If you want to replace ANY non-printable characters in your data, leaving only numbers and printable letters, you would need to do some programming.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: