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 '&
'?
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.
xml version="1.0"?> An ampersand a character reference can also be escaped as & in element content of XML.
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;
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With