Extremely pureXML in DB2 10 for z/OS

102 Extremely pureXML in DB2 10 for z/OS

6.3.1 Simple SQL/XML search examples

The SQL/XML queries in this section covers a simple case of our BK_TO_CSTMR_STMT

table with five rows populated in it. The five rows represent five account statements, from

January 2010 through to May 2010.

First, we look at the entire table. Figure 6-1 shows the Optim Development Studio being used

to execute the following statement:

SELECT * FROM BK_TO_CSTMR_STMT

Figure 6-1 SQL query: SELECT * FROM BK_TO_CSTMR_STMT

The query was executed through Optim Development Studio, which includes the following

panes:

 The top pane is the project explorer, where we save our source code such as SQL

statements and stored procedures.

 The bottom left pane shows database connection, in this case, to DB0B.

 The middle top pane shows the SQL statement, which we can run from the action bar.

 The bottom right pane shows the results of the SQL statement.

The table contains the XML documents that we received from MQ, and the two fields that we

stripped out using XMLQUERY and XMLTABLE functions in the stored procedure examples.

Chapter 6. DB2 SQL/XML programming 103

To view the hidden DOCID column too, we can explicitly select the following columns:

 DB2_GENERATED_DOCID_FOR_XML

 MSG_ID

 MSG_CRE_DT_TM

 BK_TO_CSTMR_STMT

Optim Development Studio also provides an XML document viewer. Click any of the

BK_TO_CSTMR_STMT XML documents in the results pane to browse the contents of the

XML document, and drill down to look at specific element and attribute values at any level

within the XML document. The contents of the first XML document (MSG_ID

AAAASESS-FP-STAT001) are illustrated in Figure 6-2.

Figure 6-2 Optim Development Studio XML document viewer: Design view

Figure 6-2 shows that we have expanded the values of several data elements in the GrpHdr

node. It also shows that the statement node in this particular message has four Ntry

elements, each representing a credit or debit transaction on the account, during the period

covered by this statement. We can use this viewer to drill down and examine each element in

the XML document.

104 Extremely pureXML in DB2 10 for z/OS

We can also click the Source tab at the bottom of the pane, and view the Source and Format

to ensure that the source view is formatted for easy viewing. See Figure 6-3.

Figure 6-3 Optim Development Studio XML document viewer: Source view

Conceptually, the information in this XML document is the same as what you see in your

monthly account statement from your own bank, but it looks different in XML format.

Therefore, how easy is it to use SQL/XML to query the document and provide a more

commonly recognizable view of the data?

The XMLTABLE function is designed for transforming elements from an XML document into a

tabular view, similar to your monthly bank statement. The SQL/XML query in Example 6-13

on page 105 retrieves all the debit and credit transactions from the XML document in a

tabular format that looks much more like a traditional bank statement.