Why does T-SQL OpenXML bust with certain characters?

Question

I have the following XML that I'm running in SQL Server, and it breaks, why?

 declare @xml varchar(max)
 declare @hDoc int

 set @xml = '<transaction>
 <item itemId="1" value="Hello World" />
 <item itemId="2" value="Hello &World" />
 <item itemId="3" value="Hello <World" />
 <item itemId="4" value="Hello >World" />
 <item itemId="5" value="Hello ’World" />
 </transaction>'

 exec sp_xml_preparedocument @hDoc OUTPUT, @xml

 select
     itemId
 ,   value
 from
     openxml(@hDoc, '/transaction/item')
    with (
      itemId int,
      value varchar(max)
     ) item

Answer 1

The values in the XML contain invalid characters. For XML in general you must escape the less than sign and the ampersand like so: < and &

 declare @xml varchar(max)
 declare @hDoc int

 set @xml = '<transaction>
 <item itemId="1" value="Hello World" />
 <item itemId="2" value="Hello &amp;World" />
 <item itemId="3" value="Hello &lt;World" />
 <item itemId="4" value="Hello >World" />
 </transaction>'

 exec sp_xml_preparedocument @hDoc OUTPUT, @xml

 select
     itemId
 ,   value
 from
     openxml(@hDoc, '/transaction/item')
    with (
      itemId int,
      value varchar(max)
     ) item

However when using openxml certain values won't work in general, specifically that curly apostrophe. I'm not sure what values are invalid, but I know that is one of them. So the solution is to use the native XML type in SQL Server 2005.

 declare @xml xml
 set @xml = '<transaction>
 <item itemId="1" value="Hello World" />
 <item itemId="2" value="Hello &amp;World" />
 <item itemId="3" value="Hello &lt;World" />
 <item itemId="4" value="Hello >World" />
 <item itemId="5" value="Hello ’World" />
 </transaction>'

 select
       item.value('@itemId', 'int')
     , item.value('@value', 'varchar(max)')
 from @xml.nodes('/transaction/item') [transaction](item)

Answer 2

If possible, assemble your XML document in the application. The framework (.Net, Java, ...?) will do the proper oncoding for you, and will yield valid XML.