I want to accomplish the equivalent t

select a.id, b.name from a left join b on a.id = b.i

I have two xml documents

<?xml version="1.0" standalone="yes"?><CodesFile><CatNumber><number>1</number><code>001</code></CatNumber><CatNumber><number>2</number><code>002</code></CatNumber></CodesFil

and

<?xml version="1.0" standalone="yes"?><NewDataSet><Table><Number_>1</Number_><category_name>Regular </category_name></Table><Table><Number_>2</Number_><category_name>Overtime</category_name></Table><Table></NewDataset

as you can see number from the first xml document is the same as number_ from the second one.  What I need, however, is the category_name from the second xml document where number_ and number are the same.  If these documents where tables it would be easy because I could write

select doc1.number, doc2.category_name, doc1.code from doc1 left join doc2 on doc1.number = doc1.number

I have read on xpath and xquery but I can't find an example that would give me the ability to do this

Any ideas

Thank

Danny