Your first Xpath Expression for Item/ItemNumber is wrong
Instead of
N.value('(Item/ItemNumber[1])','int')
You want
N.value('(Item/ItemNumber)[1]','int')
declare @packXML XML = '<Pack>
<PackID>1</PackID>
<Item>
<ItemNumber>1</ItemNumber>
<Qty>1</Qty>
</Item>
</Pack>
<Pack>
<PackID>2</PackID>
<Item>
<ItemNumber>1</ItemNumber>
<Qty>5</Qty>
</Item>
<Item>
<ItemNumber>2</ItemNumber>
<Qty>10</Qty>
</Item>
</Pack>
'
select N.value('PackID[1]','int'), N.value('(Item/ItemNumber)[1]','int'), N.value('(Item/Qty)[1]','int')
from @packXML.nodes('Pack') as T(N)
Thanks Alan, that resolves the error, but now I realize that it doesn't give me the results I'm looking for.
I really need a row for each Item node, so my expected results should be:
This should do the trick:
SELECT
N.value('(../PackID/text())[1]', 'int'),
N.value('(ItemNumber/text())[1]','int'),
N.value('(Qty/text())[1]','int')
FROM @packXML.nodes('Pack/Item') as T(N);
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001