2 Replies Latest reply: Dec 14, 2012 8:41 AM by Adrian88 RSS

    xquery for loop

    Adrian88
      Hi,

      I have below xquery:

      <ns11:Policies>
      {
           for $InsurancePolicy in $getUFGInsuranceHistoryResponse1/ns1:Policies/ns1:Policy
                                    return
           for $ClaimPolicy in $getUFGClaimHistoryResponse1/ns1:Policies/ns1:Policy
                return
           if ((data($InsurancePolicy/ns10:InsuranceProduct) eq data($ClaimPolicy/ns10:InsuranceProduct)) and
                                    (data($InsurancePolicy/ns10:PolicyNumber) eq data($ClaimPolicy/ns10:PolicyNumber)) and
                                    (data($InsurancePolicy/ns10:StartDate) eq data($ClaimPolicy/ns10:StartDate)) and
                                    (data($InsurancePolicy/ns10:EndDate) eq data($ClaimPolicy/ns10:EndDate)))

           then
           
           <ns10:PolicyReference111 ID = "{data($ClaimPolicy/@ID)}"/>
           
      else
      (
      <ns10:PolicyReference222 ID = "{data($InsurancePolicy/@ID)}"/>
      )
      }
      </ns11:Policies>


      I have to check if some policies of two collection are the same. If they have the same element value in if condition I want to return atribute ID from ClaimPolicy collection, in other case i want to return atribute ID from InsurancePolicy collection. The problem is that in response I have multiplicated ID's with the same value. I know that the for loop work in sequence way a so i have multiplicated ID's. Any one maybe know what condition or method should I use to achieve one collection of ID's from this two collection and this ID's can't be multiplicated.
      Thanks in advance.
        • 1. Re: xquery for loop
          odie_63
          Hi,

          Since you didn't provide sample data, here's a simplified example :
          declare variable $InsuranceResp := 
          document 
          {
          <Policies>
           <Policy ID="1">
            <InsuranceProduct>A</InsuranceProduct>
            <PolicyNumber>123</PolicyNumber>
            <StartDate>2012-01-01</StartDate>
            <EndDate>2012-12-31</EndDate>
           </Policy>
           <Policy ID="2">
            <InsuranceProduct>B</InsuranceProduct>
            <PolicyNumber>567</PolicyNumber>
            <StartDate>2012-01-01</StartDate>
            <EndDate>2012-12-31</EndDate>
           </Policy>
          </Policies>
          };
          
          declare variable $ClaimResp := 
          document 
          {
          <Policies>
           <Policy ID="3">
            <InsuranceProduct>A</InsuranceProduct>
            <PolicyNumber>123</PolicyNumber>
            <StartDate>2012-01-01</StartDate>
            <EndDate>2012-12-31</EndDate>
           </Policy>
           <Policy ID="4">
            <InsuranceProduct>B</InsuranceProduct>
            <PolicyNumber>999</PolicyNumber>
            <StartDate>2012-01-01</StartDate>
            <EndDate>2012-12-31</EndDate>
           </Policy>
          </Policies>
          };
          
          <Policies> 
          {
            for $ip in $InsuranceResp/Policies/Policy
            let $cp := (
              for $j in $ClaimResp/Policies/Policy
              where $j/InsuranceProduct = $ip/InsuranceProduct
                and $j/PolicyNumber = $ip/PolicyNumber
                and $j/StartDate = $ip/StartDate
                and $j/EndDate = $ip/EndDate
              return $j
            )
            return if ($cp)
                   then 
                     <PolicyReference1 ID="{data($cp/@ID)}"/>
                   else
                     <PolicyReference2 ID="{data($ip/@ID)}"/>
          }
          </Policies>
          Output :
          <Policies>
            <PolicyReference1 ID="3"/>
            <PolicyReference2 ID="2"/>
          </Policies>
          • 2. Re: xquery for loop
            Adrian88
            Thanks a lot! It wasn't exactly what I want but it give me an idea how to resolved it! Thx