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.

Melosa

Good question, and +1 for putting Liverpool at the top :-) – Myriad

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

Varlet

Fantastic answer Marc (a fellow Scot into the bargain ;) ) I have a couple of questions if you don't mind... 1) I'm in a position where I will be doing this for multiple documents which shall have different structures. I am aiming to write an application in Java using JDBC to shred these various XML's into tables. How should I go about this? 2) Are there any advantages to be gained from Hybrid shredding? – Melosa

(1) depends on whether you just want to load these XML documents into SQL Server and shred them there - or shred them beforehand, in Java, and insert the relational results - whatever is easier for you. (2) explain hybrid shredding - what do you mean by that? – Varlet

From what I understand it is where some XML is stored in its native form, while some is 'shredded'. Thanks for your answer again Marc – Melosa

@user1320771: guess it depends on whether you'll ever need that original XML back - for whatever reason. If so: you can totally store that XML into a SQL Server XML column and retrieve it later. Otherwise, I don't see any benefit in storing the XML alongside the relational data - you can always create an XML representation of your relational data if needed – Varlet

Answer 2

With simple XML you can use the XML adaptor in SSIS. It automatically creates an XSD. No programming needed. If the XML is more complex use www.eXtractor.ONE. A very generic method that handles every type of XML.

Pipit