• 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)

    "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