XML encoding issue with single apostrophes in FOR XML PATH

Question

Post reply

XML encoding issue with single apostrophes in FOR XML PATH

ian 43089

Old Hand

Points: 350
More actions
October 16, 2014 at 10:48 am

#311824

Here is my problem:-
declare @test-2 as varchar(32)
declare @test2 as varchar(32)
set @test-2='today''s problem'
set @test2='my <string> '
select @test-2 as '@attribute' for xml path ('myrow')
select @test2 as '@attribute' for xml path ('myrow')
I want for xml path to correctly encode the single apostrophe as &apos but the single apostrophe doesn't get encoded. In the second example the greater and less than does get encoded.
Any ideas greatly appreciated.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

Correctly encode the single quote as &apos ?

Not so sure that it would be correct anywhere except in an attribute that had been enclosed in single quotes itself?

If it is causing you a problem because you are passing it around as a string and therefore the single quote is a problem, then address that.

Within SQL Server / T-SQL there shouldn't be any need to encode ' as &apos because it uses double-quote (") to enclose attribute values.

Is there some other problem it is causing you?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ian 43089 Old Hand Points: 350 More actions · Answer 2

Thanks for the reply.

The problem is that I'm using TSQL to get some query data (that contains single quotes) and I need to return that as valid XML with that data represented in am XML attribute. I'm expecting that XML to be valid and the single quotes to be properly escaped (single quotes should be escaped in XML attributes http://www.w3.org/TR/xml/#syntax).

Another nice list of references here:-http://stackoverflow.com/questions/1091945/what-characters-do-i-need-to-escape-in-xml-documents

SQL server correctly escapes, <, >, & and " but doesn't escape '

Cheers

Ian

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

Quick note, SQL Server does correctly encode all characters that need encoding, consider this example

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @SAMPLE_SIZE INT = 15;

;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

SELECT

NM.N + 32 AS '@ASCII'

,CHAR(NM.N + 32) AS '@VALUE'

FROM NUMS NM

FOR XML PATH('CHAR'), ROOT('CHAR_LIST'), TYPE;

Results

<CHAR_LIST>

<CHAR ASCII="33" VALUE="!" />

<CHAR ASCII="34" VALUE=""" />

<CHAR ASCII="35" VALUE="#" />

<CHAR ASCII="36" VALUE="$" />

<CHAR ASCII="37" VALUE="%" />

<CHAR ASCII="38" VALUE="&" />

<CHAR ASCII="39" VALUE="'" />

<CHAR ASCII="40" VALUE="(" />

<CHAR ASCII="41" VALUE=")" />

<CHAR ASCII="42" VALUE="*" />

<CHAR ASCII="43" VALUE="+" />

<CHAR ASCII="44" VALUE="," />

<CHAR ASCII="45" VALUE="-" />

<CHAR ASCII="46" VALUE="." />

<CHAR ASCII="47" VALUE="/" />

</CHAR_LIST>

mister.magoo SSC-Forever Points: 47068 More actions · Answer 4

ian 43089 (10/17/2014)
Thanks for the reply.
The problem is that I'm using TSQL to get some query data (that contains single quotes) and I need to return that as valid XML with that data represented in am XML attribute. I'm expecting that XML to be valid and the single quotes to be properly escaped (single quotes should be escaped in XML attributes http://www.w3.org/TR/xml/#syntax).
Another nice list of references here:-http://stackoverflow.com/questions/1091945/what-characters-do-i-need-to-escape-in-xml-documents
SQL server correctly escapes, <, >, & and " but doesn't escape '
Cheers
Ian

I think your expectation is not backed up by your reference :

the apostrophe or single-quote character (') may be represented as " &apos ; "

SQL Server uses (") to delimit attributes, so any xml it produces will not convert a single quote to &apos ; as it is not required. It is valid xml output. You should not have any problem using the xml produced from SQL Server in any other system.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);