CODEWITHSUNDEEP
xmlsql-server-2008xqueryrelational
user1320771
user1320771

Reputation:

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

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.

Upvotes: 7

Views: 8386

Answers (2)

Marc
Marc

Reputation: 1

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.

Upvotes: 0

marc_s
marc_s

Reputation: 754468

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

Upvotes: 9

Related Questions