CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'D:\OpenXMLTesting.xml', SINGLE_BLOB) AS x;
--DECLARE FILENAME HERE^^^^^

--This creates the Parsed_XML table that data will be retrieved from later
DECLARE @XML XML = (select XMLData from XMLwithOpenXML)
CREATE TABLE Parsed_XML
(EventID varchar(200)
,EventType varchar(200)
,EventCategory varchar(200)
,EventTimestamp datetime
,EventCausedBy varchar(200)
,"EventData" varchar(max)
)

--This parses the raw data from the XMLwithOpenXML table and inserts it into the PARSED_XML as columns and rows
INSERT INTO Parsed_XML
SELECT
EventID = Events.value('(id)[1]', 'varchar(200)'),
EventType = Events.value('(type)[1]','varchar(200)'),
EventCategory = Events.value('(category)[1]','varchar(200)'),
EventTimestamp = Events.value('(timestamp)[1]','datetime'),
EventCausedBy = Events.value('(causedbyuser)[1]','varchar(200)'),
"EventData" = Events.value('(data)[1]','varchar(max)')
FROM @XML.nodes('/root/event') AS Xtbl(Events)

--See the results
SELECT * FROM Parsed_XML ORDER BY EventTimestamp DESC

--Sample Query
--Once the above tables are built, this will return the number of events by hour. Useful for finding high activity periods.

SELECT CAST(EventTimeStamp as date) AS ForDate,
DATEPART(hour,EventTimeStamp) AS OnHour,
COUNT(*) AS Totals
FROM PARSED_XML
GROUP BY CAST(EventTimeStamp as date),
DATEPART(hour,EventTimeStamp)
ORDER BY ForDate, OnHour

--Once analysis is complete, remember to clean up after yourself and drop the unnecessary tables.
/*
DROP TABLE XMLwithOpenXML
DROP TABLE Parsed_XML
*/