Insert into a xml column

• Laerte Poltronieri Junior-367636

  Hall of Fame

  Points: 3311

  More actions

  September 2, 2009 at 7:09 am

  #97986

  Hia all, i have this query

  create table LockChain (Bloqueador int,

  Bloqueado int,

  Handle varbinary(max),

  Comando xml

  )

  insert into LockChain (Bloqueador ,

  Bloqueado ,

  Handle ,

  Comando

  )

  SELECT A.Blocking_Session_ID,

  A.Session_ID,

  A.sql_handle,

  cast(x.text as xml)

  --INTOLockChain

  FROMsys.dm_exec_requests A

  CROSS APPLY sys.dm_exec_sql_text (a.sql_handle) AS x

  WHEREBlocking_Session_ID <> 0

  and i want to put the "cast(x.text as xml)" into a column "comando" in

  LockChain table.

  This way does not work...How can i do that ?

  The error was

  XML parsing: line 39, character 24, illegal qualified name character

  $hell your Experience !!![/url]

• Mauve

  SSChampion

  Points: 11316

  More actions

  September 2, 2009 at 8:02 am

  #1048158

  In order to cast a text string to the XML datatype, the text string must be valid XML. The text column that is returned from sys.dm_exec_sql_text is not XML.

  See sys.dm_exec_sql_text in the Books Online (BOL).

  ---

  [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

• Laerte Poltronieri Junior-367636

  Hall of Fame

  Points: 3311

  More actions

  September 2, 2009 at 8:05 am

  #1048160

  Hi , thanks for your reply..

  i change to

  cast(replace(x.text,'''','') as xml)

  and works fine now !!!

  Thanks a lot

  $hell your Experience !!![/url]