Chapter 7. Using XML with Java 143
The numbered steps in Example 7-6 on page 142 are as follows:
1. The parser.parse() method parses the content of the given file as an XML document and
returns a new DOM Document object.
2. This step finds the “MsgId” element, by using the getElementsByTagName() method, and
returns a node list.
3. Because we are expecting only one node in the nodelist, we simply use item(0) to get the
first node in the nodelist, and then use getFirstChild().getNodeValue() method to get
the value of text node.
4. The XML datetime format uses the letter T as a separator indicating that time-of-day (such
as 2010-10-18T17:00:00+01:00) is not compatible with Java datetime format, which
requires a space between day and hour (such as 2010-10-18 17:00:00+01:00). This step
converts it.
5. Call the DSN_XMLVALIDATE(CAST(? AS XML) function to validate the XML document.
6. Set the parameter to the given java.sql.SQLXML object.
7. Execute the insert. The XML document is inserted as binary XML format (data that is in
the Extensible Dynamic Binary XML DB2 Client/Server Binary XML Format), if the data
server supports binary XML data.
7.2.3 Updates of XML columns
You can use the SQL UPDATE statement to update entire documents in an XML column, or
update portions of XML documents using the XMLMODIFY function with a basic XPath
updating expression.
To update the entire XML documents, you can execute a Java statement or execute a
prepareStatement with a setXXX method to set the designated parameter to an XML value.
To update portions of XML documents, use the SQL UPDATE statement with the
XMLMODIFY built-in scalar function. The XMLMODIFY function specifies a basic updating
expression that you can use to insert nodes, delete nodes, replace nodes, or replace the
values of a node in XML documents that are stored in XML columns.
We have inserted a Bank To Customer Statement message, and the statement that reports
on booked entries and balances for a cash account is shown as Figure 7-2 on page 144.
144 Extremely pureXML in DB2 10 for z/OS
Figure 7-2 Bank To Customer Statement example
The first balance (Bal element with the Cd code OPBD) shows that the book balance of the
account at the beginning of the account reporting period is 600000 Swedish krona. There is
one entry (Ntry) in the statement, and the code of CdtDbtInd is DBIT which indicates the
balance is a a debit balance, so the operation is a decrease. The Amount is 200100 Swedish
krona. The second balance (Bal element with the Cd code CLBD) shows the balance of the
account at the end of the pre-agreed account reporting period. It is the sum of the opening
booked balance at the beginning of the period and all entries booked to the account during
the pre-agreed account reporting period, so the amount is 399900 Swedish krona.
To modify or correct the message, add a new entry which is a credit balance increase of
100100 Swedish krona. We perform the following modification:
1. Append a new entry (Ntry) after the first entry, record the credit balance of 100100
Swedish krona.
2. Modify the amount of ClosingBooked (CLBD) balance to 500000 Swedish krona
Chapter 7. Using XML with Java 145
Example 7-7 shows how to modify an XML document, insert nodes, and replace the values of
a node.
Example 7-7 Modifying an XML document
//************************************************************************
// update XML document, add a new entry as the last entry
//************************************************************************
String sql = " UPDATE BK_TO_CSTMR_STMT "+
"SET BK_TO_CSTMR_STMT = XMLMODIFY ( "+
1
"'declare default element namespace " +
"\"urn:iso:std:iso:20022:tech:xsd:camt.053.001.02\"; "+
"insert nodes $newentry/newNtry/Ntry "+
2
"after /Document/BkToCstmrStmt/Stmt/Ntry[fn:last()]', "+ 3
"XMLPARSE(DOCUMENT " +
" '<newNtry xmlns=\"urn:iso:std:iso:20022:tech:xsd:camt.053.001.02\"> "+
" <Ntry> "+
" <Amt Ccy=\"SEK\">100100</Amt> "+
" <CdtDbtInd>CRDT</CdtDbtInd> "+
" <Sts>BOOK</Sts> "+
" <BkTxCd> "+
" <Domn> "+
" <Cd>PAYM</Cd> "+
" <Fmly> "+
" <Cd>0001</Cd> "+
" <SubFmlyCd>0005</SubFmlyCd> "+
" </Fmly> "+
" </Domn> "+
" </BkTxCd> "+
" </Ntry> "+
" </newNtry>') as \"newentry\") "+
"WHERE MSG_ID=? ";
pst = con1.prepareStatement(sql);
pst.setString(1, "AAAASESS-FP-STAT002");
pst.executeUpdate();
//************************************************************************
// update XML document, modify the amount of ClosingBooked(CLBD) balance
//************************************************************************
sql = " UPDATE BK_TO_CSTMR_STMT "+
"SET BK_TO_CSTMR_STMT = XMLMODIFY ( "+
"'declare default element namespace " +
"\"urn:iso:std:iso:20022:tech:xsd:camt.053.001.02\"; "+
" replace value of node " +
4
"/Document/BkToCstmrStmt/Bal[Tp/CdOrPrtry/Cd=\"CLBD\"]/Amt" + 5
" with \"500000\"') "+
"WHERE MSG_ID=? ";
pst = con1.prepareStatement(sql);
pst.setString(1, "AAAASESS-FP-STAT002");
pst.executeUpdate();

Get Extremely pureXML in DB2 10 for z/OS now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.