Cross apply / XML shredding -- mutiple nodes creating problems

• jmetape

  Right there with Babe

  Points: 729

  More actions

  February 26, 2018 at 10:14 am

  #77754

  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: 1706

  More actions

  February 26, 2018 at 10:29 am

  #1981144

  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

  Right there with Babe

  Points: 729

  More actions

  February 26, 2018 at 11:30 am

  #1981161

  You the man

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  February 26, 2018 at 2:45 pm

  #1981198

  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)