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
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 &
.
You need to change & to &
Do read this
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With