Reputation: 2905
Does anybody know why this XML text throws illegal name character error in SQL Server 2008?
'<cs><c a="2" b="CITY & STATE TX" c="CITY & STATE TX"/></cs>'
Exception message is
Msg 9421, Level 16, State 1, Line 2
XML parsing: line 1, character 23, illegal name character
Following is the query used for parse this xml
DECLARE @CaptionsDescriptions XML = '<cs><c a="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
Upvotes: 13
Views: 64859
Reputation: 111950
The &
is a reserved/special character in XML. It should be &
I'll even add that XML parsing: line 1, character 23, illegal name character
is quite clear if you know that SQL counts from 1: then the 23rd character is the &
.
Upvotes: 27
Reputation: 24633
put your values under cdata cover like this
DECLARE @CaptionsDescriptions XML = '<cs><c a="2" b="<![CDATA[CITY & STATE TX]>" c="CITY & STATE TX"/></cs>'
Upvotes: 1