Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to preserve an ampersand (&) while using FOR XML PATH on SQL 2005

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 '&amp;'

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&amp;raw=1&amp;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 '&amp;'?

like image 746
Duffy Avatar asked Nov 07 '08 01:11

Duffy


People also ask

How do you handle ampersand in SQL?

Use an ampersand (&) to identify each variable in your SQL statement. You do not need to define the value of each variable. Toggling the display of the text of a command before and after SQL*Plus replaces substitution variabfes with values.

How do you represent an ampersand in XML?

xml version="1.0"?> An ampersand a character reference can also be escaped as &amp; in element content of XML.


2 Answers

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; 
like image 161
Janmonn Avatar answered Sep 23 '22 14:09

Janmonn


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 &amp;. See the XML specification for more information.

When your XML parser parses this string out of XML, it will understand the &amp; 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).

like image 43
ykaganovich Avatar answered Sep 25 '22 14:09

ykaganovich