SQL Server Performance

xml datatype prob

Discussion in 'SQL Server 2005 General Developer Questions' started by ramdotnet, Apr 25, 2007.

  1. ramdotnet New Member

    Hi
    I am working on SQL Server 2005.
    I am having two tables as follows

    Tables Structure

    Emp table :

    ID int
    Name varchar(50)
    Dept varchar(50)
    details XML

    EmpLog

    tableName VARRCHAR(50)
    data xml
    modifiedby varchar(50)
    modifiedtime datetime

    I am creating a trigger EmpTrigger on Emptable.
    Purpose of the trigger is to log the changes on the table.
    -When the user inserts a new row i want to capture whole row and place the row
    in the log table
    -When a user modifies a field , want to capture the old value and place that value
    in the log table
    -when a user deletes row i want to capture whole row and place the row
    in the log table


    In the trigger i am taking all the rows from the inserted table into a cursor and looping through to perform set based operation
    In the update operation -->if any column value is changed, i am capturing those values and building a xml string.


    XML structure
    <Log>
    <ChangedColumn>ChangedData<ChangedColumn>
    </Log>
    I am inserting this xml string into log table
    my insert stmt looks like
    Insert into EmpLog values(emp,xmlstring,host_name,getdate());

    The problem is with constructing xml string in the trigger
    1)If i declare xmlstring as xml
    I cant perform add operation on it
    set xmlstring = xmlstring + <ChangedColumn>ChangedData<ChangedColumn>
    Error : '+' can not be performed on xml datatype

    2) If i declare xmlstring as text/nvarchar
    i cant add xml datatpe to xmlstring
    bcoz i am having details column as xml datatype in the emp table
    Error : '+' can not be performed on xml datatype


    I dont want to change my both tables columns datatype.
    I wantto return xmlstring from a trigger
    i wantto insert that string into the log table data column

    Please help me

    Thanks in advance
    Ram
  2. Adriaan New Member

    quote:In the trigger i am taking all the rows from the inserted table into a cursor and looping through to perform set based operation
    To be honest, that is the exact opposite of a set-based operation.

    Can't you just insert the value from the XML-typed column of inserted/deleted into the XML-typed column of the log table?

Share This Page