Extremely pureXML in DB2 10 for z/OS

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