Retrieving XML data to Table from dm_os_ring_buffers

set nocount on
IF Object_id('tempdb..#CTE_ring_buffer_Connectivity_Temp') IS NOT NULL 
   DROP TABLE #CTE_ring_buffer_Connectivity_Temp 

create table #CTE_ring_buffer_Connectivity_Temp
( RecordTime datetime,
text varchar(4096),
id int,
type varchar(50),
time varchar(50),
RecordType varchar(50),
RecordSource varchar(50),
Spid int,
SniConnectionId uniqueidentifier,
SniProvider int,
OSError int,
SniConsumerError int,
State int,
RemoteHost varchar(50),
RemotePort  varchar(50),
LocalHost  varchar(50),
LocalPort  varchar(50),
RecordTime2 datetime,
TotalLoginTimeInMilliseconds bigint,
LoginTaskEnqueuedInMilliseconds bigint,
NetworkWritesInMilliseconds bigint,
NetworkReadsInMilliseconds bigint,
SslProcessingInMilliseconds bigint,
SspiProcessingInMilliseconds bigint,
LoginTriggerAndResourceGovernorProcessingInMilliseconds  bigint,

TdsInputBufferError int,
TdsOutputBufferError int,
TdsInputBufferBytes int,
PhysicalConnectionIsKilled int,
DisconnectDueToReadError int,
 NetworkErrorFoundInInputStream int,
 ErrorFoundBeforeLogin int,
 SessionIsKilled int,
 NormalDisconnect int,
message_id int,
language_id int,
severity int,
is_event_logged int,
text2 varchar(4096))
-- Up to 1k records in the ring buffer are persisted for as long as the server is online, and after 1000 records, the buffer wraps around and begins replacing the oldest records. 
-- Define the CTE expression name and column list.

;
WITH CTE_ring_buffer_Connectivity
as
-- Define the CTE query.
(
SELECT
record.value('(Record/@id)[1]', 'int') as id,--column 1
record.value('(Record/@type)[1]', 'varchar(50)') as type,
record.value('(Record/@time)[1]', 'varchar(50)') as time,
record.value('(Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(50)') as RecordType,
record.value('(Record/ConnectivityTraceRecord/RecordSource)[1]', 'varchar(50)') as RecordSource,
record.value('(Record/ConnectivityTraceRecord/Spid)[1]', 'int') as Spid,
record.value('(Record/ConnectivityTraceRecord/SniConnectionId)[1]', 'uniqueidentifier') as SniConnectionId,
record.value('(Record/ConnectivityTraceRecord/SniProvider)[1]', 'int') as SniProvider,
record.value('(Record/ConnectivityTraceRecord/OSError)[1]', 'int') as OSError,
record.value('(Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') as SniConsumerError,
record.value('(Record/ConnectivityTraceRecord/State)[1]', 'int') as State,
record.value('(Record/ConnectivityTraceRecord/RemoteHost)[1]', 'varchar(50)') as RemoteHost,
record.value('(Record/ConnectivityTraceRecord/RemotePort)[1]', 'varchar(50)') as RemotePort,
record.value('(Record/ConnectivityTraceRecord/LocalHost)[1]', 'varchar(50)') as LocalHost,
record.value('(Record/ConnectivityTraceRecord/LocalPort)[1]', 'varchar(50)') as LocalPort,
record.value('(Record/ConnectivityTraceRecord/RecordTime)[1]', 'datetime') as RecordTime,
record.value('(Record/ConnectivityTraceRecord/LoginTimers/TotalLoginTimeInMilliseconds)[1]', 'bigint') as TotalLoginTimeInMilliseconds,
record.value('(Record/ConnectivityTraceRecord/LoginTimers/LoginTaskEnqueuedInMilliseconds)[1]', 'bigint') as LoginTaskEnqueuedInMilliseconds,
record.value('(Record/ConnectivityTraceRecord/LoginTimers/NetworkWritesInMilliseconds)[1]', 'bigint') as NetworkWritesInMilliseconds,
record.value('(Record/ConnectivityTraceRecord/LoginTimers/NetworkReadsInMilliseconds)[1]', 'bigint') as NetworkReadsInMilliseconds,
record.value('(Record/ConnectivityTraceRecord/LoginTimers/SslProcessingInMilliseconds)[1]', 'bigint') as SslProcessingInMilliseconds,
record.value('(Record/ConnectivityTraceRecord/LoginTimers/SspiProcessingInMilliseconds)[1]', 'bigint') as SspiProcessingInMilliseconds,
record.value('(Record/ConnectivityTraceRecord/LoginTimers/LoginTriggerAndResourceGovernorProcessingInMilliseconds)[1]', 'bigint') as LoginTriggerAndResourceGovernorProcessingInMilliseconds,
record.value('(Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferError)[1]', 'int') as TdsInputBufferError,
record.value('(Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsOutputBufferError)[1]', 'int') as TdsOutputBufferError,
record.value('(Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferBytes)[1]', 'int') as TdsInputBufferBytes,
record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/PhysicalConnectionIsKilled)[1]', 'int') as PhysicalConnectionIsKilled,
record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/DisconnectDueToReadError)[1]', 'int') as DisconnectDueToReadError,
record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/NetworkErrorFoundInInputStream)[1]', 'int') as NetworkErrorFoundInInputStream,
record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/ErrorFoundBeforeLogin)[1]', 'int') as ErrorFoundBeforeLogin,
record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/SessionIsKilled)[1]', 'int') as SessionIsKilled,
record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalDisconnect)[1]', 'int') as NormalDisconnect
--record.value('(Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalLogout)[1]', 'int') as NormalLogout
from

(SELECT CAST(record as xml) as record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY') as tab
)

INSERT INTO #CTE_ring_buffer_Connectivity_Temp 
SELECT c.RecordTime,m.[text],*
FROM CTE_ring_buffer_Connectivity c
LEFT JOIN sys.messages m ON c.SniConsumerError = m.message_id AND m.language_id = 1033
ORDER BY c.RecordTime DESC


go

select * from #CTE_ring_buffer_Connectivity_Temp

set nocount off