Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56

    Unanswered: Notification XML formatting

    We're using Event Notifications for our MSSQL 2005 db.
    I'd like to make the XML formatting into a more traditional output.

    Before I take the effort of creating a formatting modification,
    does anybody know of a pre-existing one that I could use?

    You know, stripping the tags, and putting the data within into columns...

    Any assistance or input you could provide would be appreciated. Thanks.

  2. #2
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56
    BTW...a little more info.

    We are inserting the Event Notification into a table.
    So we're looking for a sql statement that will allow viewing and filtering the xml rows.
    Or something that we can execute to convert it, then we could filter on it.

    Yes, I'm investigating the web right now...but its always best to get input from those familiar with it.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Have a look at the XML Datatype methods in SQL Server.

  4. #4
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56
    I'll look into it...

    In the meantime, here is an output from a standard event type row inserted into the table.
    <EVENT_INSTANCE>
    <EventType>AUDIT_LOGOUT</EventType>
    <PostTime>2011-08-16T09:56:38.147</PostTime>
    <SPID>64</SPID>
    <DatabaseID>4</DatabaseID>
    <NTUserName>SYSTEM</NTUserName>
    <NTDomainName>NT AUTHORITY</NTDomainName>
    <HostName>WTSOLOMONDB</HostName>
    <ClientProcessID>1924</ClientProcessID>
    <ApplicationName>DatabaseMail - SQLAGENT90 - Id&lt;1924&gt;</ApplicationName>
    <LoginName>NT AUTHORITY\SYSTEM</LoginName>
    <Duration>1200003000</Duration>
    <StartTime>2011-08-16T09:36:38.107</StartTime>
    <EndTime>2011-08-16T09:56:38.110</EndTime>
    <Reads>2</Reads>
    <Writes>0</Writes>
    <CPU>0</CPU>
    <EventSubClass>1</EventSubClass>
    <Success>1</Success>
    <ServerName>WTSOLOMONDB</ServerName>
    <DatabaseName />
    <LoginSid>AQEAAAAAAAUSAAAA</LoginSid>
    <RequestID>0</RequestID>
    <EventSequence>124691</EventSequence>
    <IsSystem />
    <SessionLoginName>NT AUTHORITY\SYSTEM</SessionLoginName>
    </EVENT_INSTANCE>

    Of course, the field sets can change according to the event type...

  5. #5
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56
    Nevermind.
    It's not ideal or subtle, but they are happy with a simple output they can manipulate and filter on.
    At least I don't have to spend time on it any more than necessary.

    This gets them what they want...

    select * from (
    select
    replace(replace(replace(replace(CAST(EventData as varchar(max)),'><',';'),'/',''),'<',';'),'>',';') as EventData
    FROM dbo.tbl_AuditErrors) A
    where EventData like '%ERRORLOG%'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •