Read XML file 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)