The special characters for quote ("), apostrophe ('), less-than (<), greater-than (>), and ampersand (&) are used for punctuation in XML, and are represented with predefined entities: ", ', <, >, and &. Notice that the semicolon is part of the entity.You cannot use "<" or "&" in attributes or elements, as the following series of examples demonstrates.
select xmlparse("<a atr='<'/>")
Msg 14702, Level 16, State 0:
Line 1:
XMLPARSE(): XML parser fatal error <<A '<' character cannot be used in attribute 'atr', except through <>> at line 1, offset 14.
select xmlparse("<a atr1='&'>")
Msg 14702, Level 16, State 0:
Line 1:
XMLPARSE(): XML parser fatal error
<<Expected entity name for reference>>
at line 1, offset 11
select xmlparse("<a> < </a>")
Msg 14702, Level 16, State 0:
Line 2:
XMLPARSE(): XML parser fatal error
<<Expected an element name>>
at line 1, offset 6.
select xmlparse(" & ")
Msg 14702, Level 16, State 0:
Line 1:
XMLPARSE(): XML parser fatal error
<<Expected entity name for reference>>
at line 1, offset 6.
Instead, use the predefined entities < and &, as follows:
select xmlextract("/",
"<a atr='< &'> < & </a>" )
--------------------------------
<a atr="< &"> < & </a>
You can use quotation marks within attributes delimited by apostrophes, and vice versa. These marks are replaced by the predefined entities " or '. In the following examples, notice that the quotation marks or apostrophes surrounding the word 'yes' are doubled to comply with the SQL character literal convention:
select xmlextract("/", "<a atr=' ""yes"" '/> " )
---------------------------------
<a atr=" "yes" "></a>
select xmlextract('/', '<a atr=" ''yes'' "/> ' )
----------------------------
<a atr=" 'yes' "></a>
You can use quotation marks and apostrophes within elements. They are replaced by the predefined entities " and &apol:, as the following example shows:
select xmlextract("/", " ""yes"" and 'no' " )
-------------------------------------
"yes" and 'no'
