Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


FOR XML PATH, BINARY BASE64 Encoded and Decoding


FOR XML PATH, BINARY BASE64 Encoded and Decoding

Author
Message
ross.cecil
ross.cecil
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 91
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search