'Conversion from xml to int is not supported on the connected database server'

• forsqlserver

  SSCoach

  Points: 18902

  More actions

  May 27, 2011 at 12:33 am

  #389599

  I have a blank table with one xml column.

  I am changing the datatype from xml to int then it is giving error:

  'Conversion from xml to int is not supported on the connected database server'

  Any Idea?

  Thanks

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  May 27, 2011 at 12:40 pm

  #1331187

  XML is a binary data type so it has to de-serialized into a string before it can be cast to a numeric type.

  Works because SQL Server XML data types support fragments:

  SELECT CAST('1' AS XML)

  Does not work *

  SELECT CAST(CAST('1' AS XML) AS INT)

  Works:

  SELECT CAST(CAST(CAST('1' AS XML) AS VARCHAR(100)) AS INT)

  * See "Implicit Conversions" section for map of supported implicit and explicit conversions http://msdn.microsoft.com/en-us/library/ms187928.aspx

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• forsqlserver

  SSCoach

  Points: 18902

  More actions

  May 30, 2011 at 10:41 pm

  #1331778

  Thanks,

  I was trying it from GUI!!

  Using command it works!!

  Thanks