How to query for Xml values and attributes from table in SQL Server?

Actually you're close to your goal, you just need to use nodes() method to split your rows and then get values:

select    s.SqmId,    m.c.value('@id', 'varchar(max)') as id,    m.c.value('@type', 'varchar(max)') as type,    m.c.value('@unit', 'varchar(max)') as unit,    m.c.value('@sum', 'varchar(max)') as [sum],    m.c.value('@count', 'varchar(max)') as [count],    m.c.value('@minValue', 'varchar(max)') as minValue,    m.c.value('@maxValue', 'varchar(max)') as maxValue,    m.c.value('.', 'nvarchar(max)') as Value,    m.c.value('(text())[1]', 'nvarchar(max)') as Value2from sqm as s    outer apply s.data.nodes('Sqm/Metrics/Metric') as m(c)

sql fiddle demo

I've been trying to do something very similar but not using the nodes. However, my xml structure is a little different.

You have it like this:

<Metrics>    <Metric id="TransactionCleanupThread.RefundOldTrans" type="timer" ...>

If it were like this instead:

<Metrics>    <Metric>        <id>TransactionCleanupThread.RefundOldTrans</id>        <type>timer</type>        .        .        .

Then you could simply use this SQL statement.

SELECT    Sqm.SqmId,    Data.value('(/Sqm/Metrics/Metric/id)[1]', 'varchar(max)') as id,    Data.value('(/Sqm/Metrics/Metric/type)[1]', 'varchar(max)') AS type,    Data.value('(/Sqm/Metrics/Metric/unit)[1]', 'varchar(max)') AS unit,    Data.value('(/Sqm/Metrics/Metric/sum)[1]', 'varchar(max)') AS sum,    Data.value('(/Sqm/Metrics/Metric/count)[1]', 'varchar(max)') AS count,    Data.value('(/Sqm/Metrics/Metric/minValue)[1]', 'varchar(max)') AS minValue,    Data.value('(/Sqm/Metrics/Metric/maxValue)[1]', 'varchar(max)') AS maxValue,    Data.value('(/Sqm/Metrics/Metric/stdDeviation)[1]', 'varchar(max)') AS stdDeviation,FROM Sqm

To me this is much less confusing than using the outer apply or cross apply.

I hope this helps someone else looking for a simpler solution!

use value instead of query (must specify index of node to return in the XQuery as well as passing the sql data type to return as the second parameter):

select    xt.Id    , x.m.value( '@id[1]', 'varchar(max)' ) MetricIdfrom    XmlTest xt    cross apply xt.XmlData.nodes( '/Sqm/Metrics/Metric' ) x(m)