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 */
|