I have an interesting challenge that has, at least temporarily, stopped me. I've done the usual Internet and SQL Server Central searches without seeing a solution so I'm open for suggestions. At this point, I'll try just about anything!
I'm attempting to move varchar data in the us-ascii character set between SQL Server databases using XML documents. (The nature of the task is such that other tools such as SSIS cannot be used.) The decoded data from the BASE64 encoding used in the FOR XML clause does not resemble the input data.
The source is a table that is designed to contain variable length character data where each character represents a 0 to 255 value out of an insturmentation system -
CREATE TABLE FVImageRowData(
Sequence char(8) NOT NULL DEFAULT (''),
Image_Nbr int NOT NULL DEFAULT (0),
Row_Nbr int NOT NULL DEFAULT (0),
Strip_Nbr int NOT NULL DEFAULT (0),
Row_Data varchar](128) NOT NULL)
Simply selecting data from the table yields the expected results -
FROM dbo.FVImageRowData AS VIR
Sequence Image_Nbr Row_Nbr Strip_Nbr Row_Data
-------- ----------- ----------- ----------- ----------------------------------------------------
B0002NPW 105 15 1 „xz€tppx€€„tp~ŠŽ‚~„~t€†‚„|†‚|xv†„Šˆ|~ˆz~z€„”†zx†|Ž€ˆ[/size
The XML document is created by a SELECT statement using the FOR XML PATH clause; a simplified but functional version of the query follows. This is essentially the previous query with the addition that the column Row_Data is now CONVERTed to VARBINARY and the BINARY BASE64 directive has been added to the FOR XML clause.
CONVERT(VARBINARY(128),VIR.Row_Data) AS 'Row_Data'
FROM FVImageRowData AS VIR
FOR XML PATH ('FVimageRowData'), TYPE, BINARY BASE64
The resulting XML is -
The problem is that when the encoded text is decoded using the standard "us-ascii" character set the result does not resemble the input.
I would appreciate any questions, comments or suggestions on this one as it has me stopped.