----------------------------------------------------------- New Message on BDOTNET
----------------------------------------------------------- From: RamSdotnet Message 3 in Discussion Hi Mahesh, thnx for your reply. I am doing it in a differentpmannner. I had declared a xmlstr as varchar(max) while appendinding xml to xmlstr, i am converting xml to varchar and then adding to it. Code Snippet: Declare @xmlstr as varchar(max) set @xmlstr = convert(varchar(max),'<log>data</log>') Cheers Ram >From: "Vemula, Mahesh" <[EMAIL PROTECTED]> >To: "BDOTNET" <[email protected]> >CC: <[EMAIL PROTECTED]> >Subject: RE: xm datatype problem >Date: Thu, 26 Apr 2007 10:07:03 +0530 > >Hi > > > >As u can not do string manipulations on xml data type declare a variable >of varchar > >Once u complete the manipulations assign that varchar variable to xml >data type and then insert in to table > > > > > >declare @xml xml > >declare @xmlstr varchar(1000) > > > >SET @xmlstr = '<Log1><ChangedColumn>ChangedData</ChangedColumn></Log1>' > >SET @xmlstr = @xmlstr + >'<Log1><ChangedColumn>ChangedData</ChangedColumn></Log1>' > >SET @xml = @xmlstr > >INSERT INTO EmpLog(TableName,data,modifieddatetime) > > VALUES('emp',@xml, getdate()) > > > >I hope it will work, let me know if it's not working > > > > > >Thanks > >Mahesh Vemula > > > > > >________________________________ > >From: RamSdotnet [mailto:[EMAIL PROTECTED] >Sent: Thursday, April 26, 2007 9:28 AM >To: BDOTNET >Subject: xm datatype problem > > > > ><http://c.msn.com/c.gif?NC=9523&NA=34131&PS=&PI=26283&DI=416&TP=http://g >roups.msn.com> > > <http://sc.groups.msn.com/themes/R9c/pby/img/mail/mlstar.gif> > >New Message on BDOTNET <http://groups.msn.com/bdotnet> > > <http://sc.groups.msn.com/img/R9c/c.gif> > > > > xm datatype problem ><http://groups.msn.com/bdotnet/_notifications.msnw?type=msg&action=showd >iscussion&parent=1&item=35385> > >Reply ><mailto:[EMAIL PROTECTED]> > <http://sc.groups.msn.com/img/R9c/c.gif> > > > >Reply to Sender ><mailto:[EMAIL PROTECTED]> >Recommend ><http://groups.msn.com/bdotnet/_notifications.msnw?type=msg&action=recom >mend&parent=1&item=35385> > >Message 1 in Discussion > >From: RamSdotnet ><http://groups.msn.com/bdotnet/profile?user=RamSdotnet%E2%9C%93> > > > >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 > > > View other groups in this category. ><http://groups.msn.com/Browse?CatId=26> > > > > >Passport Member Services. >For other questions or feedback, go to our Contact Us ><http://groups.msn.com/contact> page. > >If you do not want to receive future e-mail from this MSN group, or if >you received this message by mistake, please click the "Remove" link >below. On the pre-addressed e-mail message that opens, simply click >"Send". Your e-mail address will be deleted from this group's mailing >list. >Remove my e-mail address from BDOTNET. ><mailto:[EMAIL PROTECTED]> > > > _________________________________________________________________ Spice up your IM conversations. New, colorful and animated emoticons. Get chatting! http://server1.msn.co.in/SP05/emoticons/ ----------------------------------------------------------- To stop getting this e-mail, or change how often it arrives, go to your E-mail Settings. http://groups.msn.com/bdotnet/_emailsettings.msnw Need help? If you've forgotten your password, please go to Passport Member Services. http://groups.msn.com/_passportredir.msnw?ppmprop=help For other questions or feedback, go to our Contact Us page. http://groups.msn.com/contact If you do not want to receive future e-mail from this MSN group, or if you received this message by mistake, please click the "Remove" link below. On the pre-addressed e-mail message that opens, simply click "Send". Your e-mail address will be deleted from this group's mailing list. mailto:[EMAIL PROTECTED]
