FOR XML PATH, BINARY BASE64 Encoded and Decoding

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » XML » FOR XML PATH, BINARY BASE64 Encoded and...

FOR XML PATH, BINARY BASE64 Encoded and Decoding

Rate Topic

Display Mode

Topic Options

Author

Message

ross.cecil

Posted Tuesday, September 14, 2010 2:44 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 22, 2013 7:20 AM
Points: 48, Visits: 85

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 -

SELECT VIR.Sequence,
VIR.Image_Nbr,
VIR.Row_Nbr,
VIR.Strip_Nbr,
VIR.Row_Data
FROM dbo.FVImageRowData AS VIR
WHERE VIR.Sequence='B0002NPW'
AND VIR.Image_Nbr=105
AND VIR.Row_Nbr=15

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.

SELECT VIR.Sequence,
VIR.Image_Nbr,
VIR.Row_Nbr,
VIR.Strip_Nbr,
CONVERT(VARBINARY(128),VIR.Row_Data) AS 'Row_Data'
FROM FVImageRowData AS VIR
WHERE VIR.Sequence='B0002NPW'
AND VIR.Image_Nbr=105
AND VIR.Row_Nbr=15
FOR XML PATH ('FVimageRowData'), TYPE, BINARY BASE64

The resulting XML is -

<FVimageRowData>
<Sequence>B0002NPW</Sequence>
<Image_Nbr>105</Image_Nbr>
<Row_Nbr>15</Row_Nbr>
<Strip_Nbr>1</Strip_Nbr>
<Row_Data>hHh6gHRwcHiAgIR0cH6KjoJ+hH50gIaChHyGgnx4doaEioh8foh6fnqAhJSGeniGfI6AiA==</Row_Data>
</FVimageRowData>

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.

Thanks!

Post #985881

« Prev Topic | Next Topic »

Permissions