British pound sign (£) in xml causes error

[McGeeky]

Hi,

I am creating xml using for xml explicit. Some of the data contains the
British pound sign £. When later processing the xml I get errors about an
invalid character in the xml - its the pound sign. Why does for xml explicit
not escape it like it has done with other illegal characters? What is the
best way to deal with the pound signs?

Thanks

[McGeeky]

This query shows the problem:

select
1 as tag,
null as parent,
'£' as 'Root!1!Pound',
'<' as 'Root!1!LessThan'
for
xml explicit

Results in the following which is invalid XML:

<Root Pound="£" LessThan="&lt;"/>

The less than gets converted correctly but the pound sign doesn't. It should
come out as this

<Root Pound="&#163;" LessThan="&lt;"/>
--
McGeeky
http://mcgeeky.blogspot.com

Post by McGeeky
Hi,
I am creating xml using for xml explicit. Some of the data contains the
British pound sign £. When later processing the xml I get errors about an
invalid character in the xml - its the pound sign. Why does for xml
explicit not escape it like it has done with other illegal characters?
What is the best way to deal with the pound signs?
Thanks
--
McGeeky
http://mcgeeky.blogspot.com

[McGeeky]

I have found a work around. I don't know if this is the correct approach
though (feedback welcome). I add a directive to the generated XML file from
the for xml explicit to specify the encoding as follows:

<?xml version="1.0" encoding="ISO-8859-1"?>

This seems to fix the problem
--
McGeeky
http://mcgeeky.blogspot.com

Post by McGeeky
Hi,
I am creating xml using for xml explicit. Some of the data contains the
British pound sign £. When later processing the xml I get errors about an
invalid character in the xml - its the pound sign. Why does for xml
explicit not escape it like it has done with other illegal characters?
What is the best way to deal with the pound signs?
Thanks
--
McGeeky
http://mcgeeky.blogspot.com

[Michael Rys [MSFT]]

You may also want to write N'£' in your select clause instead of just '£' to
make sure that SQL Server does treat the character correctly.

BTW, in SQL Server 2005, this works.

Best regards
Michael

Post by McGeeky
I have found a work around. I don't know if this is the correct approach
though (feedback welcome). I add a directive to the generated XML file from
<?xml version="1.0" encoding="ISO-8859-1"?>
This seems to fix the problem
--
McGeeky
http://mcgeeky.blogspot.com

Post by McGeeky
Hi,
I am creating xml using for xml explicit. Some of the data contains the
British pound sign £. When later processing the xml I get errors about an
invalid character in the xml - its the pound sign. Why does for xml
explicit not escape it like it has done with other illegal characters?
What is the best way to deal with the pound signs?
Thanks
--
McGeeky
http://mcgeeky.blogspot.com