insert results of FOR XML query into table using triggers

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

SQL Server 2000 Forums

SQL Server Development (2000)

insert results of FOR XML query into table using triggers

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-28 : 11:23:26

ifdefnt1 writes "Hi guru,

I have 2 tables,
TBLDATA


int         id (autoincrement)
varchar  100 datacol1
varchar  400 datacol2
varchar  200 datacol3
varchar  100 datacol4
datetime    dt_updated

and TBLXML


int          id autoincrement
varchar  100 xmlid
text         rowxmldata 
datetime     dt_updated

I am trying to get, using triggers, the data from the inserted, updated or deleted TBLDATA row as XML and insert or update this xml result into TBLXML.rowxmldata and the value of TBLDATA.datacol1 for that row into TBLXML.xmlid. The inserting of the datacol1 to xmlid is done, I just dont know how to capture the xml as the FOR XML query cannot be used in nested queries. Any ideas greatfully received. Thanks."

monkeybite
Posting Yak Master

152 Posts

Posted - 2002-08-28 : 11:51:07

The results from a FOR XML query cannot be reused within a SPROC or trigger, per SQL Books Online and personal experience. This forum thread will give more details:
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=15093[/url]

-- monkey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources