VARBINARY(MAX) to XML

• Jai-SQL DBA

  Ten Centuries

  Points: 1330

  More actions

  December 10, 2014 at 2:39 am

  #299232

  I have a column in VARBINARY(MAX) and I want its data to convert it to XML

  Can someone please provide me a simple query.I tried looking on other websites but no luck .they are quite confusing..

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  December 10, 2014 at 2:45 am

  #1763710

  Assuming that the contents are already valid XML, then

  ALTER TABLE <table name> ALTER COLUMN <column name> XML

  should do it.

  If the contents aren't valid XML, you'll have to fix that first.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Jai-SQL DBA

  Ten Centuries

  Points: 1330

  More actions

  December 10, 2014 at 2:49 am

  #1763711

  Gila,

  I am looking for a query to convert data in VARBINARY(MAX) to be displayed in XML

  can you help

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  December 10, 2014 at 3:58 am

  #1763724

  It doesn't appear there is much to do to convert from varbinary(max) to XML;

  DECLARE @test TABLE (

  varbincol varbinary(max)

  )

  INSERT INTO @test VALUES ( CAST(N'<XML><!-- A comment --></XML>' AS varbinary(MAX)))

  SELECT CAST(varbincol AS xml)

  FROM @test

  Doesn't it solve your issue?

  -- Gianluca Sartori