trouble with changing encoding utf-8 on xml load

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)

trouble with changing encoding utf-8 on xml load

Author

Topic

crafteecook
Starting Member

6 Posts

Posted - 2009-02-26 : 10:58:01

What am I missing here?
I have an xml document that also has a DTD defined for it, no problem there, but I'm getting the error
XML parsing: line 1, character 38, unable to switch the encoding
Of course the xml declaration in the doc contains encoding="UTF-8".
I do understand that SS2005 accepts UTF-16, not UTF-8. I do know that I need to convert 8 to 16, but have not been able to do it successfully within this code.

I want to be able to take care of this on the load into SQL Server 2005, not at the document level. I have tried changing the @TempXML datatype to varbinary and nvarchar. I have tried changing the CONVERT xml to CONVERT varbinary and varchar; still no luck

Thanks for your expertise!

ALTER PROCEDURE [dbo].[call_to_get_ord] (@FileName varchar(255))
AS
DECLARE @ExecCmd VARCHAR(255)
DECLARE @FileContents NVARCHAR(max)
DECLARE @TempXML TABLE (ThisLine varchar(max))

SET @ExecCmd = 'type ' + @FileName
SET @FileContents = ''

INSERT INTO @TempXML EXEC master.dbo.xp_cmdshell @ExecCmd
SELECT @FileContents = @FileContents + ISNULL(ThisLine, '') FROM @TempXml
SELECT CONVERT(xml, @FileContents,2) as FileContents

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-26 : 10:59:38

See http://support.microsoft.com/kb/232580

E 12°55'05.63"
N 56°04'39.26"

crafteecook
Starting Member

6 Posts

Posted - 2009-02-26 : 11:20:04

thank you for your response. I believe it is possible to do what is required using the code I posted with minor datatype changes. Your suggestion is appreciated, but not what I'm looking for.
It is not a requirement to store it as UTF-8. Additionally, I am not a C programmer; in the support document you reference it details some C libs and code. I want to load the document without stipping off the encoding line at the OS level. Some of the search results I read talk about converting the xml to a varbinary, but I must not be doing it in the right place.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources