Import 'xml' into Sql Server

Question

I have a file that is structured like so:

<?xml version="1.0" encoding="UTF-8"?> <EventSchedule>     <Event Uid="2" Type="Main Event">         <IsFixed>True</IsFixed>         <EventKind>MainEvent</EventKind>         <Fields>             <Parameter Name="Type" Value="TV_Show"/>             <Parameter Name="Name" Value="The Muppets"/>             <Parameter Name="Duration" Value="00:30:00"/>         </Fields>     </Event>     <Event>     ...and so on     </Event> </EventSchedule> 

I'm not entirely sure if it is valid XML, however I need to import it into SQL Server but everything I try doesn't seem to work.

Please could anyone point me in the right direction either with some example code or a recommendation on which method to use?

I'd ideally like to get the raw data into a flat table, along the lines of:

Name        | Type    | Duration | EventKind  The Muppets | TV_Show | 00:30:00 | MainEvent 

Finally this is coming from fairly large files and I will need to import the regularly.

Thanks, pugu

Answer 1

Try this:

DECLARE @XML XML = '<EventSchedule>     <Event Uid="2" Type="Main Event">         <IsFixed>True</IsFixed>         <EventKind>MainEvent</EventKind>         <Fields>             <Parameter Name="Type" Value="TV_Show"/>             <Parameter Name="Name" Value="The Muppets"/>             <Parameter Name="Duration" Value="00:30:00"/>         </Fields>     </Event>     <Event Uid="3" Type="Secondary Event">         <IsFixed>True</IsFixed>         <EventKind>SecondaryEvent</EventKind>         <Fields>             <Parameter Name="Type" Value="TV_Show"/>             <Parameter Name="Name" Value="The Muppets II"/>             <Parameter Name="Duration" Value="00:30:00"/>         </Fields>     </Event> </EventSchedule>'  SELECT     EventUID = Events.value('@Uid', 'int'),     EventType = Events.value('@Type', 'varchar(20)'),     EventIsFixed =Events.value('(IsFixed)[1]', 'varchar(20)'),     EventKind =Events.value('(EventKind)[1]', 'varchar(20)') FROM  @XML.nodes('/EventSchedule/Event') AS XTbl(Events) 

Gives me an output of:

And of course, you can easily do an

INSERT INTO dbo.YourTable(EventUID, EventType, EventIsFixed, EventKind)    SELECT           ...... 

to insert that data into a relational table.

Update: assuming you have your XML in files - you can use this code to load the XML file into an XML variable in SQL Server:

DECLARE @XmlFile XML  SELECT @XmlFile = BulkColumn FROM  OPENROWSET(BULK 'path-to-your-XML-file', SINGLE_BLOB) x; 

and then use the above code snippet to parse the XML.

Update #2: if you need the parameters, too - use this XQuery statement:

SELECT     EventUID = Events.value('@Uid', 'int'),     EventType = Events.value('@Type', 'varchar(20)'),     EventIsFixed = Events.value('(IsFixed)[1]', 'varchar(20)'),     EventKind = Events.value('(EventKind)[1]', 'varchar(20)'),     ParameterType = Events.value('(Fields/Parameter[@Name="Type"]/@Value)[1]', 'varchar(20)'),     ParameterName = Events.value('(Fields/Parameter[@Name="Name"]/@Value)[1]', 'varchar(20)'),     ParameterDuration = Events.value('(Fields/Parameter[@Name="Duration"]/@Value)[1]', 'varchar(20)') FROM     @XML.nodes('/EventSchedule/Event') AS XTbl(Events) 

Results in: