Home:Learn XQuery:White Papers:ACORD:Updating a Relational Database

Updating a Relational Database

Once we know that the address change request message (tc=181) is valid (see Validating Update Requests), we need to process that request and commit any changes to our relational database. DataDirect XQuery has the ability to perform updates against relational data (http://www.xquery.com/examples/updates/). The XQuery implementing that update operation is shown here (click here to open the XQuery document):

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;

(: Address Change :)
example:update-181($request/tx:TXLife/tx:TXLifeRequest)

The example:update-181() function is fairly simple, and it relies on the ddtek:sql-update() function that allows rows in relational tables to be modified directly from your XQuery:

declare updating function example:update-181($request as element(tx:TXLifeRequest)) {
    let $party := $request/tx:OLifE/tx:Party[@id eq $request/@PrimaryObjectID]
    let $key := xs:string($party/tx:PartyKey)
    let $oldAddress := $party/tx:Address[@DataRep eq "Removed"]
    let $newAddress := $party/tx:Address[@DataRep eq "Full"]
    let $row := collection("ACORD_ADDRESS")/ACORD_ADDRESS
            [OwnerKey eq $key]
            [AddressTypeCode eq xs:integer($oldAddress/tx:AddressTypeCode/@tc)]
            [Line1 eq $oldAddress/tx:Line1]
            [City eq $oldAddress/tx:City]
            (: nullable :)
            [AddressState eq $oldAddress/tx:AddressState
             or
             fn:empty(AddressState) and fn:empty($oldAddress/tx:AddressState)]
            [Zip eq $oldAddress/tx:Zip]
            [AddressCountry eq $oldAddress/tx:AddressCountry]
    return ddtek:sql-update( $row,
                             "AddressTypeCode",
                             xs:integer($newAddress/tx:AddressTypeCode/@tc),
                             "Line1",

                             $newAddress/tx:Line1,
                             "AddressState",
                             $newAddress/tx:AddressState,
                             "Zip",
                             $newAddress/tx:Zip,
                             "AddressCountry",
                             $newAddress/tx:AddressCountry
           )
};

What's Next

Go to Handling Complex Update Requests to see how you can use DataDirect XQuery™ to manage update requests that require changes to multiple tables.