CODEWITHSUNDEEP
xmlsql-server-2008
Sunil
Sunil

Reputation: 2905

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

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

Answers (3)

xanatos
xanatos

Reputation: 111950

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

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

eshirvana
eshirvana

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

Ehsan
Ehsan

Reputation: 32729

You need to change & to &amp;

Do read this

Upvotes: 5

Related Questions