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
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:
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.