Why FOR XML PATH does not escape " or ' but does < or > or &

Vladimir Moldovanenko 276 Reputation points
2024-11-25T15:22:53.06+00:00

Hi there.

I am puzzled by this behavior

SELECT

 v.sc as [data()]

FROM (VALUES

 ('"'  , '&quot;')

,('''' , '&apos;')

,('<'  , '&lt;')

,('>'  , '&gt;')

,('&'  , '&amp;')

) v (sc, sce)

FOR XML PATH(''), TYPE

The result: " ' < > &
I am expecting: " ' < > &

Any reason?

How to make it entitize values properly?

Is this a bug?

Thank you.

Have a great day.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,133 questions
{count} votes

Accepted answer
  1. Bruce (SqlWork.com) 67,921 Reputation points
    2024-11-25T17:28:08.93+00:00

    Xml does not require single quote to be quoted. XML only require quoting of double quotes when the double quote is in an attribute value.

    <root v="this require quoted double quote(&quot;)">double quote ok (")<root>
    
    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.