binary to XML Encoding problem

• dhaneenja-755935

  SSC Veteran

  Points: 219

  More actions

  October 7, 2010 at 5:24 am

  #230090

  hi all,

  I have CLR function written in following code,

  =======CLR FUNCTION=========

  public static string Str2UTF8ASCII(string sMain,string RCSeq)

  {

  ASCIIEncoding ascii = new ASCIIEncoding();

  byte[] byteArray = Encoding.UTF8.GetBytes(sMain);

  byte[] asciiArray = Encoding.Convert(Encoding.UTF8, Encoding.ASCII, byteArray);

  string finalString = ascii.GetString(asciiArray);

  return finalString.Replace("?X?X?", RCSeq);

  }

  public static byte[] StringToByte(String hex)

  {

  int NumberChars = hex.Length;

  byte[] bytes = new byte[NumberChars / 2];

  for (int i = 0; i < NumberChars; i += 2)

  bytes = Convert.ToByte(hex.Substring(i, 2), 16);

  return bytes;

  }

  [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None)]

  public static string fn_CLR_BytesAsString(string ZipInput, string Replaceto)

  {

  string strDecompressedDataStream;

  strDecompressedDataStream = "";

  try

  {

  Stream InputStream = new MemoryStream(StringToByte(ZipInput));

  using (GZipStream Decompress = new GZipStream(InputStream,

  CompressionMode.Decompress))

  {

  MemoryStream OutStream = new MemoryStream();

  byte[] buffer = new byte[4096];

  int numRead;

  while ((numRead = Decompress.Read(buffer, 0, buffer.Length)) != 0)

  {

  OutStream.Write(buffer, 0, numRead);

  }

  OutStream.Position = 0;

  StreamReader reader = new StreamReader(OutStream);

  strDecompressedDataStream = reader.ReadToEnd();

  strDecompressedDataStream = Str2UTF8ASCII(strDecompressedDataStream, Replaceto);

  }

  }

  catch

  {

  strDecompressedDataStream = "-1";

  }

  finally { }

  return strDecompressedDataStream;

  }

  ==========================

  after created ASSEMBLY and execute result contain garbage value(?) as follows,

  select CONVERT(XML,dbo.fn_CLR_BytesAsString(<my value>,'ObjectTag="')) ssql

  Results:

  <void property="description">

  <string>test b? abc, abc axv tt-wwwwww-ffffffff</string>

  </void>

  garbage value is "?"

  what is the Encoding problem in above CLR function?

  Thanks

  Tharindu Dhaneenja

  Tharindu Dhaneenja.
  MCTS,MCITP(SQL Server),OCA(Oracle)
  http://www.databaseusergroup.com