Home:Learn XQuery:White Papers:ACORD:Complex Update Requests

Managing Complex Update Requests

A more complicated update scenario (compare to the simple one described in Updating a Relational Database) might be one in which we receive a party update message (tc=186). Party update messages of this type allow for the possibility of inserting or deleting rows in multiple tables.

The sample XQuery file acord_186-update.xquery processes a party update message in XML format (available by clicking here) twice — inserting new entries the first time, and deleting them the second time:

declare namespace tx = "http://ACORD.org/Standards/Life/2";

import module namespace example = "http://www.datadirect.com/xquery/examples" at "acord_verbs.xquery";

(:Party Update Transaction (delete + insert) :)
for $request in doc("186-5.xml")/tx:TXLife/tx:TXLifeRequest
return (
        example:delete-186($request),
        example:insert-186($request)
        )

As the update message is dealing with “Party” information, multiple tables are affected when deleting or inserting entries; for example, this is the function that takes care of deleting all the information related to a specific Party stored in the database:

declare updating function example:delete-186($request as element(tx:TXLifeRequest)) {
    if ( fn:not(fn:empty($request/tx:OLifE/tx:Party/tx:Organization)) )
    (: update of organization not supported in example :)
    then fn:error((), "update organization not supported")
    else
        (
        (: look up existing Party based on GovtID :)
        for $partyKey in collection("ACORD_PARTY")/ACORD_PARTY[GovtID eq $request/tx:OLifE/tx:Party/tx:GovtID]/PartyKey
        return
            (
            (: Example delete of Party with 1 - 1 relation {Party -> Person}:)
            ddtek:sql-delete( collection("ACORD_PARTY")/ACORD_PARTY[PartyKey eq $partyKey] ),
            ddtek:sql-delete( collection("ACORD_PERSON")/ACORD_PERSON[PartyKey eq $partyKey] ),

            (: Example deleting an 1 - n relation {Party -> Address} :)
            ddtek:sql-delete( collection("ACORD_ADDRESS")/ACORD_ADDRESS[OwnerKey eq $partyKey] ),

            (: Example deleting an n - n relation {Party <-> Phone} :)
            for $relation in collection("ACORD_RELATION")/ACORD_RELATION
                         [OriginatingObjectKey eq $partyKey]
                         [OriginatingObjectType eq $example:OLI_PARTY]
                         [RelatedObjectType eq $example:OLI_PHONE]
            return (
                    ddtek:sql-delete($relation),
                    ddtek:sql-delete(collection("ACORD_PHONE")/ACORD_PHONE[PhoneKey eq $relation/RelatedObjectKey])
                    )
            )
        )
};

What's Next

What about EDI? Go to Using DataDirect XQuery™ with EDI and see how you can use DataDirect XQuery™ to work with older, non-XML standards like ACORD EDI or even X12 EDI message types.