XML初次接觸

 with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data')
 SELECT top 5 CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset, event_time),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS '觸發(fā)審核的日期和時(shí)間' ,
--b.connect_time,
        sequence_number AS '單個(gè)審核記錄中的記錄順序' ,
        action_id AS '操作的 ID' ,
        succeeded AS '觸發(fā)事件的操作是否成功' ,
        permission_bitmask AS '權(quán)限掩碼' ,
        is_column_permission AS '是否為列級(jí)別權(quán)限' ,
        a.session_id AS '發(fā)生該事件的會(huì)話的 ID' ,
        server_principal_id AS '執(zhí)行操作的登錄上下文 ID' ,
        database_principal_id AS '執(zhí)行操作的數(shù)據(jù)庫(kù)用戶上下文 ID' ,
        target_server_principal_id AS '執(zhí)行 GRANT/DENY/REVOKE 操作的服務(wù)器主體' ,
        target_database_principal_id AS '執(zhí)行 GRANT/DENY/REVOKE 操作的數(shù)據(jù)庫(kù)主體' ,
        object_id AS '發(fā)生審核的實(shí)體的 ID(服務(wù)器對(duì)象，DB,數(shù)據(jù)庫(kù)對(duì)象，架構(gòu)對(duì)象)' ,
        class_type AS '可審核實(shí)體的類型' ,
        session_server_principal_name AS '會(huì)話的服務(wù)器主體' ,
        server_principal_name AS '當(dāng)前登錄名' ,
        server_principal_sid AS '當(dāng)前登錄名 SID' ,
        database_principal_name AS '當(dāng)前用戶' ,
        target_server_principal_name AS '操作的目標(biāo)登錄名' ,
        target_server_principal_sid AS '目標(biāo)登錄名的 SID' ,
        target_database_principal_name AS '操作的目標(biāo)用戶' ,
        server_instance_name AS '審核的服務(wù)器實(shí)例的名稱' ,
        database_name AS '發(fā)生此操作的數(shù)據(jù)庫(kù)上下文' ,
        schema_name AS '此操作的架構(gòu)上下文' ,
        object_name AS '審核的實(shí)體的名稱' ,
        statement AS 'TSQL 語(yǔ)句（如果存在）' ,
        additional_information AS '單個(gè)事件的唯一信息，以 XML 的形式返回' ,
        file_name AS '記錄來(lái)源的審核日志文件的路徑和名稱' ,
        audit_file_offset AS '包含審核記錄的文件中的緩沖區(qū)偏移量' ,
        user_defined_event_id AS '作為 sp_audit_write 參數(shù)傳遞的用戶定義事件 ID' ,
        user_defined_information AS '于記錄用戶想要通過(guò)使用 sp_audit_write 存儲(chǔ)過(guò)程記錄在審核日志中的任何附加信息'--,
 --b.CLIENT_NET_ADDRESS AS 'ClientIPAddress' --into MyAudit..Audit_DYDB_UPDL
 ,additional_information--.value('(/action_info/address)[1]','varchar(20)')
FROM   sys.[fn_get_audit_file]('D:\SqlAudits\MyAudit_1FE965A7-77D0-41A6-9D40-543162C722F2_0_131486627855460000.sqlaudit',
                                DEFAULT, DEFAULT) a --left join SYS.DM_EXEC_CONNECTIONS b with(nolock)
 --on a.session_id=b.session_id
where 
--CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset, event_time),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) between dateadd(mi, -5,getdate()) and getdate()
--and  
action_id='LGIS'




declare @xml xml='<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>4096</packet_data_size><address>10.20.50.11</address><is_dac>0</is_dac></action_info>';


with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data')
select @xml.value('(/action_info/address)[1]','varchar(20)')


<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>4096</packet_data_size><address>10.20.50.11</address><is_dac>0</is_dac></action_info>