Extracting SetOptions in the TSQLCommand section of EVENTDATA

How do I parse out the SetOptions in the EventData that is generated
for DDL events? I would like to extract the various options and the
values. Seems I would need to do this dynamically since I don't which
set options would be present each time. Since these are not elements
like the other entries, I'm having trouble getting at them.
<EVENT_INSTANCE>
<EventType>ALTER_PROCEDURE</EventType>
<PostTime>2006-02-22T07:42:57.830</PostTime>
<SPID>55</SPID>
<ServerName>DBServer1\DEVELOPMENT</ServerName>
<LoginName>AAA/BBB</LoginName>
<UserName>dbo</UserName>
<DatabaseName>EventData</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>Process_Audit_Messages_pr</ObjectName>
<ObjectType>PROCEDURE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>/*
Create procedure xxx as ...
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
What about:
declare @.x xml
set @.x = N'<EVENT_INSTANCE>
<EventType>ALTER_PROCEDURE</EventType>
<PostTime>2006-02-22T07:42:57.830</PostTime>
<SPID>55</SPID>
<ServerName>DBServer1\DEVELOPMENT</ServerName>
<LoginName>AAA/BBB</LoginName>
<UserName>dbo</UserName>
<DatabaseName>EventData</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>Process_Audit_Messages_pr</ObjectName>
<ObjectType>PROCEDURE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>/*
Create procedure xxx as ...
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>';
select n.value('local-name(.)', 'nvarchar(100)') as opt_name
, n.value('.', 'nvarchar(100)') as opt_value
from @.x.nodes('/EVENT_INSTANCE/TSQLCommand/SetOptions/@.*') as T(n)
Best regards
Michael
"SteveM" <stephencmyers@.hotmail.com> wrote in message
news:1140617241.326529.71400@.o13g2000cwo.googlegro ups.com...
> How do I parse out the SetOptions in the EventData that is generated
> for DDL events? I would like to extract the various options and the
> values. Seems I would need to do this dynamically since I don't which
> set options would be present each time. Since these are not elements
> like the other entries, I'm having trouble getting at them.
>
> <EVENT_INSTANCE>
> <EventType>ALTER_PROCEDURE</EventType>
> <PostTime>2006-02-22T07:42:57.830</PostTime>
> <SPID>55</SPID>
> <ServerName>DBServer1\DEVELOPMENT</ServerName>
> <LoginName>AAA/BBB</LoginName>
> <UserName>dbo</UserName>
> <DatabaseName>EventData</DatabaseName>
> <SchemaName>dbo</SchemaName>
> <ObjectName>Process_Audit_Messages_pr</ObjectName>
> <ObjectType>PROCEDURE</ObjectType>
> <TSQLCommand>
> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
> ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
> <CommandText>/*
> Create procedure xxx as ...
> </CommandText>
> </TSQLCommand>
> </EVENT_INSTANCE>
>
|||Thanks! Exactly what I was looking for.

Extracting SetOptions in the TSQLCommand section of EVENTDATA

How do I parse out the SetOptions in the EventData that is generated
for DDL events? I would like to extract the various options and the
values. Seems I would need to do this dynamically since I don't which
set options would be present each time. Since these are not elements
like the other entries, I'm having trouble getting at them.
<EVENT_INSTANCE>
<EventType>ALTER_PROCEDURE</EventType>
<PostTime>2006-02-22T07:42:57.830</PostTime>
<SPID>55</SPID>
<ServerName>DBServer1\DEVELOPMENT</ServerName>
<LoginName>AAA/BBB</LoginName>
<UserName>dbo</UserName>
<DatabaseName>EventData</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>Process_Audit_Messages_pr</ObjectName>
<ObjectType>PROCEDURE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>/*
Create procedure xxx as ...
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>What about :
declare @.x xml
set @.x = N'<EVENT_INSTANCE>
<EventType>ALTER_PROCEDURE</EventType>
<PostTime>2006-02-22T07:42:57.830</PostTime>
<SPID>55</SPID>
<ServerName>DBServer1\DEVELOPMENT</ServerName>
<LoginName>AAA/BBB</LoginName>
<UserName>dbo</UserName>
<DatabaseName>EventData</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>Process_Audit_Messages_pr</ObjectName>
<ObjectType>PROCEDURE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>/*
Create procedure xxx as ...
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>';
select n.value('local-name(.)', 'nvarchar(100)') as opt_name
, n.value('.', 'nvarchar(100)') as opt_value
from @.x.nodes('/EVENT_INSTANCE/TSQLCommand/SetOptions/@.*') as T(n)
Best regards
Michael
"SteveM" <stephencmyers@.hotmail.com> wrote in message
news:1140617241.326529.71400@.o13g2000cwo.googlegroups.com...
> How do I parse out the SetOptions in the EventData that is generated
> for DDL events? I would like to extract the various options and the
> values. Seems I would need to do this dynamically since I don't which
> set options would be present each time. Since these are not elements
> like the other entries, I'm having trouble getting at them.
>
> <EVENT_INSTANCE>
> <EventType>ALTER_PROCEDURE</EventType>
> <PostTime>2006-02-22T07:42:57.830</PostTime>
> <SPID>55</SPID>
> <ServerName>DBServer1\DEVELOPMENT</ServerName>
> <LoginName>AAA/BBB</LoginName>
> <UserName>dbo</UserName>
> <DatabaseName>EventData</DatabaseName>
> <SchemaName>dbo</SchemaName>
> <ObjectName>Process_Audit_Messages_pr</ObjectName>
> <ObjectType>PROCEDURE</ObjectType>
> <TSQLCommand>
> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
> ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
> <CommandText>/*
> Create procedure xxx as ...
> </CommandText>
> </TSQLCommand>
> </EVENT_INSTANCE>
>|||Thanks! Exactly what I was looking for.