another n00b question

Man am I ever being thick this week.

M'kay, I've been banging my head against the wall for the last few hours trying to do what conceptually seems quite easy.

Basically I've got an input variable to an SP (in SQL Express 2005) set as type xml. The XML being passed accross looks like this:

<i:items xmlns:i="http://www.mydn.co.uk/schema/boxcheck/list">

<i:item number="12345" />

<i:item number="12346" />

<i:item number="11223" />

</i:items>

I also have a table which has a field called Id which contains, for now at least, the same values as the @.number attribute in the above XML. All I wish to do is to extract the @.number value from the XML above and use that value to check the Id field in the table for any matches. If a match is found, return the xml field in the table.

I've looked at nodes() and query() and exist() and different structures of XQuery, and have tried other methods using normal SQL select statements along with XQuery, but I just cant get the damn thing to work. Can someone, anyone, for the love of all this is holy, please help me out here? what's the big obvious noisy annoying thing I'm missing, apart from a brain?

Cheers for any replies

See if this helps

declare @.x xml
set @.x='<i:items xmlns:i="http://www.mydn.co.uk/schema/boxcheck/list">
<i:item number="12345" />
<i:item number="12346" />
<i:item number="11223" />
</i:items>'

SELECT X.n.value('@.number','int') as [number]
FROM @.x.nodes('declare default element namespace
"http://www.mydn.co.uk/schema/boxcheck/list";
/items/item') AS X(n)

|||

Cheers for the reply

The problem I've had all along was using the value extracted from the input xml (I did it a different way to you as it happens) to get the associated row from the table with the same id value

any clues?

|||

I'm not totally clear what you're asking. If the code below
doesn't help, try posting some DDL and sample data.

declare @.x xml
set @.x='<i:items xmlns:i="http://www.mydn.co.uk/schema/boxcheck/list">
<i:item number="12345" />
<i:item number="12346" />
<i:item number="11223" />
</i:items>'

SELECT xmlField
FROM mytable
WHERE ID IN (
SELECT X.n.value('@.number','int') as [number]
FROM @.x.nodes('declare default element namespace
"http://www.mydn.co.uk/schema/boxcheck/list";
/items/item') AS X(n)
)

|||Man you a supastar! cheers dude