March 1, 2011 at 3:01 pm
I have created an audit log in my database using a DDL trigger. The data from EVENTDATA() is stored in XML format, one record per event. The format of the record is below. I cannot figure out how to retrieve the data and get into reporting services in order to create a report. Using XML is totally foreign to me. I was hoping to use SSIS to do the report generation. Any examples out there?
<EVENT_INSTANCE>
<EventType>event </EventType>
<PostTime>date-time</PostTime>
<SPID>spid</SPID>
<ServerName>name </ServerName>
<LoginName>login </LoginName>
<UserName>name</UserName>
<DatabaseName>name</DatabaseName>
<SchemaName>name</SchemaName>
<ObjectName>name</ObjectName>
<ObjectType>type</ObjectType>
<TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>
March 1, 2011 at 3:08 pm
Here's a sample.
CREATE TABLE #T (
ID INT IDENTITY PRIMARY KEY,
LogEntry XML);
INSERT INTO #T (LogEntry)
VALUES ('<EVENT_INSTANCE>
<EventType>event </EventType>
<PostTime>date-time</PostTime>
<SPID>spid</SPID>
<ServerName>name </ServerName>
<LoginName>login </LoginName>
<UserName>name</UserName>
<DatabaseName>name</DatabaseName>
<SchemaName>name</SchemaName>
<ObjectName>name</ObjectName>
<ObjectType>type</ObjectType>
<TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>');
SELECT LogEntry.value('(EVENT_INSTANCE/EventType/text())[1]','varchar(100)')
FROM #T;
What you're looking for is "XQuery". Search for that in Books Online or on MSDN.com. For this, you mainly just need the "value" function, but you'll eventually want to know how to use "nodes" and "query" as well.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply