Insert into a xml column

  • 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]

  • 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]

  • 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]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply