how can i use eventdata().modify() properly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how can i use eventdata().modify() properly

Author

Topic

ml90
Starting Member

2 Posts

Posted - 2009-11-08 : 11:14:55

My ScriptTable_fn returns script command as an xml element. The problem occurs on the .modify(). I’m not sure how to use it properly. I’ve looked around and could not find a solution. Any help would be appreciated a lot. Thanks

CREATE TRIGGER [tableTrg]

ON DATABASE

FOR CREATE_TABLE, ALTER_TABLE

AS

DECLARE @Body XML

DECLARE @tableName nvarchar(100)

DECLARE @tableTsqlCommand XML

DECLARE @script XML

SET @Body = eventdata() -- get all other attributes...login, datetime, etc.

SET @tableName = @Body.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') -- get name of table to be scripted

SET @script = (select dbo.ScriptTable_fn(@tableName))

SET @Body.modify('replace value of (/EVENT_INSTANCE/tsqlcommand) [1] with "(@script)"')

EXEC dbo.sendmsg_sp @Body

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 10:46:46

you need to use like

SET @Body.modify('replace value of (/EVENT_INSTANCE/tsqlcommand) [1] with sql:variable("@script")')

ml90
Starting Member

2 Posts

Posted - 2009-11-13 : 17:51:46

This did not work.

Error was
Msg 9342, Level 16, State 1, Procedure tableTrg, Line 20
XQuery [modify()]: An XML instance is only supported as the direct source of an insert using sql:column/sql:variable.

Any suggestions?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources