April 14, 2009 at 3:58 pm
Hi Everyone,
My goal here is to create some 'simple' way to save table records to an Audit table either through the update stored procedure or trigger, that part I'm still working on. But below is a small example of how I'm trying to work this out:
-- Create Audit Table
Create Table logSystemAudit (
[AuditID]numeric(18,0) Not Null Identity Primary Key,
[Record]xml,
[Table]nvarchar(50)Not Null,
[CreatedBy]NVARCHAR(50)Not Null Default(SUSER_SNAME()),
[CreatedDate]DATETIMENot Null DEFAULT(getdate()))
-- Write Log from CustomerData table (dummy data of course)
DECLARE @raw xml
SET @raw = (
select (
SELECT TOP 1
'CustomerData' AS '@Table',
'E' AS '@ChangeType',
*
FROM CustomerData
FOR XML PATH('DataRow'), Type
)
FOR XML PATH('Root')
)
insert into logSystemAudit ([Record], [Table])
Select @raw, 'CustomerData'
This updates logSystemAudit with an XML column that's attached as xml_output.txt (could not get it to present in the forum).
So now any ideas on how to pull this data out of the database and into a temp table to send back out to the application? Or is it possible to even pull multiple records into one table? I guess I'm not sure what my options are and I'm not the worlds best at Xquery.
Thanks for any suggestions on this process thus far and how to make it more efficient. I'd like to use this for all our tables which is why XML seems ideal with its flexibility... the Audit table structure is VERY simple, it's just getting the darned data out of it that's being a bear.
Thanks again and take care --
Sam Alex
April 14, 2009 at 9:32 pm
Really there are two ways that I preferred to get the data out, and it really depends upon what I am doing and how often I am doing it.
The first, an my favorite is the VALUE method.
The second is sp_xml_preparedocument. I use this for very large portions of data in a xml field. This can be a bit of a memory hog, so I only do it with large xml files and when it will be rare, as I think it is easier to setup than the VALUE method.
A nice example of using the value method can be found here
http://www.sqlservercentral.com/Forums/Topic512799-145-1.aspx
The sp_xml_preparedocument is pretty well documented in BOL and is pretty easy to use.
Hope that helps.
Fraggle
April 15, 2009 at 7:58 am
Thanks for the reply, and that did point me into the right direction. Below is what I'm testing with to read the data created in my original post:
-- Declare Variables
Declare @inXML XML
DECLARE @hdoc int
-- Get XML Record from Audit Table
select@inXML = Record
fromlogSystemAudit
whereAuditID = 2
-- Prepare XML data
EXEC sp_xml_preparedocument @hdoc OUTPUT, @inXML
-- Select XML Data
SELECT*
Into#outLogTable
FROM OPENXML( @hdoc, '//DataRow')
WITH (IDint'ID',
Namevarchar(50) 'Name',
Cityvarchar(50) 'City',
Statevarchar(50)'State',
Zipvarchar(50)'Zip',
Phonevarchar(50)'Phone',
CreatedByvarchar(50)'CreatedBy',
CreatedDateDatetime'CreatedDate'
)
-- Output
select * from #outLogTable
-- Cleanup
EXEC sp_xml_removedocument @hdoc
Drop table #outLogTable
The temp table #outLogTable contains the data I'm looking for. This works great, but I still have a few issues to iron out before putting it into Production, namely how to dynamically create the 'with' portion of the select statement so this will be more dynamic. Each XML will be created from a table and the OPENXML specs show it can use a Tablename for the specs, but using FROM OPENXML( @hdoc, '//DataRow', 0) with CustomerData returns only nulls, though it does have the correct number of rows returned. Any thoughts on this?
Thanks and take care,
Sam Alex
April 15, 2009 at 8:11 am
The reason for the nulls is due to the fact that you don't have your XML paths setup correct when using the Openrowset. It is similar to above in that you have to explicitly state what you are trying to pull.....at least in my experience.
As for dynamic, if you can figure that out, please post it, as I have been trying to do the same myself for a while. The one issue I have with XML.
Fraggle.
April 15, 2009 at 9:09 am
Hi Fraggle (love the nick!),
I was able to get the Select using OPENXML to work with a table name as opposed to a hard coded definition. This works in my case since I'm dropping the entire table record into the database.
Below is the entire process that works for me, so hopefully it works for others.
----------------------------------------------------------------------------------------
-- Delete tables if they exist
----------------------------------------------------------------------------------------
IF EXISTS(SELECT name FROM sys.tables WHERE name = 'logSystemAudit') Drop table logSystemAudit
IF EXISTS(SELECT name FROM sys.tables WHERE name = 'CustomerData') Drop table CustomerData
----------------------------------------------------------------------------------------
-- Create CustomerData Table
----------------------------------------------------------------------------------------
Create Table CustomerData (
[ID]numeric(18,0)Not Null Identity Primary Key,
[Name]nvarchar(50)Not Null,
[City]nvarchar(50)Not Null,
[State]nvarchar(50)Not Null,
[Zip]nvarchar(50)Not Null,
[Phone]nvarchar(50)Not Null,
[CreatedBy]NVARCHAR(50)Not Null Default(SUSER_SNAME()),
[CreatedDate]DATETIMENot Null DEFAULT(getdate()))
----------------------------------------------------------------------------------------
-- Create Audit Table
----------------------------------------------------------------------------------------
Create Table logSystemAudit (
[AuditID]numeric(18,0)Not Null Identity Primary Key,
[Record]xml,
[Table]nvarchar(50)Not Null,
[Action]nvarchar(10)Not Null,
[CreatedBy]NVARCHAR(50)Not Null Default(SUSER_SNAME()),
[CreatedDate]DATETIMENot Null DEFAULT(getdate()))
----------------------------------------------------------------------------------------
-- Populate CustomerData with dummy data generated through http://www.generatedata.com
----------------------------------------------------------------------------------------
INSERT INTO CustomerData (Name,City,State,Zip,Phone) VALUES ('Coby Woods','Atlantic City','Northwest Territories','L6E 8C7','1-463-585-4629');
INSERT INTO CustomerData (Name,City,State,Zip,Phone) VALUES ('Robert Hurley','Fayetteville','Alberta','24976','1-222-403-0403');
INSERT INTO CustomerData (Name,City,State,Zip,Phone) VALUES ('Tucker Thomas','Taylorsville','Nunavut','61621','1-783-281-8030');
INSERT INTO CustomerData (Name,City,State,Zip,Phone) VALUES ('Graham Jarvis','Laguna Woods','Tennessee','S2Z 8N3','1-515-236-8209');
INSERT INTO CustomerData (Name,City,State,Zip,Phone) VALUES ('Arthur Cleveland','Rolling Hills Estates','NU','Z9V 8T2','1-592-446-8610');
----------------------------------------------------------------------------------------
-- Insert customer record(s) into Audit Table
-- The Select can be customized as needed
----------------------------------------------------------------------------------------
-- Get XML data from table
DECLARE @raw xml
SET @raw =
(
select
(
SELECT'CustomerData' AS '@Table', *
FROM CustomerData
FOR XML PATH('DataRow'), Type
)
FOR XML PATH('Root')
)-- Insert into Audit table
insert into logSystemAudit ([Record], [Table], [Action])
Select @raw, 'CustomerData', 'Edit'
----------------------------------------------------------------------------------------
-- Retrieve customer record(s) from Audit Table
----------------------------------------------------------------------------------------
-- Declare Variables
Declare @inXML XML
DECLARE @hdoc int
-- Get XML Record from Audit Table
select@inXML = Record
fromlogSystemAudit
whereAuditID = 1
-- Prepare XML data
EXEC sp_xml_preparedocument @hdoc OUTPUT, @inXML
-- Select XML Data
SELECT*
Into#outLogTable
FROM OPENXML( @hdoc, '/Root/DataRow',2) with CustomerData
-- Output
select * from #outLogTable
-- Cleanup
EXEC sp_xml_removedocument @hdoc
Drop table #outLogTable
The only thing that's bugging me is the ID field isn't coming across, which I could've sworn was working when I first put this SQL statement together. Any ideas on why that's so?
But all and all this works, and I'll be wrapping it into some procedures to create my table audits.
Take care,
Sam Alex
April 15, 2009 at 9:24 am
:angry: Hrm, it looks like doing it this way excludes all Identity fields, which won't work. Does anyone know the technical reason why this would be so? I found this post that has a work around, but I hate that this has to be done since everything else is working so well. We have too many Identity fields in tables that would need to come across to exclude those fields.
Anyway, if anyone else has another suggestion on how to get primary keys to load when using a table definition in an OPENXML Select, I'd love to hear your thoughts. This is quite frustrating.
Take care,
Sam Alex
Note: I just edited this post because initially I thought the PK was causing the problem but it's actually the Identity fields being excluded.
April 15, 2009 at 10:36 am
I wrote a work around that creates a Temp table based on the table schema which I hate to do, but it works. It will need to be called before the Audit table is hit, then the temp table name can be used as the table definition (after WITH) in the Select OPENXML statement.
DECLARE @TableSchema NVARCHAR(MAX)
SET @TableSchema = 'CREATE TABLE ##tmpSchema ( '
select -- Column_Name,
@TableSchema = @TableSchema + '[' + Column_NAME + '] ' + Data_Type +
ISNULL('(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) + ')','') +
ISNULL('(' + (CAST(NUMERIC_PRECISION AS VARCHAR(5))+ ',' + CAST(NUMERIC_SCALE AS VARCHAR(5))) + ')','') + ','
from Information_Schema.Columns
WHERETABLE_NAME = 'CustomerData'
ORDER BY Ordinal_Position
SET @TableSchema = @TableSchema + ')'
SET @TableSchema = REPLACE(@TableSchema,',)',')')
EXEC(@TableSchema)
I hate work arounds like this, but I don't see any other way to get this to work. Basically what I need is a schema of the table for which I'm getting the definition from but without the Identity columns being labeled as such. This allows the OpenXML Select statement to pick-up identity columns which is a must in my case.
The only blah is since I'm building ##tmpSchema dynamically I have to use a global temp table since the exec runs within its own scope and isn't accessible once the scope ends. The only snag this may cause is if two people run this at the same time, but hopefully since it's just for auditing purposes there won't be an issue.
At any rate this works, but if anyone has other suggestions on how to either get the Select OpenXML statement to show Identity columns or a simpler way to strip the Identity column from the table definition please let me know.
Take care and hopefully this thread helps someone....
Sam Alex
April 15, 2009 at 9:55 pm
I will have to take a look at this to see if there is a better way, but it is going to be a couple of days. Initially it looks pretty good though.
Fraggle.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply