基于SizeType与Count计算Consumption值的XQuery实现问询

Got it, let's figure out how to calculate that Consumption value in XQuery based on your SizeType and Count conditions. Here's a straightforward solution that works with most modern XQuery processors:

Step 1: Use XQuery 3.0+ Copy-Modify Syntax (Cleanest Approach) #

This method lets you modify specific nodes in your existing XML without rebuilding the entire structure from scratch:

declare namespace ns0 = "http://testV4";

copy $originalXml := /ns0:Response
modify (
  for $record in $originalXml/ns0:DataDetails/ns0:ConsumptionPerRecord
  return replace node $record/ns0:Consumption with (
    let $size := $record/ns0:SizeType/text()
    let $count := xs:decimal($record/ns0:Count/text())
    let $calculatedValue := 
      if ($size = "A") then $count * 0.5
      else if ($size = "B") then $count * 1.0
      else 0  <!-- Add more conditions here for SizeType C/D etc. -->
    return <ns0:Consumption>{$calculatedValue}</ns0:Consumption>
  )
)
return $originalXml

How This Works: #

1. Namespace Declaration: We first declare the ns0 namespace to match your input XML—critical for avoiding node selection errors.
2. Copy Original XML: The copy keyword creates a duplicate of your input so we don't alter the source data directly.
3. Modify Target Nodes: We loop through every ConsumptionPerRecord node, then:
   • Pull the SizeType text and convert Count to a decimal (ensures accurate numeric calculations).
   • Use if-else logic to apply the correct multiplier based on SizeType.
   • Replace the original Consumption node with our calculated value.
4. Return Modified XML: Finally, we output the updated document.

Step 2: Compatibility Version (For Older XQuery Processors) #

If your environment doesn't support XQuery 3.0, you can rebuild the XML structure explicitly while inserting the calculated values:

declare namespace ns0 = "http://testV4";

<ns0:Response>
  {$originalXml/ns0:ReferenceData}
  <ns0:DataDetails>
    {$originalXml/ns0:DataDetails/ns0:Status}
    {
      for $record in $originalXml/ns0:DataDetails/ns0:ConsumptionPerRecord
      return
        <ns0:ConsumptionPerRecord>
          {$record/ns0:SizeType}
          {$record/ns0:Count}
          <ns0:Consumption>
            {
              let $size := $record/ns0:SizeType/text()
              let $count := xs:decimal($record/ns0:Count/text())
              return
                if ($size = "A") then $count * 0.5
                else if ($size = "B") then $count * 1.0
                else 0
            }
          </ns0:Consumption>
          {$record/ns0:NotConsumed}
        </ns0:ConsumptionPerRecord>
    }
    {$originalXml/ns0:DataDetails/ns0:Message}
    {$originalXml/ns0:DataDetails/ns0:StatusCode}
  </ns0:DataDetails>
</ns0:Response>

Example Output #

Running either script on your input XML will produce this updated document:

<ns0:Response xmlns:ns0="http://testV4">
  <ns0:ReferenceData>Dummy Client reference</ns0:ReferenceData>
  <ns0:DataDetails>
    <ns0:Status>Not Available</ns0:Status>
    <ns0:ConsumptionPerRecord>
      <ns0:SizeType>A</ns0:SizeType>
      <ns0:Count>2</ns0:Count>
      <ns0:Consumption>1.0</ns0:Consumption>
      <ns0:NotConsumed>5</ns0:NotConsumed>
    </ns0:ConsumptionPerRecord>
    <ns0:ConsumptionPerRecord>
      <ns0:SizeType>B</ns0:SizeType>
      <ns0:Count>2</ns0:Count>
      <ns0:Consumption>2.0</ns0:Consumption>
      <ns0:NotConsumed>2</ns0:NotConsumed>
    </ns0:ConsumptionPerRecord>
    <ns0:Message>Success</ns0:Message>
    <ns0:StatusCode>1000</ns0:StatusCode>
  </ns0:DataDetails>
</ns0:Response>

Adding More SizeTypes #

Just extend the if-else block to handle additional values (like C):

let $calculatedValue := 
  if ($size = "A") then $count * 0.5
  else if ($size = "B") then $count * 1.0
  else if ($size = "C") then $count * 1.5  <!-- Example multiplier for C -->
  else 0

内容的提问来源于stack exchange，提问作者kumarb