XML parsing: line 1, character 23, illegal name character

Question

Does anyone know why this XML text throws an invalid character name error in SQL Server 2008?

'<cs><ca="2" b="CITY & STATE TX" c="CITY & STATE TX"/></cs>' 

Exception message

 Msg 9421, Level 16, State 1, Line 2 XML parsing: line 1, character 23, illegal name character 

The following is the query used to parse this XML

 DECLARE @CaptionsDescriptions XML = '<cs><ca="2" b="CITY & STATE TX" c="CITY & STATE TX"/></cs>' DECLARE @DocHandle int DECLARE @CaptionsDescriptionsTable TABLE ( ID INT IDENTITY(1,1), languageID INT, Caption VARCHAR(50), Description VARCHAR(2000) ) EXEC sp_xml_preparedocument @DocHandle OUTPUT,@CaptionsDescriptions INSERT INTO @CaptionsDescriptionsTable SELECT a,b,c FROM OPENXML(@DocHandle,'cs/c') WITH ( a int, -- language id b varchar(50), -- caption c varchar(2000) -- description ) -- remove document handler EXEC sp_xml_removedocument @DocHandle 

Answer 1

& is a reserved / special character in XML. It should be &

I will even add that XML parsing: line 1, character 23, illegal name character understandable. If you think that SQL is calculated from 1, then the 23rd character is & .

Answer 2

You need to change & to &

Read this

Answer 3

put your values ​​under the cdata cover as follows

 DECLARE @CaptionsDescriptions XML = '<cs><ca="2" b="<![CDATA[CITY & STATE TX]>" c="CITY & STATE TX"/></cs>'