Chapter 7. Using XML with Java 147
"WHERE MSG_ID = ?";
pst = con1.prepareStatement(sql);
pst.setString(1, "AAAASESS-FP-STAT002");
rs=pst.executeQuery();
while (rs.next()) {
sqlxml=rs.getSQLXML(1);
actualResults.println(sqlxml.getString());
}
7.2.5 Call stored procedure to shred XML
The BankStmt application continues to shred the BankToCustomerStatement message from
BK_TO_CSTMR_STMT table into a STMT table, which demonstrates a simple hybrid design,
and how to populate using the INSERT from SELECT with XMLTABLE. We perform the
shredding in a native stored procedure. Our java application retrieves the XML message and
calls the stored procedure with the XML value as parameter.
Example 7-9 shows the definition for the STMT table, each row contains one statement (Stmt
building block) of the BankToCustomerStatement message. For example "STMT_ID" column
corresponds to the 'Id' node under Stmt element, and "STMT_XML" column will be the sub
document (the Stmt building block) of the BankToCustomerStatement message.
Example 7-9 DDL for STMT table
CREATE TABLE "STMT" (
"STMT_ID" VARCHAR(35) NOT NULL ,
"MSG_ID" VARCHAR(35) ,
"MSG_CRE_DT_TM" TIMESTAMP ,
"ELECTRNC_SEQ_NB" BIGINT ,
"LGL_SEQ_NB" BIGINT ,
"CRE_DT_TM" TIMESTAMP NOT NULL ,
"FR_DT_TM" TIMESTAMP ,
"TO_DT_TM" TIMESTAMP ,
"RPTG_SRC_CD" CHAR(4) NOT NULL ,
"RPTG_SRC_PRTRY" VARCHAR(35) NOT NULL ,
"ADDTL_INF" VARCHAR(140) NOT NULL,
"STMT_XML" XML NOT NULL )
IN DATABASE XMLR5DB ;
Example 7-10 shows our code for creating the SQL procedure in Java.
Example 7-10 Creating a SQLstored procedure
stmt.executeUpdate("" +
"CREATE PROCEDURE MYSP(IN parm1 XML,OUT parm2 XML)" +
1
"LANGUAGE SQL " +
"APPLICATION ENCODING SCHEME UNICODE" +
"DISABLE DEBUG MODE " +
"BEGIN " +
"DECLARE var1 XML; " +
"SET var1 = parm1; " +
"INSERT INTO STMT( " +
2
" STMT_ID, " +
" MSG_ID, " +
148 Extremely pureXML in DB2 10 for z/OS
" MSG_CRE_DT_TM, " +
" ELECTRNC_SEQ_NB, " +
" LGL_SEQ_NB, " +
" CRE_DT_TM, " +
" FR_DT_TM, " +
" TO_DT_TM, " +
" RPTG_SRC_CD, " +
" RPTG_SRC_PRTRY, " +
" ADDTL_INF, " +
" STMT_XML " +
") " +
"SELECT T.STMT_ID, " +
" T.MSG_ID, " +
" T.MSG_CRE_DT_TM," +
" T.ELECTRNC_SEQ_NB," +
" T.LGL_SEQ_NB, " +
" T.CRE_DT_TM, " +
" T.FR_DT_TM, " +
" T.TO_DT_TM, " +
" COALESCE(T.RPTG_SRC_CD,'') AS PRTG_SRC_CD," +
3
" COALESCE(T.RPTG_SRC_PRTRY,'') AS RPTG_SRC_PRTRY," +
" COALESCE(T.ADDTL_INF,'') AS ADDTL_INF," +
" XMLDOCUMENT(T.STMT_XML)" +
4
"FROM XMLTABLE( " +
" XMLNAMESPACES(DEFAULT " +
" 'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02')," +
" '$var1/Document/BkToCstmrStmt/Stmt'" +
" PASSING var1 as \"var1\"" +
" COLUMNS STMT_ID VARCHAR(35) PATH 'Id'," +
" MSG_ID VARCHAR(35) PATH '../GrpHdr/MsgId'," +
" MSG_CRE_DT_TM TIMESTAMP PATH '../GrpHdr/CreDtTm'," +
" ELECTRNC_SEQ_NB BIGINT PATH 'ElctrncSeqNb'," +
" LGL_SEQ_NB BIGINT PATH 'LglSeqNb'," +
" CRE_DT_TM TIMESTAMP PATH 'CreDtTm'," +
" FR_DT_TM TIMESTAMP PATH 'FrToDt/FrDtTm'," +
" TO_DT_TM TIMESTAMP PATH 'FrToDt/ToDtTm'," +
" RPTG_SRC_CD CHAR(4) PATH 'RptgSrc/Cd'," +
" RPTG_SRC_PRTRY VARCHAR(35) PATH 'RptgSrc/Prtry'," +
" ADDTL_INF VARCHAR(144) PATH 'AddtlStmtInf'," +
" STMT_XML XML PATH '.'" +
" )AS T; " +
//************************************************************************
// For the output parameter, You can do some more XML operations,
// here we just simply set the output parameter the same as input
//************************************************************************
"SET parm2 = var1; " +
"END
);
Chapter 7. Using XML with Java 149
The numbered steps in Example 7-10 on page 147 are as follows:
1. In DB2 10, we can use XML as the data type for a parameter of a native SQL procedure,
and an XML SQL variable declared within the procedure.
2. We use INSERT from SELECT with the XMLTABLE function for the shredding. If the
BankToCustomerStatement message includes multiple statements, we are able to divide
one message into multiple statements (one rows for each statement) in the STMT table by
using the XMLTABLE function.
3. To insert a nullable value into a NOT NULL column, we use the COALESCE function.
4. When inserting the XML value from XMLTABLE, we use the XMLDOCUMENT function to
return a constructed XML value.
To call the stored procedures in Java, invoke methods in the CallableStatement class. The
following basic steps call stored procedures, using standard CallableStatement methods:
1. Invoke the Connection.prepareCall method with the CALL statement as its argument to
create a CallableStatement object.
2. Invoke the CallableStatement.setXXX methods to pass values to the input parameters
(parameters that are defined as IN or INOUT in the CREATE PROCEDURE statement).
3. Invoke the CallableStatement.registerOutParameter method to register parameters that
are defined as OUT in the CREATE PROCEDURE statement with specific data types.
4. Invoke the CallableStatement.executeXXX methods to call the stored procedure.
5. Invoke the CallableStatement.getXXX methods to retrieve values from the OUT
parameters or INOUT parameters.
The sample code to prepare, call the stored procedure, and retrieve the XML data from OUT
parameters is shown in Example 7-11.
Example 7-11 Handling the SQL stored procedure
String sql = "CALL MYSP(?,?)";
CallableStatement cstmt = con1.prepareCall(sql);
//initialize the parms
SQLXML xml1 = con1.createSQLXML();
stmt = con1.createStatement();
sql = "SELECT BK_TO_CSTMR_STMT FROM BK_TO_CSTMR_STMT " +
"WHERE MSG_ID='AAAASESS-FP-STAT003-4'";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next())
xml1=rs.getSQLXML(1);
actualResults.println("value of input XML:");
actualResults.println(xml1.getString());
cstmt.setSQLXML(1, xml1);
cstmt.registerOutParameter(2, java.sql.Types.SQLXML);
cstmt.execute();
xml1 = cstmt.getSQLXML(2);
actualResults.println("value of output XML:");
actualResults.println(xml1.getString());
cstmt.close();

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.