Shredding data from XML, Importing into Relational Tables (SQL Server 2008)

Question

I have looked everywhere for help on this.

I'm new to all this and I'm finding it hard to understand all the documentation on it.

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,ground).

I would like to do this in SQL Server 2008, and from what I have read everywhere a useful method to do this is .nodes() method, but I just can't understand 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, the call to .nodes() create a pseudo "table" called Tbl with a single XML column called Foot that will contain each <team> XML node as its value.

Then, you can select from that pseudo table and extract the individual values of XML attributes (@manager) and elements (name, ground) from that <team> XML snippet, and convert those to a T-SQL data value of a type of your chosing.

To insert those values into your table - just use an INSERT statement 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