June 26, 2007 at 9:20 am
I am prototyping a Data-Auditing table in which all audits are stored in one table. I want to store the Data that is modified in Updates and Deletes for now. What I am having trouble with is forming the Xml for all the Rows updated in a Trigger. Please don't tell me about bad design or anything, I understand, it's just a prototype.
With that said, here's the basic Xml structure. I've made it more compact so that less data is stored:
<Data>
<Column Name="[Name of Column]">
<Value>1</Value>
<!-- <OriginalValue>2</OriginalValue> Only used for Updates -->
</Column>
<Column Name="[Name of Column]">
<Value>1</Value>
</Column>
...
<Data>
I can get the Schema for this structure by using this Query. However, I cannot figure out how I would use it as a "Schema" and then just insert Xml into it.
Declare @data Xml
Select @data = Convert(Xml, '' + (Select name As '@Name' From SysColumns SC Where ID = Object_ID('MyTable') FOR XML PATH('Column')) + '')
Select @data
Any ideas?
October 17, 2007 at 4:07 am
Using your favourite XML/XML Schema editor you should design an appropriate XML Schema and then import it into your database as an XML SCHEMA COLLECTION.
You can then reference the schema in the column/variable declarations.
To build each XML instance when auditing data modifications your best option is to use XQuery - there are a few examples in Books Online. For a more acurate answer, please, post DDL, sample data and expected results.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply