How to iterate through XML file using XQuery to build an XML request?

Question

I have an xml file where in I have to iterate and build a request file. The XQuery which I have written is not iterating through all the elements I am stuck as to how to include those lineitems which have productCode != "NOT_AVAILABLE"

declare function local:constructSubscriberLineItems($LineItems){
    for $productCode in distinct-values($LineItems/LineItem/ProductCode)
    let $entries := $LineItems/LineItem[ProductCode = $productCode]
    let $notAvailable := $entries[ProductCode/text() = "NOT_AVAILABLE"]
    for $distinctProductType in distinct-values($notAvailable/ProductType)
    let $productType := $notAvailable[ProductType = $distinctProductType]
    for $distinctBillingCode in distinct-values($productType/BillingCode)
    let $BillingCode := $productType[BillingCode = $distinctBillingCode] 
    let $subscriberLineItemDetails :=

    <subscriberLineItemDetails>
        <productType>{distinct-values($BillingCode/ProductType/text())}</productType>
        <productCode>{distinct-values($BillingCode/ProductCode/text())}</productCode>
        <billingCode>{distinct-values($BillingCode/BillingCode/text())}</billingCode>
        <quantity>{sum($BillingCode/Quantity)}</quantity>
    </subscriberLineItemDetails>

    return $subscriberLineItemDetails
};

Sample XML

<LineItems>
    <LineItem>
        <Id>01</Id>
        <ProductCode>prod123456</ProductCode>
        <BillingCode>CHE001</BillingCode>
        <ProductType>HARDGOOD</ProductType>
        <Quantity>10</Quantity>
    </LineItem>
    <LineItem>
        <Id>02</Id>
        <ProductCode>prod123456</ProductCode>
        <BillingCode>CHE001</BillingCode>
        <ProductType>HARDGOOD</ProductType>
        <Quantity>10</Quantity>
    </LineItem>
    <LineItem>
        <Id>03</Id>
        <ProductCode>prod123456</ProductCode>
        <BillingCode>CHE001</BillingCode>
        <ProductType>HARDGOOD</ProductType>
        <Quantity>10</Quantity>
    </LineItem>
    <LineItem>
        <Id>04</Id>
        <ProductCode>prod6789</ProductCode>
        <BillingCode>CHE001</BillingCode>
        <ProductType>HARDGOOD</ProductType>
        <Quantity>4</Quantity>
    </LineItem>
    <LineItem>
        <Id>05</Id>
        <ProductCode>prod6789</ProductCode>
        <BillingCode>CHE001</BillingCode>
        <ProductType>HARDGOOD</ProductType>
        <Quantity>5</Quantity>
    </LineItem>
    <LineItem>
        <Id>06</Id>
        <ProductCode>prod6789</ProductCode>
        <BillingCode>CHE001</BillingCode>
        <ProductType>HARDGOOD</ProductType>
        <Quantity>7</Quantity>
    </LineItem>
    <LineItem>
        <Id>07</Id>
        <ProductCode>NOT_AVAILABLE</ProductCode>
        <BillingCode>BANG001</BillingCode>
        <ProductType>OPTIONAL_FEATURE</ProductType>
        <Quantity>7</Quantity>
    </LineItem>
    <LineItem>
        <Id>08</Id>
        <ProductCode>NOT_AVAILABLE</ProductCode>
        <BillingCode>BANG001</BillingCode>
        <ProductType>OPTIONAL_FEATURE</ProductType>
        <Quantity>7</Quantity>
    </LineItem>
    <LineItem>
        <Id>09</Id>
        <ProductCode>NOT_AVAILABLE</ProductCode>
        <BillingCode>TRV001</BillingCode>
        <ProductType>OPTIONAL_FEATURE</ProductType>
        <Quantity>7</Quantity>
    </LineItem>
    <LineItem>
        <Id>10</Id>
        <ProductCode>NOT_AVAILABLE</ProductCode>
        <BillingCode>BANG001</BillingCode>
        <ProductType>INCLUDED_FEATURE</ProductType>
        <Quantity>7</Quantity>
    </LineItem>
    <LineItem>
        <Id>11</Id>
        <ProductCode>NOT_AVAILABLE</ProductCode>
        <BillingCode>BANG001</BillingCode>
        <ProductType>INCLUDED_FEATURE</ProductType>
        <Quantity>7</Quantity>
    </LineItem>
    <LineItem>
        <Id>12</Id>
        <ProductCode>NOT_AVAILABLE</ProductCode>
        <BillingCode>TRV001</BillingCode>
        <ProductType>INCLUDED_FEATURE</ProductType>
        <Quantity>7</Quantity>
    </LineItem>
</LineItems>

Expected Output

<subscriberLineItemDetails>
    <productType>HARDGOOD</productType>
    <productCode>prod123456</productCode>
    <billingCode>CHE001</billingCode>
    <quantity>30</quantity>
</subscriberLineItemDetails>
<subscriberLineItemDetails>
    <productType>HARDGOOD</productType>
    <productCode>prod6789</productCode>
    <billingCode>CHE001</billingCode>
    <quantity>16</quantity>
</subscriberLineItemDetails>
<subscriberLineItemDetails>
    <productType>OPTIONAL_FEATURE</productType>
    <productCode>NOT_AVAILABLE</productCode>
    <billingCode>BANG001</billingCode>
    <quantity>14</quantity>
</subscriberLineItemDetails>
<subscriberLineItemDetails>
    <productType>OPTIONAL_FEATURE</productType>
    <productCode>NOT_AVAILABLE</productCode>
    <billingCode>TRV001</billingCode>
    <quantity>7</quantity>
</subscriberLineItemDetails>
<subscriberLineItemDetails>
    <productType>INCLUDED_FEATURE</productType>
    <productCode>NOT_AVAILABLE</productCode>
    <billingCode>BANG001</billingCode>
    <quantity>14</quantity>
</subscriberLineItemDetails>
<subscriberLineItemDetails>
    <productType>INCLUDED_FEATURE</productType>
    <productCode>NOT_AVAILABLE</productCode>
    <billingCode>TRV001</billingCode>
    <quantity>7</quantity>
</subscriberLineItemDetails>

Which version of XQuery / which execution engine? If this were 3.0, your code could arguably be simplified a bit. — Charles Duffy

Chondrops Chondrops · Accepted Answer · 2016-09-26T13:06:48

The group by clause is almost certainly your friend here. It looks like you want to aggregate LineItems which have the same ProductCode, and do something a little different for items which are 'NOT_AVAILABLE'. I don't get exactly what you need to do different for not available ones (whether you need to aggregate those based on some other combination of values) but the following code should get you on your way.

for $items in LineItems/LineItem
group by $val := $items/ProductCode
return 
if ($val != 'NOT_AVAILABLE') then
    <subscriberLineItemDetails>
      <productType>{$items[1]/ProductType/text()}</productType>
      <productCode>{$val}</productCode>
      <billingCode>{$items[1]/BillingCode/text()}</billingCode>
    <quantity>{sum($items/Quantity)}</quantity>
  </subscriberLineItemDetails>
else
(: Different handling for NOT_AVAILABLE line items. You can add a group by clause for these as well 
   if you need to aggregate them in some way :)
for $item in $items
return
  <subscriberLineItemDetails>
    <productType>{$item/ProductType/text()}</productType>
    <productCode>{$item/ProductCode}</productCode>
    <billingCode>{$item/BillingCode/text()}</billingCode>
    <quantity>{sum($item/Quantity)}</quantity>
  </subscriberLineItemDetails>

How to iterate through XML file using XQuery to build an XML request?

1 Answers