Home Forums Programming XML XQuery error RE: XQuery error

  • April 24, 2017 at 4:25 pm

    #1939688

    Lisa Cherney - Monday, April 24, 2017 3:37 PM

    Alan.B - Monday, April 24, 2017 3:24 PM

    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:
    https://www.sqlservercentral.com/Forums/Uploads/Images/3a0b1d27-7de6-4e15-9563-43f6.PNG

    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