Problem to save unicode text in xml dataType

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem to save unicode text in xml dataType

Author

Topic

hdv212
Posting Yak Master

140 Posts

Posted - 2010-09-13 : 15:25:20

Hi
i have a table which have a column with xml dataType. i want to save my infopath data in database.
my problem is that i can't save xml data when contains unicode chars.
for example, this code fails :

insert FormData select newid(),'4243df98-2c4d-49a3-b838-01d2c13ce90e',N'???1',
'<?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution solutionVersion="1.0.0.21" productVersion="12.0.0" PIVersion="1.0.0.0" href="file:///C:\Users\Hamed\Documents\customForm1.xsn" name="urn:schemas-microsoft-com:office:infopath:customForm1:-myXSD-2010-09-11T10-56-47" ?><?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?><my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-09-11T10:56:47" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-us">
<my:txtFirstName>UnicodeText</my:txtFirstName>
<my:txtLastName>UnicodeText</my:txtLastName>
<my:txtFatherName>UnicodeText</my:txtFatherName>
<my:txtNationalCode>UnicodeText</my:txtNationalCode>
<my:cmbReuqesType>2</my:cmbReuqesType>
<my:dtmRequestDate>2010-09-11</my:dtmRequestDate>
<my:txtDescription>UnicodeText</my:txtDescription>
</my:myFields>',
getdate()

here is error message :

Msg 9420, Level 16, State 1, Line 1
XML parsing: line 2, character 19, illegal xml character

but this code works correctly :

insert FormData select newid(),'4243df98-2c4d-49a3-b838-01d2c13ce90e',N'???1',
'<?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution solutionVersion="1.0.0.21" productVersion="12.0.0" PIVersion="1.0.0.0" href="file:///C:\Users\Hamed\Documents\customForm1.xsn" name="urn:schemas-microsoft-com:office:infopath:customForm1:-myXSD-2010-09-11T10-56-47" ?><?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?><my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-09-11T10:56:47" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-us">
<my:txtFirstName>Test1</my:txtFirstName>
<my:txtLastName>Test2</my:txtLastName>
<my:txtFatherName>Test3</my:txtFatherName>
<my:txtNationalCode>Test4</my:txtNationalCode>
<my:cmbReuqesType>2</my:cmbReuqesType>
<my:dtmRequestDate>2010-09-11</my:dtmRequestDate>
<my:txtDescription>Test5</my:txtDescription>
</my:myFields>',
getdate()

where is my problem and how to solve it ?
thanks in advance

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-13 : 21:31:23

quote:

insert FormData select newid(),'4243df98-2c4d-49a3-b838-01d2c13ce90e',N'???1',
'<?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution solutionVersion="1.0.0.21" productVersion="12.0.0" PIVersion="1.0.0.0" href="file:///C:\Users\Hamed\Documents\customForm1.xsn" name="urn:schemas-microsoft-com:office:infopath:customForm1:-myXSD-2010-09-11T10-56-47" ?><?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?><my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-09-11T10:56:47" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-us">
<my:txtFirstName>UnicodeText</my:txtFirstName>
<my:txtLastName>UnicodeText</my:txtLastName>
<my:txtFatherName>UnicodeText</my:txtFatherName>
<my:txtNationalCode>UnicodeText</my:txtNationalCode>
<my:cmbReuqesType>2</my:cmbReuqesType>
<my:dtmRequestDate>2010-09-11</my:dtmRequestDate>
<my:txtDescription>UnicodeText</my:txtDescription>
</my:myFields>',
getdate()

Replace the red part by this one.
N'<?xml version="1.0" encoding="UTF-16"?>
or
N'<?xml version="1.0" encoding="UTF-8"?>

You will be fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources