xml datatype prob | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

xml datatype prob

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

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?
Referhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=21031 and please do not duplicate the posts. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>