Home:Learn XQuery:White Papers:ACORD:Search and Inquiry Using Multiple Joins

Performing a Search and Inquiry Using Multiple Joins

Now consider a more complicated scenario, one in which we receive a message that contains a party inquiry, but one that also requests information about related objects (tc=204 with InquiryLevel set to 2). A "party" in ACORD terms refers to a generic entity that can be associated to people, organizations or trusts; information like details about person/organization, address, phone and more are all related to a party.

In our case, the party inquiry message request looks like this (click here to open the XML document):

<tx:TXLife xmlns:tx="http://ACORD.org/Standards/Life/2">
    <tx:UserAuthRequest>
        <tx:UserLoginName>username</tx:UserLoginName>
        <tx:UserPswd>
            <tx:Pswd>password</tx:Pswd>
        </tx:UserPswd>
    </tx:UserAuthRequest>

    <tx:TXLifeRequest>
        <tx:TransRefGUID>f2965a64-903b-4616-aa9c-afa6ed04e1b0</tx:TransRefGUID>
        <tx:TransType tc="204">Party Inquiry</tx:TransType>
        <tx:TransExeDate>2007-06-01</tx:TransExeDate>
        <tx:TransExeTime>17:00:13-05:00</tx:TransExeTime>
        <tx:InquiryLevel tc="2">OLI_INQUIRY_OBJREL</tx:InquiryLevel>

        <tx:OLifE Version="2.14.00">
            <tx:Party id="Party_1">
                <tx:PartyTypeCode tc="1">Person</tx:PartyTypeCode>
                <tx:GovtID>222222222</tx:GovtID>
                <tx:Person>
                    <tx:FirstName>John</tx:FirstName>
                    <tx:LastName>Smith</tx:LastName>
                </tx:Person>
            </tx:Party>
        </tx:OLifE>
    </tx:TXLifeRequest>
</tx:TXLife>

The XQuery handling this request looks very similar to what we have seen before:

declare namespace tx = "http://ACORD.org/Standards/Life/2";
import module namespace example = "http://www.datadirect.com/xquery/examples" at "acord_verbs.xquery";

declare variable $request as document-node(element(*, xs:untyped)) external;

<tx:TXLife> {
    for $r in $request/tx:TXLife/tx:TXLifeRequest
    return example:reply-204($r)
}
</tx:TXLife>

This time the code responsible for computing the response needs to join data from two tables in the relational database; we use the GovtID information in the message to select the Party and then join with Person based on PartyKey:

declare function example:reply-204($request as element(tx:TXLifeRequest)) as element(tx:TXLifeResponse)? {
    let $results :=
        for $p in $request/tx:OLifE/tx:Party
        let $govtID := $p/tx:GovtID
        (: Party inquiry on GovtID :)
        for $party in collection("ACORD_PARTY")/ACORD_PARTY[GovtID eq $govtID]
        let $partyKey := $party/PartyKey
        (:
            Example of 1-1 relation.
            ACORD_PERSON.PartyKey must match with ACORD_PARTY.PartyKey
        :)
        for $person in collection("ACORD_PERSON")/ACORD_PERSON[PartyKey eq $partyKey]
        return example:create-person($party, $person, $request/tx:InquiryLevel/@tc)

    return
        example:create-response( $request,
                                  (
                                    <tx:TransResult>
                                        <tx:ResultCode tc="1">Success</tx:ResultCode>
                                    </tx:TransResult>,
                                    <tx:OLifE Version="2.12.00">
                                    {$results}
                                    </tx:OLifE>
                                  )
                                )
    
};

Once again, note how the XQuery author is only focusing on navigating the various data sources as if they all were native XML structures; the underlying DataDirect XQuery™ engine handles the part of code that deals with relational data by issuing the proper SELECT statements to resolve the join condition to the relational database.

What's Next

Go to Validating Update Requests to see how you can use DataDirect XQuery™ to validate data prior to updating relational database records.