I was asked to do the very same thing with XML a few weeks ago and this is a slightly simplified version of what I came up with:

declare @stringXML varchar(max), @XMLData as xml

set @stringXML =

'<app-data>

<FlowsNotification trandate="09/19/2008" type="Contribution" manager="BCA" custodian="">

<Contribution>

<transaction id="cm2428382">

<snam>TF1012</snam>

<style>DFISPM</style>

<date>09/18/2008</date>

<include>true</include>

<amount>200000.00</amount>

<originalamount>200000.00</originalamount>

<description>$JNL :Funds trans from acct (97302287)</description>

<accountid>97527748</accountid>

<custid>164</custid>

<custodian>CS</custodian>

<isAccountInCDB>true</isAccountInCDB>

</transaction>

</Contribution>

</FlowsNotification>

<account-data>

<account custAcctNo="97527748" custID="164" />

</account-data>

</app-data>'

set @XMLData = CAST(@stringXML AS XML)

select @XMLData as XMLData into #XMLTable

select x.y.value('Contribution[1]/transaction[1]/@id[1]','VARCHAR(20)') AS tranid,

x.y.value('@trandate[1]','Date') AS NotificationTranDate,

x.y.value('@manager[1]', 'CHAR(10)') AS MgrCode,

x.y.value('Contribution[1]/transaction[1]/snam[1]','VARCHAR(50)') AS ACCTsnam,

x.y.value('Contribution[1]/transaction[1]/style[1]','VARCHAR(20)') AS style,

x.y.value('Contribution[1]/transaction[1]/date[1]','Date') AS TranDate,

Case when x.y.value('Contribution[1]/transaction[1]/include[1]','varchar(50)') = 'true' then 1 else 0 end AS include,

x.y.value('Contribution[1]/transaction[1]/amount[1]','Float') AS amount,

x.y.value('Contribution[1]/transaction[1]/originalamount[1]','Float') AS originalamount,

x.y.value('Contribution[1]/transaction[1]/description[1]','VARCHAR(255)') AS tranDescription,

x.y.value('Contribution[1]/transaction[1]/accountid[1]','VARCHAR(50)') AS accountid,

x.y.value('Contribution[1]/transaction[1]/custid[1]','integer') AS custid,

x.y.value('Contribution[1]/transaction[1]/custodian[1]','varchar(20)') AS custodian,

Case when x.y.value('Contribution[1]/transaction[1]/isAccountInCDB[1]','varchar(50)') = 'true' then 1 else 0 end AS isAccountInCDB

FROM#XMLTable T

CROSS APPLY T.XMLData.nodes('app-data/FlowsNotification') x(y)

drop table #XMLTable

The query pulls out both elements and values of the sample XML provided.

I found the XML examples by Jacob Sebastian site very helpful http://beyondrelational.com/modules/2/blogs/28/posts/10279/xquery-labs-a-collection-of-xquery-sample-scripts.aspx.