Invalid Characters in XML

Question

Invalid Characters in XML

PNS

Right there with Babe

Points: 773
More actions
July 26, 2007 at 6:34 am

#70583

Hi
I am facing a problem storing a string that has some special characters in a XML type variable.
DECLARE
@d XML
SET
@d='¾ p-ØpBØ€,Ø 0â 0â 0âh2 0ëwúw 0h2G ; 0âh Ð ÿ ó ÔçØç GVúw 0 0â Xúw0 @âpå xæ ˜æ êýtˆ=ýt€æ õ iP@âÜ ``@âÜ 8 ë$`Q 8 ¤æ ¨è ?u° ?u„æ àç l'
SELECT
@D
It parses the string before storing it and gives the following error
XML parsing: line 1, character 22, illegal xml character
I cannot replace these characters before storing in the XML variable as this is coming from a free text column from the front end application that might have number of such characters.
Is there any way to handle these invalid characters at the Database level.
I have already tired using CDATA.It didnt help!
Thanks
PS

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

dmbaker SSCertifiable Points: 5068 More actions · Answer 1

Your XML data contains binary characters that are illegal in XML. As such, you don't have much choice but to do something with them. If you cannot just outright strip them, then you need to "encode" them.

A simple thing to do would be to Base 64-encode the whole string and then store that. And, when returning it, decode it from Base 64, which would return the original string.

dmbaker SSCertifiable Points: 5068 More actions · Answer 2

Might also help to share why you need to store this data as XML, or maybe what you are wanting to accomplish, as that might help everyone to help you to come up with an appropriate solution.

PNS Right there with Babe Points: 773 More actions · Answer 3

This is coming from a free text box from the front end alongwith other text and it is stored in the database in a column

The value is something like this

"Ideally located just a short drive down SR 160 from Las Vegas Blvd ( | l €xÙüé è¯7uøè1< (Ö ;7uøè1< (Ö¨!ÿÿÿÿ øè1< ÿÿÿÿ€ê Ð¥7u ˆL=u8hê ¨Û¨!pA<ê Šê \ê H ¼Ö 8 0 × "

These characters are seen when i generate the XML and while parsing i get the illegal character error.

How do i strip them off becuase these can be many.

steve smith-401573 SSChampion Points: 10889 More actions · Answer 4

Rather than using an XML declaration, could you use an NVARCHAR to store the values initially? Then you could manipulate the content to satisfy the XML rules - yes it's extra work, but if you are going to blend languages together, it will certainly get confusing.