Cross apply / XML shredding -- mutiple nodes creating problems

  • jmetape

    Mr or Mrs. 500

    Points: 516

    Oh XML Gurus please help. I don't have a clear understanding of CROSS APPLY. 

    I have the following xml (2 orders and 3 line items each):

    SET @x = '
    <root><salesOrder orderNumber="100001" customerNumber="JAC001" orderDate="01-01-2007">
    <lineItems>
    <item itemNumber="A001" qty="10" rate="10.5" />
    <item itemNumber="A002" qty="20" rate="11" />
    <item itemNumber="A003" qty="30" rate="13" />
    </lineItems>
    </salesOrder>
    <salesOrder orderNumber="100002" customerNumber="JAC003" orderDate="01-01-2010">
    <lineItems>
    <item itemNumber="A012" qty="10" rate="25" />
    <item itemNumber="A013" qty="20" rate="16" />
    <item itemNumber="A016" qty="30" rate="14" />
    </lineItems>
    </salesOrder>

    </root>'

    I have the following query which returns 12 records--6 line items for each order   (i want 6 records -- 3 for each order)

    SELECT
    y.header.value('@orderNumber[1]', 'varchar(20)') as OrderNumber,

    x.item.value('@itemNumber[1]','varchar(10)') AS ItemNumber,
    x.item.value('@qty[1]','int') AS Qty,

    x.item.value('@rate[1]','float') AS Rate
    FROM @x.nodes('//item') AS x(item)
    CROSS APPLY @x.nodes('//salesOrder') AS y(header)

    here is the recordset I want (i finagled data to get this screenshot, so no smart @** haha). 

    Please help....

  • Mikael Eriksson SE

    SSCommitted

    Points: 1688

    select O.X.value('@orderNumber', 'varchar(20)') as OrderNumber,
           I.X.value('@itemNumber', 'varchar(10)') as ItemNumber,
           I.X.value('@qty', 'varchar(10)') as Qty,
           I.X.value('@rate', 'float') as Rate
    from @x.nodes('/root/salesOrder') as O(X)
    cross apply O.X.nodes('lineItems/item') as I(X);

  • jmetape

    Mr or Mrs. 500

    Points: 516

    You the man

  • Scott Coleman

    One Orange Chip

    Points: 27348

    There is another way to do it by finding all the item nodes and using xpath expressions to navigate back to the grandparent salesOrder node. This is going to be slower than the answer posted above, I just wanted to post it so you would know what the alternative looks like.

    SELECT OrderNumber = I.X.value('../../@orderNumber', 'varchar(20)'),
     ItemNumber = I.X.value('@itemNumber', 'varchar(10)'),
     Qty = I.X.value('@qty', 'int'),
     Rate = I.X.value('@rate', 'float')
    FROM @x.nodes('//item') I(X)

    The other thing I wanted to point out is that your original query used [1] qualifiers on XML node attributes, which is unnecessary. A given node can't have multiple attributes of the same name, so you don't have to specify which one you want. If the XML format used elements instead of attributes like this:

    <item>
    <itemNumber>A001</itemNumber>
    <qty>10</qty>
    <rate>10.5</rate>
    </item>

    then you have to use the [1] qualifiers.

    SELECT OrderNumber = I.X.value('../../@orderNumber', 'varchar(20)'),
     ItemNumber = I.X.value('itemNumber[1]', 'varchar(10)'),
     Qty = I.X.value('qty[1]', 'int'),
     Rate = I.X.value('rate[1]', 'float')
    FROM @x.nodes('//item') I(X)

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply