Or.... here's a really simple example but should get you going....

/* Set up a table variable to hold the XML*/

DECLARE @table TABLE

(

RiskXML XML

)

/* Insert some very simple XML */

INSERT INTO @table

SELECT '<RISK>

<ID>1</ID>

<VALUE>20</VALUE>

</RISK>'

UNION ALL SELECT '<RISK>

<ID>2</ID>

<VALUE>20</VALUE>

</RISK>'

UNION ALL SELECT '<RISK>

<ID>3</ID>

<VALUE>20</VALUE>

</RISK>'

/* Prove it's in there! */

SELECT * FROM @table

/* This is how we can flatten (shred) it */

SELECT

r.risk.value('(ID)[1]', 'int') AS ID,

r.risk.value('(VALUE)[1]', 'int') AS Value

FROM

@table t

CROSS APPLY t.RiskXML.nodes('RISK') r(risk)

/* Simple aggregations on it */

SELECT

COUNT(r.risk.value('(ID)[1]', 'int')) AS [Count],

SUM(r.risk.value('(VALUE)[1]', 'int')) AS [Sum]

FROM

@table t

CROSS APPLY t.RiskXML.nodes('RISK') r(risk)