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
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>
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);
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply