How to preserve an ampersand (&) while using FOR XML PATH on SQL 2005
Solution 1
What SQL Server generates is correct. What you expect to see is not well-formed XML. The reason is that &
character signifies the start of an entity reference, such as &
. See the XML specification for more information.
When your XML parser parses this string out of XML, it will understand the &
entity references and return the text back in the form you want. So the internal format in the XML file should not cause a problem to you unless you're using a buggy XML parser, or trying to parse it manually (in which case your current parser code is effectively buggy at the moment with respect to the XML specification).
Solution 2
There are situations where a person may not want well formed XML - the one I (and perhaps the original poster) encountered was using the For XML Path technique to return a single field list of 'child' items via a recursive query. More information on this technique is here (specifically in the 'The blackbox XML methods' section): Concatenating Row Values in Transact-SQL
For my situation, seeing 'H&E' (a pathology stain) transformed into 'well formed XML' was a real disappointment. Fortunately, I found a solution... the following page helped me solve this issue relatively easily and without having re-architect my recursive query or add additional parsing at the presentation level (for this as well for as other/future situations where my child-rows data fields contain reserved XML characters): Handling Special Characters with FOR XML PATH
EDIT: code below from the referenced blog post.
select
stuff(
(select ', <' + name + '>'
from sys.databases
where database_id > 4
order by name
for xml path(''), root('MyString'), type
).value('/MyString[1]','varchar(max)')
, 1, 2, '') as namelist;
Solution 3
Try this....
select
stuff(
(select ', <' + name + '>'
from sys.databases
where database_id > 4
order by name
for xml path(''), root('MyString'), type
).value('/MyString[1]','varchar(max)')
, 1, 2, '') as namelist;
Duffy
Updated on March 16, 2020Comments
-
Duffy over 4 years
Are there any tricks for preventing SQL Server from entitizing chars like &, <, and >? I'm trying to output a URL in my XML file but SQL wants to replace any '&' with '
&
'Take the following query:
SELECT 'http://foosite.com/' + RTRIM(li.imageStore) + '/ImageStore.dll?id=' + RTRIM(li.imageID) + '&raw=1&rev=' + RTRIM(li.imageVersion) AS imageUrl FROM ListingImages li FOR XML PATH ('image'), ROOT ('images'), TYPE
The output I get is like this (&s are entitized):
<images> <image> <imageUrl>http://foosite.com/pics4/ImageStore.dll?id=7E92BA08829F6847&raw=1&rev=0</imageUrl> </image> </images>
What I'd like is this (&s are not entitized):
<images> <image> <imageUrl>http://foosite.com/pics4/ImageStore.dll?id=7E92BA08829F6847&raw=1&rev=0</imageUrl> </image> </images>
How does one prevent SQL server from entitizing the '&'s into '
&
'?