PDF

XQuery update
Use cases for DB2 pureXML in the health care, business, financial
and IT industries
Susan Malaika ([email protected])
Senior Technical Staff Member
IBM
09 October 2008
Jan-Eike Michels ([email protected])
Software Engineer
IBM
Christian Pichler
Data Server Solutions
IBM
XQuery, the query language for XML, can be used to modify XML. In this article, learn about
the various ways to modify XML, including XML stored in memory or in a DB2® pureXML™
database. Explore the different ways to modify XML in four different use cases: health care,
business, financial derivatives, and information technology, while utilizing DB2 pureXML.
Finally, examine guidelines for when to use XQuery to perform either sub-document updates or
full document replacement and versioning of the stored XML.
Introduction
Increasingly, XML is being used as a message exchange format in a variety of industries. Often,
industry consortia or governments define the structure of these exchange messages. The growing
popularity of different initiatives are encouraging the use of these XML exchange messages.
These initiatives include, for example, Information as a Service and Software as a Service (SaaS),
along with the dominance of technologies such as Web Services, File Transfer Protocol (FTP),
messaging, e-mail, and Web based feed information.
As organizations consume and produce these exchange messages, they are also beginning to
store the messages directly (for example, for audit purposes). In some systems, these stored
messages are the primary source of up-to-date information for supporting the business of an
institution or firm.
© Copyright IBM Corporation 2008
XQuery update
Trademarks
Page 1 of 20
developerWorks®
ibm.com/developerWorks/
There are cases where it is desirable to modify a stored XML message or derive a new message
from an existing stored message. Here are some examples to produce an updated XML message
that:
• Relates to ("links" to) existing information. For example, in health care, additional test results
may be produced, so a new patient record is created, based on the existing record, which is
then augmented with recent medical results.
• Incorporates additional information. For example, in business, additional items may be added
subsequently to an order. Often, the procedure is to cancel the original order and produce a
new order with both the content of the original order and the additional order items.
• Incorporates modified information. For example, in financial derivatives processing, a new
party may replace an existing party at a particular point in time, through a process called
novation.
XQuery, the language that can be used to query XML documents, has added extensions to
perform sub-document updates on XML documents. These extensions make it possible to add
new nodes, delete or rename existing nodes, and replace existing nodes and their values. (See
Resources for more information on XQuery.)
This article explains how to apply the XQuery Update Facility, a W3C standard, to XML stored in
DB2. Furthermore, the XQuery Update Facility is then illustrated in the context of four industries:
health care, business, financial derivatives, and information technology. The article bases its
examples on typical message exchange formats for those industries. It complements an earlier
article entitled "Update XML in DB2 9.5" (developerWorks, October 2007), which covers the basics
of XQuery update, and shows that it is easier and more efficient to use XQuery update to modify
XML than to read, parse, and modify XML in applications.
You can try out XQuery update by downloading some of the Industry Bundles, which are free and
publicly available as part of the DB2 pureXML online demonstration named "Industry Formats and
Services with pureXML" (see Resources).
Ways of modifying XML
The different options to modify and potentially update XML, also illustrated in Figure 1, include:
• Modify XML in memory: XML is modified, but modifications are not stored permanently.
• Modify XML on disk and store modified XML in-place: A modified version of the XML is
created based on the original XML. The modified XML is then stored, instead of the original
XML, thus replacing the original XML.
• Modify XML on disk and store new version of modified XML: A modified version of the
XML is created based on the original XML. The modified version is then stored separate from
the original version.
XQuery update
Page 2 of 20
ibm.com/developerWorks/
developerWorks®
Figure 1. Different options to modify and potentially update XML
Usage patterns for modifying XML through XQuery update
The ways to modify XML are the basis for the patterns for performing updates on XML using the
XQuery update support. The different update patterns, also illustrated in Figure 2, include:
•
•
•
•
Updating XML stored in-place
Inserting a modified version of XML
Updating XML on the fly (in memory) from an XML request to produce an XML response
Hiding parts of the XML content through an XQuery update view
Figure 2. XQuery update patterns
Updating XML stored in-place and inserting updated versions of XML are the two main ways to
persist the modified XML. Updating XML on the fly does not store the XML request or response
in a database, although one or both can be stored. Hiding parts of the XML content through an
XQuery update view does not modify the stored content, but allows the utilization of security
features of a traditional relational view.
The following paragraphs describe each of the patterns in more detail. Furthermore, examples are
provided that assume an existing table in a DB2 pureXML database with two or more columns;
one column is of type XML.
XQuery update
Page 3 of 20
developerWorks®
ibm.com/developerWorks/
Updating XML stored in-place
Updating XML that is stored in-place means that sub-document updates are performed on the
original XML using the XQuery Update notation.
Listing 1 illustrates an SQL/XML statement with an embedded XQuery update statement to modify
the zip code in the original XML and then replace the original XML with the modified XML:
Listing 1. XQuery update to replace original XML with updated XML
UPDATE xmlcustomer
SET info = XMLQUERY('transform
copy
$new := $i
modify
do replace value of $new/customerinfo/addr/zipcode
with 90111
return
$new' PASSING info AS "i")
WHERE cid = 1000;
Inserting a modified version of XML
Another way to update existing XML while maintaining the original XML is to insert a complete,
new, and updated record in addition to the existing, original, XML.
Listing 2 shows an SQL/XML statement with an embedded XQuery statement to insert a new row
with the modified XML based on original XML:
Listing 2. XQuery update to insert a modified version of XML in a database
INSERT INTO xmlcustomer (cid,
info)
VALUES (1003,
XMLQUERY('transform
copy
$new := $i
modify
do replace value of $new/customerinfo/addr/zipcode
with 90111
return
$new' PASSING (SELECT info
FROM
xmlcustomer
WHERE cid=1000) as "i"));
Updating XML on the fly from an XML request to produce an XML response
Updating XML on the fly means that XML is modified in memory while it is retrieved from an XML
variable or column.
Listing 3 illustrates an XQuery statement that modifies the zip code in the XML on the fly while it is
retrieved:
Listing 3. XQuery update to produce a modified version of XML in memory
XQUERY
for
$doc in db2-fn:sqlquery('SELECT info
FROM
xmlcustomer
WHERE cid=1000')
return transform
copy
$new := $doc
modify
do replace value of $new/customerinfo/addr/zipcode
with 90111
return
$new;
XQuery update
Page 4 of 20
ibm.com/developerWorks/
developerWorks®
Hiding parts of XML content through an XQuery update view
Sometimes it is necessary to hide or obscure parts of XML for certain users, for example, hiding
social security numbers from medical staff or removing sensitive customer information for survey
purposes. It is possible to create views on XML that apply XQuery update to hide or restructure
portions of XML. Note that you should carefully check the performance of the access through the
XQuery update views.
Listing 4 illustrates the hiding of customer name, street, zip code, and phone number from
software that accesses the XML through the anonymousCust view:
Listing 4. XQuery update in a view to hide portions of the XML
CREATE VIEW anonymousCust (custInfo)
AS SELECT XMLQUERY('for
$ci in $i
return transform
copy
$c := $i
modify
(do delete $c/customerinfo/name,
do delete $c/customerinfo/addr/street,
do delete $c/customerinfo/addr/zipcode,
do delete $c/customerinfo/phone)
return
$c' PASSING info AS "i")
FROM xmlcustomer;
HL7 update (Versioning and chaining)
Health Level 7 (HL7) is a Standards Developing Organization (SDO) in the clinical and
administrative area of health care. The basis of HL7 is the Reference Information Model (RIM),
which is an information model where all of HL7's specifications are derived from. One of these
specifications is the Clinical Document Architecture (CDA), which describes the structure and
semantics of clinical documents for the purpose of exchange. CDA documents can contain
information such as the name, address, gender, or medication of a patient.
Besides the structure and semantics contained in a CDA document, the specification also defines
how to perform updates on existing CDA documents. The following is an example situation where
a CDA document could be updated:
The patient Megan Brown had to visit her local practitioner since she needed to have
blood work done. However, Megan did not provide her address details during her visit
at the practitioner but submitted her address details several days after she had her
blood work done.
As the example above shows, the CDA document would now need to be updated with Megan's
address. To do so, the CDA document itself could be updated. However, the CDA specification
describes that existing CDA documents must not be updated directly. Instead, a copy of the
original document must be created and updated accordingly. In addition to the updated copy of the
CDA document, a reference must be added to the updated document that points to the existing,
original, CDA document. (For more information on the CDA, see Resources.)
Figure 3 illustrates the update of a CDA document according to specification:
XQuery update
Page 5 of 20
developerWorks®
ibm.com/developerWorks/
Figure 3. Scenario to update CDA document
Each CDA document has an element <id> that assigns each document a unique identifier. The
versioning of CDA documents is accomplished by the elements <setId> and <versionNumber>. An
original document and its updated version have the same <setId> but different <versionNumber>,
where <setId> indicates that both documents belong together and the <versionNumber> indicates
the version. An addition to the versioning information, it is also necessary to define so-called
"relationships" between the documents. Applied to the sample of an original document that gets
replaced by its updated version, it is necessary to define a relationship that shows the replacement
of the document by another one. The definition of such a relationship is achieved through the
element <relatedDocument>.
Code snippets
Listing 5 illustrates an excerpt from an existing CDA document containing patient information but
missing important contact information:
Listing 5. Excerpt from original HL7 CDA sample XML document
...
<id root="2.16.840.1.113883.3.18.1" extension="2"/>
<setId root="2.16.840.1.113883.3.18.1" extension="2"/>
<versionNumber value="1" />
...
<patient>
<name>
<family>Megan</family>
<given>Brown</given>
</name>
<administrativeGenderCode
code="F"
codeSystem="2.16.840.1.113883.5.1" />
<birthTime value="198110070000" />
<birthplace>
<place>
<addr>
<city>San Jose</city>
<country>United States of America</country>
XQuery update
Page 6 of 20
ibm.com/developerWorks/
developerWorks®
<postalCode>95102</postalCode>
</addr>
</place>
</birthplace>
</patient>
...
According to the example illustrated in Figure 2, the CDA document needs to be augmented with
the address information of the patient. Listing 6 illustrates an excerpt from a CDA document, which
was augmented with address information of the patient. In addition, the excerpt also contains the
necessary versioning information.
Listing 6. Excerpt from updated HL7 CDA sample XML document
...
<id root="2.16.840.1.113883.3.18.1" extension="2-1"/>
<setId root="2.16.840.1.113883.3.18.1" extension="2"/>
<versionNumber value= "2" />
...
<addr>
<streetName>Martine Ave</streetName>
<houseNumber>11</houseNumber>
<houseNumberNumeric>11</houseNumberNumeric>
<city>San Jose</city>
<postalCode>95102</postalCode>
<country>United States of America</country>
</addr>
<patient>
<name>
<family>Megan</family>
<given>Brown</given>
</name>
<administrativeGenderCode
code="F"
codeSystem="2.16.840.1.113883.5.1" />
<birthTime value="198110070000" />
...
</patient>
...
<relatedDocument typeCode="RPLC">
<parentDocument>
<id root="2.16.840.1.113883.3.18.1"
extension="2" />
</parentDocument>
</relatedDocument>
...
To perform the update of the CDA document while maintaining the original CDA document and
adding proper versioning information, use the SQL/XML statement illustrated in Listing 7:
Listing 7. SQL/XML statement to perform update of a CDA document
INSERT INTO BUCKET (id, document)
VALUES (11,
XMLQUERY('declare default element namespace "urn:hl7-org:v3";
for
$doc in $p
let
$newAddress := <addr>
<streetName>Martine Ave</streetName>
<houseNumber>11</houseNumber>
<houseNumberNumeric>11</houseNumberNumeric>
<city>San Jose</city>
XQuery update
Page 7 of 20
developerWorks®
let
let
let
return
ibm.com/developerWorks/
<postalCode>95102</postalCode>
<country>United States of America</country>
</addr>
$IdRoot := $doc/ClinicalDocument/id/@root
$IdExtension := $doc/ClinicalDocument/id/@extension
$relationship := <relatedDocument typeCode="RPLC">
<parentDocument>
<id root="{$IdRoot}"
extension="{$IdExtension}" />
</parentDocument>
</relatedDocument>
transform
copy
$c := $doc
modify
(do insert $newAddress
after $c/ClinicalDocument/recordTarget/patientRole/id,
do replace
value of $c/ClinicalDocument/versionNumber/@value
with xs:integer($c/ClinicalDocument/versionNumber/@value)+1,
do replace
value of $c/ClinicalDocument/id/@extension
with fn:concat($c/ClinicalDocument/id/@extension, "-1"),
do insert $relationship
after $c/ClinicalDocument/recordTarget)
return
$c' PASSING (SELECT document
FROM bucket
WHERE id=1) as "p")
)@
UBL purchase order (Versioning)
Universal Business Language (UBL) (see Resources) is developed by an Organization for the
Advancement of Structured Information Standards (OASIS) Technical Committee (see Resources)
with the goal to define a royalty-free library of standard electronic XML business documents such
as purchase orders and invoices. The OASIS Technical Committee defines UBL as "... designed to
plug directly into existing business, legal, auditing, and records management practices, eliminating
the re-keying of data in existing fax- and paper-based supply chains and providing an entry point
into electronic commerce or small and medium-sized businesses".
Among other items, Version 2.0 of UBL contains XML schemas for an "Order" and an
"OrderChange" document, which are used in the following simple example scenario. In this
scenario, one business partner (the buyer) submits an order to the other business partner (the
seller). The seller acknowledges the order by sending back an "OrderResponse" document.
Shortly after receiving the order response, the buyer notices that the delivery address in the
original order is incorrect. The buyer now has a few options to correct this error:
1. Submit an "OrderChange" document (Alternative 1; illustrated in Figure 4)
2. Cancel the original order and submit a new order (Alternative 2; illustrated in Figure 5)
3. Use other out-of-band communication (for example, phone the seller directly)
Listing 8 shows an excerpt from the original Order XML document with the incorrect delivery
address:
Listing 8. Excerpt from a UBL Order XML document, with an incorrect delivery
address
<Order ...
XQuery update
Page 8 of 20
ibm.com/developerWorks/
developerWorks®
...
<cbc:UUID>6E09886B-DC6E-439F-82D1-7CCAC7F4E3B1</cbc:UUID>
<cbc:IssueDate>2007-10-15</cbc:IssueDate>
<cac:BuyerCustomerParty>...</cac:BuyerCustomerParty>
<cac:SellerSupplierParty>...</cac:SellerSupplierParty>
...
<cac:Delivery>
<cac:DeliveryAddress>
<cbc:StreetName>Avon Way</cbc:StreetName>
<cbc:BuildingName>Thereabouts</cbc:BuildingName>
<cbc:BuildingNumber>56A</cbc:BuildingNumber>
<cbc:CityName>Bridgetown</cbc:CityName>
<cbc:PostalZone>ZZ99 1ZZ</cbc:PostalZone>
<cac:Country>
<cbc:IdentificationCode>GB</cbc:IdentificationCode>
</cac:Country>
</cac:DeliveryAddress>
<cac:RequestedDeliveryPeriod>
...
</cac:RequestedDeliveryPeriod>
</cac:Delivery>
...
<cac:OrderLine>
<cac:LineItem>...</cac:LineItem>
...
</cac:OrderLine>
...
</Order>
Alternative 1. Buyer submits an OrderChange document to rectify the mistake
Figure 4. Alternative 1 to correct an existing purchase order
The buyer uses an XQuery expression (shown in Listing 10) to produce an OrderChange
document (shown in Listing 9) from the existing Order document (shown in Listing 8).
Listing 9. Excerpt from an UBL OrderChange XML document with the correct
delivery address
<OrderChange ...
...
<cbc:UUID>6E09886B-DC6E-439F-82D1-8BCFC7A4ECCC</cbc:UUID>
<cbc:IssueDate>2007-10-16</cbc:IssueDate>
<cbc:SequenceNumberID>1</cbc:SequenceNumberID>
<cac:BuyerCustomerParty>...</cac:BuyerCustomerParty>
<cac:SellerSupplierParty>...</cac:SellerSupplierParty>
...
<cac:Delivery>
<cac:DeliveryAddress>
<cbc:StreetName>Stratford Way</cbc:StreetName>
<cbc:BuildingNumber>111</cbc:BuildingNumber>
<cbc:CityName>Bridgetown</cbc:CityName>
<cbc:PostalZone>ZZ99 1ZZ</cbc:PostalZone>
<cac:Country>
<cbc:IdentificationCode>GB</cbc:IdentificationCode>
</cac:Country>
</cac:DeliveryAddress>
XQuery update
Page 9 of 20
developerWorks®
ibm.com/developerWorks/
<cac:RequestedDeliveryPeriod>
...
</cac:RequestedDeliveryPeriod>
</cac:Delivery>
...
<cac:OrderLine>
<cac:LineItem>...</cac:LineItem>
...
</cac:OrderLine>
...
</OrderChange>
Listing 10. XQuery expression to produce the UBL OrderChange XML
document (Listing 9) from the UBL Order XML document (Listing 8)
XQUERY
declare default element namespace
"urn:oasis:names:specification:ubl:schema:xsd:Order-2";
declare namespace cac =
"urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2";
declare namespace cbc =
"urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2";
for
let
let
return
$doc in db2-fn:sqlquery('select data from ubladmin.ubltable where id=100')
$SequenceNumber := <cbc:SequenceNumberID>1</cbc:SequenceNumberID>
$newAddress := <cac:DeliveryAddress>
<cbc:StreetName>Stratford Way</cbc:StreetName>
<cbc:BuildingNumber>111</cbc:BuildingNumber>
<cbc:CityName>Bridgetown</cbc:CityName>
<cbc:PostalZone>ZZ99 1ZZ</cbc:PostalZone>
<cac:Country>
<cbc:IdentificationCode>GB</cbc:IdentificationCode>
</cac:Country>
</cac:DeliveryAddress>
transform
copy
$c := $doc
modify
(do rename $c/Order
as "OrderChange",
do replace
value of $c/Order/cbc:UUID
with "6E09886B-DC6E-439F-82D1-8BCFC7A4ECCC",
do replace
value of $c/Order/cbc:IssueDate
with "2007-10-16",
do insert $SequenceNumber
after $c/Order/cbc:IssueDate,
do replace $c/Order/cac:Delivery/cac:DeliveryAddress
with $newAddress)
return
$c
The buyer then submits the OrderChange document to the seller. To accommodate for the
differences between an Order document and an OrderChange document, the XQuery expression
has to:
•
•
•
•
Rename the <Order> element to "OrderChange"
Replace the values of the UUID and IssueDate elements with new ones
Insert a new SequenceNumber and
Replace the existing <cac:DeliveryAddress> element with a newly constructed one
Instead of completely replacing the existing <cac:DeliveryAddress> element, you could also only
replace the incorrect sub-elements, as shown in Listing 11:
XQuery update
Page 10 of 20
ibm.com/developerWorks/
developerWorks®
Listing 11. XQuery expression to produce the new UBL Order XML document
(Listing 12) from the UBL Order XML document (Listing 8)
XQUERY
declare default element namespace
"urn:oasis:names:specification:ubl:schema:xsd:Order-2";
declare namespace cac =
"urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2";
declare namespace cbc =
"urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2";
for
$doc in db2-fn:sqlquery('select data from ubladmin.ubltable where id=100')
return transform
copy
$c := $doc
modify
(do replace
value of $c/Order/cbc:UUID
with "6E09886B-DC6E-439F-82D1-8BCFC7A4ECCC",
do replace
value of $c/Order/cbc:IssueDate
with "2007-10-16",
do replace
value of $c/Order/cac:Delivery/cac:DeliveryAddress/cbc:StreetName
with "Stratford Way",
do replace value of
$c/Order/cac:Delivery/cac:DeliveryAddress/cbc:BuildingNumber
with "111",
do delete
$c/Order/cac:Delivery/cac:DeliveryAddress/cbc:BuildingName)
return
$c
Alternative 2. Buyer cancels the existing order and submits a new Order document to
correct the error
Figure 5. Alternative 2 to correct an existing purchase order
The buyer could use an XQuery expression, similar to the one shown in Listing 11, to produce a
new Order document (shown in Listing 12) from the existing Order document (shown in Listing 8)
and submit this to the seller.
Listing 12. Excerpt from the new UBL Order XML document with the correct
address
<Order ...
...
<cbc:UUID>6E09886B-DC6E-439F-82D1-8BCFC7A4ECCC</cbc:UUID>
<cbc:IssueDate>2007-10-16</cbc:IssueDate>
<cac:BuyerCustomerParty>...</cac:BuyerCustomerParty>
<cac:SellerSupplierParty>...</cac:SellerSupplierParty>
...
<cac:Delivery>
<cac:DeliveryAddress>
<cbc:StreetName>Stratford Way</cbc:StreetName>
<cbc:BuildingNumber>111</cbc:BuildingNumber>
<cbc:CityName>Bridgetown</cbc:CityName>
<cbc:PostalZone>ZZ99 1ZZ</cbc:PostalZone>
XQuery update
Page 11 of 20
developerWorks®
ibm.com/developerWorks/
<cac:Country>
<cbc:IdentificationCode>GB</cbc:IdentificationCode>
</cac:Country>
</cac:DeliveryAddress>
<cac:RequestedDeliveryPeriod>
...
</cac:RequestedDeliveryPeriod>
</cac:Delivery>
...
<cac:OrderLine>
<cac:LineItem>...</cac:LineItem>
...
</cac:OrderLine>
...
</Order>
To create the new Order document, the XQuery expression has to replace the values of the
UUID and IssueDate elements with new ones, as well as replace the existing values of the
<cbc:StreetName> and <cbc:BuildingNumber> elements with the correct ones, and delete the
<cbc:BuildingName> element.
FpML derivatives (Novation request response)
Financial products Markup Language (FpML) is an XML notation defined by the International
Swaps and Derivatives Association (ISDA) to describe privately negotiated derivatives (contracts).
In creating FpML, ISDA is acting on behalf of a community of investment banks that make a
market in Over the Counter (OTC) derivatives. (See Resources for more information on ISDA,
FpML.)
XML is well-suited to FpML, as new kinds of derivatives are being created all the time, so a flexible
notation is required. FpML is difficult to represent in other notations such as relational tables,
because FpML today has over 600 XML types, over 1730 XML elements, and over 21 XML
schema files (XSDs).
The FpML XQuery update example in this article (see Figure 6) illustrates producing a response to
a novation request (see Resources). A novation is an agreement to substitute an existing party to
a contract with a new party.
XQuery update
Page 12 of 20
ibm.com/developerWorks/
developerWorks®
Figure 6. FpML novation
Code snippets
Listing 13 shows a novation request:
Listing 13. Excerpt from novation request
<FpML version="4-2"
xmlns=http://www.fpml.org/2005/FpML-4-2
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.fpml.org/2005/FpML-4-2
../../fpml-main-4-2.xsd
http://www.w3.org/2000/09/xmldsig#
xmldsig-core-schema.xsd"
xsi:type="NovationConsentRequest">
...
<novation>
<oldTransaction>
<partyTradeIdentifier>
<partyReference href="abcBank"/>
<tradeId tradeIdScheme=
"http://www.abcbank.com/tradeId/OTC">
TradeABC0001
</tradeId>
</partyTradeIdentifier>
</oldTransaction>
<newTransactionReference>
<partyTradeIdentifier>
<partyReference href="xyzBank"/>
<tradeId tradeIdScheme=
"http://www.xyzbank.com/tradeId/OTC">
TradeXYZ0001
</tradeId>
</partyTradeIdentifier>
</newTransactionReference>
Listing 14 shows the desired novation response.
XQuery update
Page 13 of 20
developerWorks®
ibm.com/developerWorks/
Listing 14. Excerpt from novation reply
<FpML version="4-2"
xmlns=http://www.fpml.org/2005/FpML-4-2
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.fpml.org/2005/FpML-4-2
../../fpml-main-4-2.xsd
http://www.w3.org/2000/09/xmldsig#
xmldsig-core-schema.xsd"
xsi:type="NovationConsentGranted">
...
<novation>
<oldTransactionReference>
<partyTradeIdentifier>
<partyReference href="abcBank"/>
<tradeId tradeIdScheme=
"http://www.abcbank.com/tradeId/OTC">
TradeABC0001
</tradeId>
</partyTradeIdentifier>
</oldTransactionReference>
<newTransactionReference>
<partyTradeIdentifier>
<partyReference href="xyzBank"/>
<tradeId tradeIdScheme=
"http://www.xyzbank.com/tradeId/OTC">
TradeXYZ0001
</tradeId>
</partyTradeIdentifier>
</newTransactionReference>
Listing 15 shows how XQuery update can be used to produce the novation response or
confirmation.
Listing 15. SQL/XML statement to produce FpML novation confirmation
INSERT INTO BUCKET (id, document)
VALUES (33,
XMLQUERY('declare default element namespace "http://www.fpml.org/2005/FpML-4-2";
declare namespace xsi = "http://www.w3.org/2001/XMLSchema-instance";
for
$doc in $f
let
$reference := $doc/FpML/novation/oldTransaction/tradeHeader
/partyTradeIdentifier[last()]
let
$oldReference := <oldTransactionReference>
{$reference}
</oldTransactionReference>
return transform
copy
$c := $doc
modify
(do replace
value of $c/FpML/@xsi:type
with "NovationConsentGranted",
do insert $oldReference
before $c/FpML/novation/newTransactionReference,
do delete $c/FpML/novation/oldTransaction)
return
$c' PASSING (SELECT document
FROM bucket
WHERE id=3) as "f")
)@
Web 2.0 APP — An API for an Atom Store (Updating in memory)
Atom describes two related standards (see Resources for more information):
XQuery update
Page 14 of 20
ibm.com/developerWorks/
developerWorks®
• The Atom Syndication Format is an XML notation to describe the layout of Web feeds. A
feed has many entries. For example, a list of recent news items in a particular category (for
example, sports in the China) could be represented as an atom feed. Each news item would
be represented by an Atom entry.
• Atom Publishing Protocol (APP) is an HTTP protocol for creating and updating Web
resources that are in the Atom format. APP is intended to encourage many resources to have
an Atom representation.
A data collection of stored XML in the Atom notation is called an Atom Store.
Figure 7. Atom scenario
XML is well-suited to Atom because a wide variety of tools and technologies, including human user
interface software, needs to support, exchange, and store Atom feeds (see Figure 7).
The Atom XQuery update example in this article illustrates modifying an Atom feed entry. Listing
16 shows a feed entry:
Listing 16. An Atom entry
<entry xmlns="http://www.w3.org/2005/Atom">
<author>
<name>Lonely John</name>
</author>
<title>Scary Nights</title>
<content type="xhtml" xml:lang="en-US">
<div xmlns="http://www.w3.org/1999/xhtml">
It was a dark and stormy night
</div>
</content>
<published>
2006-07-13T10:59:26-07:00
</published>
</entry>
Listing 17 shows how the entry might look after some updates to modify and augment the entry
with more links:
Listing 17. An updated Atom entry
<entry xmlns="http://www.w3.org/2005/Atom">
XQuery update
Page 15 of 20
developerWorks®
ibm.com/developerWorks/
<author>
<name>John the brave</name>
<email>[email protected]</email>
</author>
<id xmlns="http://www.w3.org/2005/Atom">b8b2332285095249</id>
<summary xmlns="http://www.w3.org/2005/Atom" type="xhtml">
<div xmlns="http://www.w3.org/1999/xhtml">
<a href="http://atomfeeds.com?col=97">
<img src="http://atomfeeds.com?col=97"
alt="Media of type application"/>
</a>
</div>
</summary>
<created xmlns="http://www.w3.org/2005/Atom"
by="[email protected]">2007-09-26T21:15:10.541636Z
</created>
<updated xmlns="http://www.w3.org/2005/Atom"
by="[email protected]">2007-09-26T21:15:10.541636Z
</updated>
<link xmlns="http://www.w3.org/2005/Atom" rel="self"
href="http://atomfeeds.com?col=97"/>
<link xmlns="http://www.w3.org/2005/Atom" rel="edit"
href="http://atomfeeds.com?col=97"/>
<link xmlns="http://www.w3.org/2005/Atom" rel="edit-media"
href="http://atomfeeds.com?col=97&media"/>
<title>Media of type application</title>
<content xmlns="http://www.w3.org/2005/Atom"
type="application/x-www-form-urlencoded"
src="http://atomfeeds.com?col=97"/>
</entry>
Listing 18 shows how XQuery update can be used to implement some aspects of the Atom
Publishing Protocol and produce the modified Atom feed entry:
Listing 18. XQuery update to produce the modified Atom feed entry from the
original entry
XQUERY declare default element namespace 'http://www.w3.org/2005/Atom';
for $doc in db2-fn:sqlquery('select document from bucket where id=2')
let $newTitle := "Media of type application"
let $newAuthor := <author>
<name>John the brave</name>
<email>[email protected]</email>
</author>
let $newId := <id>b8b2332285095249</id>
let $summary := <summary type="xhtml">
<div xmlns="http://www.w3.org/1999/xhtml">
<a href="http://atomfeeds.com?col=97">
<img src="http://atomfeeds.com?col=97"
alt="Media of type application"/></a>
</div>
</summary>
let $newContent := <content type="application/x-www-form-urlencoded"
src="http://atomfeeds.com?col=97"/>
let $created := <created by="[email protected]">2007-09-26T21:15:10.541636Z</created>
let $updated := <updated by="[email protected]">2007-09-26T21:15:10.541636Z</updated>
let $linkSelf := <link rel="self" href="http://atomfeeds.com?col=97"/>
let $linkEdit := <link rel="edit" href="http://atomfeeds.com?col=97"/>
let $linkEditMedia := <link rel="edit-media" href="http://atomfeeds.com?col=97&media"/>
return transform copy $c := $doc
modify (do delete $c/entry/published,
do replace value of $c/entry/title with $newTitle,
do replace $c/entry/author with $newAuthor,
do replace $c/entry/content with $newContent,
XQuery update
Page 16 of 20
ibm.com/developerWorks/
do
do
do
do
do
do
do
return $c@
insert
insert
insert
insert
insert
insert
insert
developerWorks®
$newId after $c/entry/author,
$summary after $c/entry/author,
$created after $c/entry/author,
$updated after $c/entry/author,
$linkSelf after $c/entry/author,
$linkEdit after $c/entry/author,
$linkEditMedia after $c/entry/author)
Some guidelines
A common design strategy when storing XML in databases and handling modification requests
is to leave the original stored XML unchanged and to create a new version of the XML with the
modifications, together with an indication of when the changes were made and when they take
effect, for example, through time-stamping, versioning, or a combination of the two. Some industry
message formats explain how modified data should be handled through explicit versioning, for
example, in health care.
A design approach that incorporates some kind of versioning or time-stamping mirrors closely
what happens in the external world, and makes it easier to relate the processing in the computer
system to real-world conventions and practices. Versioning and time-stamping also make it easier
to handle compliance.
Conclusion
This article showed you how you can use XQuery update to modify XML stored in memory or
in a DB2 pureXML database in the context of four industries: health care, business, financial
derivatives, and information technology.
As it is rare in the real world, particularly where money is involved, to modify information directly,
in general, it is good practice to maintain a history of changes. Storing XML messages and any
new versions of messages derived from the existing stored XML helps support governance and
compliance initiatives.
In this article, you've learned how you can create new versions of stored XML through XQuery
update capabilities, in accordance with the guideline above, or you can perform an in-place
replacement of the stored XML, when it is not necessary to maintain an audit or a different system
is performing the auditing. This article also illustrated how XQuery update can be used to generate
an XML response message derived from an XML request message or how to hide information
using views.
XQuery update
Page 17 of 20
developerWorks®
ibm.com/developerWorks/
Resources
Learn
• Industry Formats and Services with pureXML: Download a great variety of examples, for free!
Each example illustrates how to work with XML-based Industry Formats and pureXML. The
examples show how to register an XML Schema, how to perform validation of XML instance
documents, how to query XML data using XQuery or SQL/XML and much more.
• XQuery Update Specification at W3C: Find the specification of the XML Query Language
(XQuery) Update Facility.
• "Update XML in DB2 9.5" (developerWorks, October 2007): Get an introduction to a new
feature of DB2 pureXML, which is the XQuery Update Facility that allows you to make
persistent changes to XML. Moreover, this article provides useful examples that illustrate the
XQuery Update Facility in DB2 pureXML.
• HL7 in Wikipedia: Get a brief overview to the Standards Developing Organization (SDO)
Health Level 7 (HL7).
• HL7: The main page of the Standards Developing Organization (SDO) Health Level 7 (HL7),
find resources around the standard itself (for example, further link, conference details,
specifications).
• UBL in Wikipedia: Get a brief introduction to the Universal Business Language (UBL).
• UBL: Find more information about the Universal Business Language (UBL) Technical
Committee (TC) at the Organization for the Advancement of Structured Information
Standards (OASIS).
• FpML in Wikipedia: Get a brief introduction to the Financial product Markup Language
(FpML).
• FpML: The main page of the Financial products Markup Language (FpML) offers many
different resources (for example, tools, documentation).
• Atom in Wikipedia: Get a brief introduction to the Atom Syndication Format and the Atom
Publishing Protocol.
• Atom Publishing Protocol: The RFC5023 at IETF describes the Atom Publishing Protocol.
• Health Level 7 (HL7) Clinical Document Architecture (CDA): The original specification of the
Clinical Document Architecture (CDA).
• developerWorks Information Management zone: Learn more about Information Management.
Find technical documentation, how-to articles, education, downloads, product information,
and more.
• Stay current with developerWorks technical events and webcasts.
• Technology bookstore: Browse for books on these and other technical topics.
Get products and technologies
• DB2 Express-C: Download the free version of DB2, which includes the core functionality as
the other Data Servers, such as the pureXML technology. DB2 Express-C is free to develop,
deploy and distribute.
• Build your next development project with IBM trial software, available for download directly
from developerWorks.
XQuery update
Page 18 of 20
ibm.com/developerWorks/
developerWorks®
Discuss
• Participate in the discussion forum for this content.
• Participate in developerWorks blogs and get involved in the developerWorks community.
XQuery update
Page 19 of 20
developerWorks®
ibm.com/developerWorks/
About the authors
Susan Malaika
Susan Malaika is a senior technical staff member in IBM's Information Management
Group (part of IBM Software Group). Her specialties include XML, the Web, and
databases. She has developed standards that support data for grid environments at
the Global Grid Forum. In addition to working as an IBM product software developer,
she has also worked as an Internet specialist, a data analyst, and an application
designer and developer. She has also co-authored a book on the Web and published
articles on transaction processing and XML. She is a member of the IBM Academy of
Technology.
Jan-Eike Michels
Jan-Eike Michels is a software engineer in IBM's Information Management Group
(part of IBM Software Group). He represents IBM on the ANSI/INCITS/H2 and ISO/
JTC1/SC32/WG3 committees responsible for standardizing SQL and SQL/XML.
He also works with the DB2 XML development teams as well as with customers
and business partners who are using XML, assisting them in implementing XML
solutions. He holds an M.S. degree in computer science from the Technical University
of Ilmenau, Germany. You can reach Jan-Eike at [email protected].
Christian Pichler
Christian Pichler is a co-op from the Technical University of Vienna in Austria, where
he is working on his thesis for a double Master's degree in Computer Engineering
and Computer Science with a focus on health care. For IBM, Christian is working on
technologies for storing XML in DB2, and accessing it through Web services, feeds,
and XForms. He is specializing in XML standards for health care.
© Copyright IBM Corporation 2008
(www.ibm.com/legal/copytrade.shtml)
Trademarks
(www.ibm.com/developerworks/ibm/trademarks/)
XQuery update
Page 20 of 20