XML.value - I can't get it right
-
Sorry for the stupid question, but I cannot figure out this thing.
I'm querying an audit file with an xml column called additional_information which looks like this:
<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><pooled_connection>0</pooled_connection>
<client_options>0x28000020</client_options>
<client_options1>0x0001f438</client_options1>
<connect_options>0x00000000</connect_options>
<packet_data_size>8192</packet_data_size>
<address>local machine</address>
<is_dac>0</is_dac>
</action_info>
All I want from there is the address. Using Google I have written the following but it returns NULL.
SELECTserver_principal_name,event_time,
CAST(additional_information AS xml).value('(action_info/address/text())[1]', 'varchar(255)') as address
FROMsys.fn_get_audit_file ('e:\MSSQL\Backup\8E26E52B.sqlaudit',default,default);
What am I doing wrong?
-
Try adding
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data')
to the start of your query
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply