Read XML file in SQL Server 2008 How can I read an XML file and store the data in XML to our table in SQL Server 2008?
This parse node based XML. It's different to read attributes but it isn't as common

I had this lying around as a demo with 3 slightly different XPath queries

DECLARE @foo XML

SELECT @foo = N'
&lt;harrys&gt;
    &lt;harry&gt;
        &lt;fish&gt;0.015000000000&lt;/fish&gt;
        &lt;bicycle&gt;2008-10-31T00:00:00+01:00&lt;/bicycle&gt;
        &lt;foo&gt;ü&lt;/foo&gt;
    &lt;/harry&gt;
    &lt;harry&gt;
        &lt;fish&gt;0.025000000000&lt;/fish&gt;
        &lt;bicycle&gt;2008-08-31T00:00:00+01:00&lt;/bicycle&gt;
        &lt;foo&gt;ä&lt;/foo&gt;
    &lt;/harry&gt;
&lt;/harrys&gt;
'

SELECT
    CAST(CAST(y.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(y.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(y.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('/*') x(item)
    CROSS APPLY
    x.item.nodes('./*') AS y(item)

SELECT
    CAST(CAST(x.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(x.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(x.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('harrys/harry') x(item)

SELECT
    CAST(CAST(y.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(y.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(y.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('/harrys') x(item)
    CROSS APPLY
    x.item.nodes('./harry') AS y(item)