XML parsing error: 'A string literal was expected' when inserting values into table

Question

In Microsoft SQL Server 2008 I am attempting to insert values into an sql table with the columns type_id of datatype int and xml_info of datatype XML. I am using the following query:

INSERT INTO tbl_applied_devices ([type_id],[xml_info])
VALUES (1,'<Profile ID=99><Server><ID>BC4A18CA-AFB5-4268-BDA9-C990DAFE7783</ID>  <Name>localhost</Name><Services></Services></Server></Profile>')

But I keep getting this error:

Msg 9413, Level 16, State 1, Line 4
XML parsing: line 1, character 13, A string literal was expected

What am I doing incorrectly?

EDIT: I found that the source of the error is the xml element's ID attribute, <Profile ID=99> seems to be what is causing the error. How can I correctly insert xml with an attribute ? Do I need to somehow escape one of the characters?

RichardTheKiwi · Accepted Answer · 2012-11-04 23:45:09Z

11

XML documents are textual by nature. This is not HTML for a web page, so you need ALL attribute values in quotes (single or double).

INSERT INTO tbl_applied_devices ([type_id],[xml_info])
VALUES (1,'<Profile ID="99"><Server><ID>BC4A18CA-AFB5-4268-BDA9-C990DAFE7783</ID>  <Name>localhost</Name><Services></Services></Server></Profile>')

It's very hard to parse the XML 1.0 specifications but here's a Microsoft version for .Net 4.5, which is just as relevant for SQL Server XML.

edited Nov 4, 2012 at 23:45

answered Nov 4, 2012 at 23:21

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RBarryYoung Over a year ago

I too have noticed that it seems much easier to parse XML than it is to parse the XML specification. ;-)

Duccio Fabbri · Accepted Answer · 2012-11-04 23:30:02Z

3

Attributes, ID is an attribute, must be quoted. Try ID="99"

edited Nov 4, 2012 at 23:30

answered Nov 4, 2012 at 23:20

Duccio Fabbri

1,0181 gold badge11 silver badges21 bronze badges

Collectives™ on Stack Overflow

XML parsing error: 'A string literal was expected' when inserting values into table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related