XML In SQL Server 2005 - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

XML In SQL Server 2005

Author

Topic

shan_savi
Starting Member

6 Posts

Posted - 2008-10-15 : 05:35:24

Hello

I have a requirement. I need to write triggers against tables which convert inserted or updated records into xml and insert into another table (working table). Let me ex with a scenario

Table: Policy

PolicyID int,

Descr varchar(50),

….

…

Table:WorkingTable

XMLValue xml (Datatype)

If someone inserts a record or updates a record it should take those values (inserted or updated) from the magic table and insert into working table as a new row. How do we achieve this in triggers?

There are many ways:

Easiest and worst way of doing is declaring variables and retrieves values from the SELECT statement and concentrates and insert into the xml field like

For ex:

SELECT @PolicyID = i.PolicyID from Inserted I

SELECT @XML = ‘<Policy><PolicyID>’ + @ PolicyID ‘</PolicyID>’ + <PolicyDescription> + @ PolicyID + ’ </PolicyDescription></Policy>’

Is there any method which converts into xml?

What is EventData() in SQL Server 2005?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 05:46:49

i think what you need is FOR XML clause. look into books online for syntax and usage.

shan_savi
Starting Member

6 Posts

Posted - 2008-10-15 : 06:20:16

quote:
Originally posted by visakh16

i think what you need is FOR XML clause. look into books online for syntax and usage.

i already checked that yesterday visaksh

for ex :

if i have a SQL Statement

Declare

@xml xml ;

SELECT @xml = i.Policy_ID,i.Descr from Inserted i FOR XML

its failing

shan_savi
Starting Member

6 Posts

Posted - 2008-10-15 : 06:27:20

quote:
Originally posted by visakh16

i think what you need is FOR XML clause. look into books online for syntax and usage.

i think it should be xml raw

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-15 : 06:38:50

I got the following code from:
http://codeinet.blogspot.com/2006/09/audit-table-data-changes-in-sql-server.html

INSERT @deletedrows (rowguid)
SELECT rowguid FROM DELETED
SET @rowcount = @@ROWCOUNT
SET @i = 1
WHILE @i <= @rowcount
BEGIN
SELECT @rowguid = rowguid
FROM @deletedrows
WHERE
id = @i
SET @data = (SELECT * FROM DELETED WHERE rowguid = @rowguid FOR XML AUTO, ELEMENTS)
INSERT INTO dbo.Audits (rowguid, table_name, data)
VALUES (@rowguid, @table_name, @data)
SET @i = @i+1
END
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources