XML field collation issue during insert

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)

XML field collation issue during insert

Author

Topic

Mathias
Posting Yak Master

119 Posts

Posted - 2007-06-21 : 05:35:22

I have a problem when I insert the following query into the table

CREATE TABLE [dbo].[SAFECOM_1](
[XML_TEXT] [xml] NOT NULL,
[REC_NUM] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

INSERT INTO Safecom_1 (XML_TEXT)
select '<ENTRY>
<JobSubmitLogon>12036967</JobSubmitLogon>
<JobName>&</JobName>
<JobDate>2007-06-13 11:54:17</JobDate>
<UserID>232</UserID>
</ENTRY>'

What should I add to the xml insert text to get it working?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-21 : 05:50:49

Try this


DECLARE @Test TABLE([XML_TEXT] [xml] NOT NULL,[REC_NUM] [int] IDENTITY(1,1) NOT NULL) 

INSERT INTO @Test(XML_TEXT)
SELECT '<ENTRY>
<JobSubmitLogon>12036967</JobSubmitLogon>
<JobName>&</JobName>
<JobDate>2007-06-13 11:54:17</JobDate>
<UserID>232</UserID>
</ENTRY>'

REPLACE THE AMPERSAND WITH THIS
"& a m p ;"
without the spaces

Mathias
Posting Yak Master

119 Posts

Posted - 2007-06-21 : 07:11:21

in fact I am using VB to import a XML file encode as
<?xml version="1.0" encoding="ISO-8859-1" ?>. Is there no way to add this collation information in the SQL insert statement?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources