June 12, 2012 at 5:59 pm
Hello specialists. I am attempting to load an xml file that contains Chinese/Russian characters into a nvarchar(max) column within a SQL 2005 database (I know, who designed that one). I have tried BCP with -w switch, Bulk Insert with DATAFILETYPE = widechar, Insert Into MyTable select * from openrowset...Single_Blob
All of the above either strip out the special characters or put the data in a different format. Any ideas how to retain the integrity of the unicode and end up with a single row? The files contain sensitive data so I don't have a sample to attach, just collecting thoughts.
June 12, 2012 at 9:19 pm
Is it a single file composed of one XML node? Or is it a character-delimited file (i.e. one for BCP) that has a delimited column containing an XML-formatted string? Are there multiple rows each with a section of XML for your XML column?
If it is just a single file, here is some sample text I put in a Notepad++ file encoded with the ISO 8859-2 character set (Eastern European)
xml>??????,???????,????,??????,???,????,????,??????,????????,???????,??????,???????</xml>
Then I used OPENROWSET to import:
DECLARE @x XML
SET @x = (
SELECT * FROM OPENROWSET (BULK 'C:\Download\Russian.txt', SINGLE_NCLOB) AS xmlFile
)
SELECT @x
I was able to see the result in Russian.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply