FOR XML PATH, BINARY BASE64 Encoded and Decoding

• ross.cecil

  Mr or Mrs. 500

  Points: 543

  More actions

  September 14, 2010 at 2:44 pm

  #95073

  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 -

  [font="Courier New"]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) [/font]

  Simply selecting data from the table yields the expected results -

  [font="Courier New"]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[/font]

  [font="Courier New"]

  Sequence Image_Nbr Row_Nbr Strip_Nbr Row_Data

  -------- ----------- ----------- ----------- ----------------------------------------------------

  B0002NPW 105 15 1 „xz€tppx€€„tp~ŠŽ‚~„~t€†‚„|†‚|xv†„Šˆ|~ˆz~z€„”†zx†|Ž€ˆ[/size[/font]

  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.

  [font="Courier New"]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[/font]

  The resulting XML is -

  [font="Courier New"]<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>[/font]

  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!