Shredding data from XML, importing into relational tables (SQL Server 2008)

Question

I have been looking for help everywhere.

I'm new to all of this, and it's hard for me to understand all the documentation on it.

Let's say I have this XML:

<footballteams> <team manager="Benitez"> <name>Liverpool</name> <ground>Anfield</ground> </team> <team manager="Mourinho"> <name>Chelsea</name> <ground>Stamford Bridge</ground> </team> <team manager="Wenger"> <name>Arsenal</name> <ground>Highbury</ground> </team> </footballteams> 

I want to take the data from this and load it into a relational table called footballteams (name, manager, land).

I would like to do this in SQL Server 2008, and from what I read everywhere, the .nodes () method is a useful method for this, but I just can't figure out how to use it.

Answer 1

Try something like this:

 DECLARE @input XML = '<footballteams> <team manager="Benitez"> <name>Liverpool</name> <ground>Anfield</ground> </team> <team manager="Mourinho"> <name>Chelsea</name> <ground>Stamford Bridge</ground> </team> <team manager="Wenger"> <name>Arsenal</name> <ground>Highbury</ground> </team> </footballteams>' SELECT TeamName = Foot.value('(name)[1]', 'varchar(100)'), Manager = Foot.value('(@manager)', 'varchar(100)'), Ground = Foot.value('(ground)[1]', 'varchar(100)') FROM @input.nodes('/footballteams/team') AS Tbl(Foot) 

Basically, calling .nodes() creates a pseudo-table called Tbl with one XML column called Foot that will contain each <team> XML node as its value.

Then you can select from this pseudo-table and extract individual values ​​of XML attributes ( @manager ) and elements ( name , ground ) from this XML <team> fragment and convert them to T -SQL data value of the type of your choice.

To insert these values ​​into your table, simply use the INSERT based on this:

 ;WITH ShreddedData AS ( SELECT TeamName = Foot.value('(name)[1]', 'varchar(100)'), Manager = Foot.value('(@manager)', 'varchar(100)'), Ground = Foot.value('(ground)[1]', 'varchar(100)') FROM @input.nodes('/footballteams/team') AS Tbl(Foot) ) INSERT INTO dbo.FootballTeams(Name, Manager, Ground) SELECT TeamName, Manager, Ground FROM ShreddedData 

Answer 2

With simple XML, you can use the XML adapter in SSIS. It automatically creates an XSD. No programming required. If XML is more complex, use www.eXtractor.ONE. A very common method that processes each type of XML.