How do i getting many Rows from a single XML column?

using SQL 2005...

I have a table called training. It has 2 columns ID and data.
The ID column is a INT.
The data column is an XML data type that contains XML looking like the following:
<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005<ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk<ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat<ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>

if we asume that the table has 3 rows with the ID field being 1-3 and all the XML is the same as above.
I'm trying create an SQL statement that outputs the following results

id	ProgramName	UrgencyLevel
1	Learn SQL 2005	3
1	Learn To Walk	1
1	Learn To Eat	1
2	Learn SQL 2005	3
2	Learn To Walk	1
2	Learn To Eat	1
3	Learn SQL 2005	3
3	Learn To Walk	1
3	Learn To Eat	1

please can some one help me?

You will need to use the nodes() to break your xml into multiple rows then CROSS APPLY to that. For example:

Dan

CREATE TABLE #t1(
id int,
data xml
)

INSERT INTO #t1 SELECT 1, '<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005</ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>'

INSERT INTO #t1 SELECT 2, '<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005</ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>'

INSERT INTO #t1 SELECT 2, '<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005</ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>'

SELECT ID, t.c.value('ProgramName[1]', 'NVARCHAR(MAX)') + ' ' + t.c.value('UrgencyLevel[1]', 'NVARCHAR(MAX)')
FROM #t1
CROSS APPLY
#t1.data.nodes('TrainingRequests/TrainingRequest') AS t(c)

|||Thank you, you saved me ripping out the rest of my hair!!!