Extremely pureXML in DB2 10 for z/OS

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();