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.