XQuery Nested Query

Question

I can't figure out how to execute this query:

Using XQuery, For each rep number, output:

A. firstname, lastname of the rep.

B. customer name for each customer associated with the rep.

C.total amount of money (excluding tax) billed for all customers associated with the rep.

Premiere Database schema:

-A Customer structure has attributes CustomerNumber, RepNum (assigned rep), etc.

-A Rep structure has attributes RepNum, etc.

-An Orders structure has attributes CustomerNumber, OrderNum, etc.

-An OrderLine structure has attributes OrderNum, NumOrdered, QuotedPrice(per -item), etc.

*Multiple order lines can have the same order number.

Sample desired output:

Q4 Output:

<results>
  <rep repnum="20" firstname="Valerie" lastname="Kaiser">
    <customer name="Al's Appliance and Sport"/>
    <customer name="Kline's"/>
    <customer name="All Season"/>
    <Revenue total="$736.45"/>
  </rep>
  <rep repnum="35" firstname="Richard" lastname="Hull">
    <customer name="Brookings Direct"/>
    <customer name="The Everything Shop"/>
    <customer name="Lee's Sport and Appliance"/>
    <customer name="Deerfield's Four Seasons"/>
    <Revenue total="$2,509.80"/>
  </rep>
  <rep repnum="65" firstname="Juan" lastname="Perez">
    <customer name="Ferguson's"/>
    <customer name="Bargains Galore"/>
    <customer name="Johnson's Department Store"/>
    <Revenue total="$5,664.89"/>
  </rep>
</results>

The output I'm getting:

<results>
  <rep>
    <rep repnum="20" firstname="Valerie" lastname="Kaiser"/>
    <customer name="Al's Appliance and Sport"/>
    <customer name="Kline's"/>
    <customer name="All Season"/>
    <Revenue total="0"/>
  </rep>
  <rep>
    <rep repnum="35" firstname="Richard" lastname="Hull"/>
    <customer name="Brookings Direct"/>
    <customer name="The Everything Shop"/>
    <customer name="Lee's Sport and Appliance"/>
    <customer name="Deerfield's Four Seasons"/>
    <Revenue total="0"/>
  </rep>
  <rep>
    <rep repnum="65" firstname="Juan" lastname="Perez"/>
    <customer name="Ferguson's"/>
    <customer name="Bargains Galore"/>
    <customer name="Johnson's Department Store"/>
    <Revenue total="0"/>
  </rep>
</results>

Revenue per rep is 0!

My code:

<results>
  {
    for $r in doc("../premiere/Rep.xml")//Rep
    let $c := doc("../premiere/Customer.xml")//Customer[RepNum = $r/RepNum]

    return 
      <rep>
        <rep repnum = "{$r/RepNum}" firstname="{$r/FirstName}" lastname="{$r/LastName}"/>

        {for $customer in $c 
        return
           <customer name= "{ $customer/CustomerName}"/>}

        <Revenue total=  "{sum(
             for $customer in $c 
             let $o := doc("../premiere/Orders.xml")//Orders[CustomerNumber = $customer/CustomerNumber]
             let $customerAmount := sum(
                 for $order in $o
                 let $l := doc("../premiere/OrderLine.xml")//OrderLine[OrderNum = $order/OrderNum]
                 let $orderAmount := format-number(sum(
                     for $lineItem in $l
                     let $LineAmount := (data($lineItem/NumOrdered) * data($lineItem/QuotedPrice))
                     return 
                       $LineAmount
                 ), '$,000.00')
                 return
                   $orderAmount
             )
             return
               $customerAmount
         )}"
         />
      </rep>

  }
</results>

Thanks in advance!

In $orderAmount you are formatting the sum, which returns a string. Then in $customerAmount you are summing this string. I would expect that to operation raise an error (sum requires values that can be cast as xs:double and that are of a type that supports addition, requirements that the xs:string data type does not meet), so why you're getting 0 as the result of this operation rather than an error is strange to me. See the function signature for fn:sum(). w3.org/TR/xpath-functions-31/#func-sum — Joe Wicentowski

Leo Wörteler Leo Wörteler · Accepted Answer · 2017-03-30T14:10:05

First, as @joewiz pointed out, you format the partial sums into strings before you sum them up, which should definitely raise an error if executed. Since it does not, I would suspect that the outer sum never adds up any values.

You did not provide any example data, but I found this SQL file for a Premiere data set that seems to match yours. One important difference is that CUSTOMER has an attribute CUSTOMER_NUM, not CUSTOMER_NUMBER like in your description. If the element is also called CustomerNum instead of CustomerNumber in your data set, then let $o := doc("../premiere/Orders.xml")//Orders[CustomerNumber = $customer/CustomerNumber] will silently be evaluated to the empty sequence and the sum is 0.

Here is a version of your query that works for me and produces the exact output you wanted when evaluated on the values from the SQL file:

<results>{
  for $r in doc("../premiere/Rep.xml")//Rep
  let $c := doc("../premiere/Customer.xml")//Customer[RepNum = $r/RepNum]
  return <rep>{
      <rep repnum = "{$r/RepNum}" firstname="{$r/FirstName}" lastname="{$r/LastName}"/>,

      for $customer in $c 
      return <customer name="{$customer/CustomerName}"/>,

      let $total :=
        sum(
          for $customer in $c
          for $order in doc("../premiere/Orders.xml")//Orders[CustomerNum = $customer/CustomerNumber]
          for $lineItem in doc("../premiere/OrderLine.xml")//OrderLine[OrderNum = $order/OrderNum]
          return $lineItem/NumOrdered * $lineItem/QuotedPrice
        )
      return <Revenue total="{format-number($total, '$,000.00')}"/>
  }</rep>
}</results>

I have simplified it by merging the two sums into one and inlining a few variables, and I moved the number formatting out to the correct spot.

XQuery Nested Query

1 Answers