Wednesday, September 7, 2011

get xml value and insert record using xml in sql server 2008

Declare @XmlOutput xml
set @XmlOutput=(SELECT '-' as Detail,12 as DesNoteType,ActionTrackerId,@AdminloginId as LoginId,GETDATE() as LastUpdated,'Deleted User – Item Unassigned' as Activity,
getdate() as ActivityDate,0 as IsDeleted,7 as ActivityType,'-' as StatusActivity,100 as StatusActivityType,0 as Edited , 0 as EditedBy,GETDATE() as EditedDate,0 as Parentid FROM tblATActionTracker where LoginId=@Loginid and Completed in(0,5,6) FOR XML AUTO,Root('ActionTrack'), ELEMENTS)
select @XmlOutput

DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @XmlOutput
INSERT INTO tblATDesNote(Detail,DesNoteType,ActionTrackerId,LoginId,LastUpdated,Activity,ActivityDate,IsDeleted,ActivityType,StatusActivity,StatusActivityType,Edited,EditedBy,EditedDate,Parentid)

SELECT Detail,DesNoteType,ActionTrackerId,LoginId,LastUpdated,Activity,ActivityDate,IsDeleted,ActivityType,StatusActivity,StatusActivityType,Edited,EditedBy,EditedDate,Parentid
FROM Openxml( @idoc, '/ActionTrack/tblATActionTracker', 3) WITH (Detail varchar(100),DesNoteType tinyint ,ActionTrackerId bigint,LoginId INT,LastUpdated datetime,Activity varchar(50),ActivityDate datetime,IsDeleted bit,ActivityType int,StatusActivity varchar(50),StatusActivityType int,Edited int,EditedBy int,EditedDate datetime ,Parentid int)

No comments:

Post a Comment