db2 9 application developer certification exam 733 prep...

DB2 9 Application Developer Certification Exam733 prep, Part 3: XML data manipulationStore and retrieve XML through your application

Skill Level: Intermediate

Donald E. Payne ([email protected])Advisory IT SpecialistIBM

16 Mar 2007

Learn how IBM® DB2® parses XML, handles whitespace, and serializes XML, and seehow document encoding and client data type affect parsing and serialization. Learn,also, how DB2 validates XML against an XML schema as well as how to use SQL/XMLfunctions to "shred" XML to relational data, assemble relational into XML, and publishXML as relational data.

Section 1. Before you start

About this series

The IBM Certified Application Developer certification confirms to others that you arean intermediate- or advanced-level IBM DB2® for Linux®, UNIX®, and Windows®application developer and shows that you have strong skills in all commonprogramming tasks as well as embedded SQL programming, ODBC/CLIprogramming, .NET programming, or Java™ programming.

This series of nine free tutorials is designed to help you prepare for the DB2 9Application Development for Linux, UNIX, and Windows certification exam (Exam733). Each tutorial includes a link to a free DB2 9 for Linux, UNIX, and Windows trialdownload. These tutorials provide a solid base for each section of the exam.

XML data manipulation© Copyright IBM Corporation 1994, 2007. All rights reserved. of 53

mailto:[email protected]

http://www.ibm.com/developerworks/offers/lp/db2cert/db2-cert733.html?S_TACT=105AGX11&S_CMP=db2cert

http://www.ibm.com/legal/copytrade.shtml

However, you should not rely on these tutorials as your only preparation for theexam.

What is this tutorial about?

This tutorial teaches you how DB2 and a DB2 client application manipulate XMLdata. You'll learn:

• How DB2 handles whitespace on input

• How DB2 determines the encoding of an XML document on input andoutput

• How DB2 can validate XML on input

• How to execute an XQuery or XPath and identify its results

• How to decompose, or shred, XML input to relational rows

• How to publish relational data as XML

• How to publish XML data as relational

This is the third in a series of nine tutorials that is designed to help you prepare forthe DB2 9 Application Developer Certification exam (Exam 733). The material in thistutorial covers the objectives in Section 3 of the exam, entitled "XML datamanipulation."

Objectives

After completing this tutorial, you should be able to write applications that store andretrieve XML.

Prerequisites

This tutorial is written for DB2 developers who are familiar with the followingconcepts:

• XML, including:

• Elements

• Attributes

developerWorks® ibm.com/developerWorks

XML data manipulation of 53 © Copyright IBM Corporation 1994, 2007. All rights reserved.


• Documents

• Well-formed documents

• XML declarations

• Namespaces

• XPath, including the XML Data Model (XDM):

• Nodes

• Atomic values

• Items

• Sequences

• Axis: Parent, child, descendant, and so on

• Steps

• Wild cards

• Predicates

• XQuery:

• The db2-fn functions xmlcolumn(), sqlquery()

• FLWOR expression: for, let, where, order by, return

• prolog

• XML Schema, validation, and namespaces

• The concepts of character encoding and Unicode

For the code examples, you should be familiar with:

• The programming language

• Any interfaces or libraries used, such as CLI and JDBC

• The data types that the language supports and how they map to DB2SQL types

See the Resources section for links to this information.

System requirements

ibm.com/developerWorks developerWorks®



To complete this tutorial, you need the following:

• A computer running Linux, UNIX, or Windows, with DB2 9 FixPack 1installed

• An editor, such as Rational Application Developer, Windows Notepad, orvi (see the Resources section of this tutorial for more information)

• A compiler to run code examples. The DB2 Information Center page onsupported programming languages and compilers for databaseapplication development links to pages listing supported compilers in allsupported languages:

• Supported C compilers: See the Resources section, subsection "Getproducts and technologies."

• For the Java language, DB2 on Windows comes with a Java 2 SDK,Version 5, including the compiler javac and runtime environment(JRE), under SQLLIB\java\jdk. (On most platforms, DB2 9supports Java 2 SDK, Versions 1.4.2 through 5. See DB2 InformationOnline, "Supported Java application development software," fordetails.) The JDK includes the JRE under the SQLLIB\java\jdk\jredirectory. Your PATH environment needs to include the bin directoriesunder jdk; CLASSPATH needs to include some JAR and ZIP filesunder SQLLIB\java.

• A Web browser is handy for viewing an XML file, checking that it iswell-formed, and finding mistakes

Running the examples

If you run the SQL examples from this tutorial in the DB2 Command Line Processor(CLP) db2, see the section entitled "DB2 Command Line Processor (CLP) behaviorand options."

Section 2. Processing whitespace in XML parsing andvalidation



http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.apdv.gs.doc/doc/r0023434.htm



http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.apdv.java.doc/doc/c0023597.htm


XML changes upon parsing (input)

When DB2 or another XML processor parses incoming XML -- that is, converts itfrom characters to an internal structure -- it makes certain changes to the XML in theprocess, as specified in the XML standard. Some of these changes matter if you areconcerned about the storage or memory requirements of XML in DB2 or in the clientapplication. It might also matter if you are concerned with text nodes that containonly whitespace, the appearance of the XML upon retrieval and serialization back tocharacters, or digital signatures.

The parser processes XML input as follows:

1. The parser determines the encoding. If the encoding of the source is notequivalent to DB2's internal encoding of UTF-8, the source characters aretranscoded, or converted from the source encoding to DB2's. See thesection "XML Encoding," subsection "Which encoding?" for moreinformation.

2. End-of-line characters are the carriage return (CR, hex code 0xD) and linefeed (LF, hex code 0xA). Different operating systems (DOS/Windows,UNIX/Linux, Mac OS, or OS X) follow different conventions for endinglines. These characters are normalized, or converted to a single form, LFor 0xA.

3. The parser checks for a well-formed document

4. XML entities include predefined entities such as & and " andentities defined in a DTD. Entity references in the document areexpanded or converted to the value of the entity. (The expandedcharacter codes of the predefined entities are smaller than the originalentity or reference.) Any internal DTD is discarded.

5. Whitespace is a catch-all term for certain non-printing characters,commonly:

• space (hex code 0x20)

• tab (hex 0x9)

• End of line characters (above)Boundary whitespace may be stripped based on the rules you'll readabout in the "Parsing and boundary whitespace" section below.




6. XML character references such as € (€, the euro symbol) areexpanded or converted to the actual character code in UTF-8. As withpredefined entity references, the expanded character codes are usuallysmaller than the original reference.

7. If validating, ignorable whitespace is stripped. See the section entitled"Validation and ignorable whitespace" below for more information.

XML comments () are not stripped.

See the Downloads section for sample code that demonstrates how parsing andvalidation change whitespace.

Tip: The DB2 CLP options -n and -q can affect whitespace in string literals beforethey get to DB2. See the section entitled "DB2 Command Line Processor (CLP)behavior and options" for more information.

Parsing and boundary whitespace

When you create XML, it often includes boundary whitespace to make it "pretty" andmore readable. Boundary whitespace in an XML document is text containing onlywhitespace at the boundary between two XML tags, with no non-whitespacecharacters.

Listing 1. Sample XML document for whitespace processing

<customerinfo xmlns="http://posample.org" Cid=' 1018 ' ><name> </name><addr country=" Canadaca " xml:space="preserve">

<street> </street><city> </city><prov-state> </prov-state><pcode-zip> </pcode-zip>

</addr><phone type=" "> 416-555-1358 </phone>

</customerinfo>

In Listing 1, there is boundary whitespace between each tag and the following tag.For example, there's whitespace:

• Between the starting tag <customerinfo Cid=' 1018 '> and thestarting tag <name>

• Between the starting tag </name> and the ending tag </name>

• Between the ending tag </phone> and the ending tag</customerinfo>

If parsing does not strip this whitespace, a parsed "pretty" document will have manymore elements with mixed content -- a mixture of child elements and text nodes --and many text nodes with nothing but whitespace.

An attribute value is not boundary whitespace, even if it's all whitespace, such astype. In Listing 1, the whitespace around the number 1018, the words "Canada ca"and the phone number "516-555-1358" may be bad data, but it is not boundarywhitespace.

Here are the rules for stripping or preserving boundary whitespace, in descendingorder of priority:

1. If the XML contains an element with the attributexml:space="preserve", the parser preserves boundary whitespace inthat element and any child elements

2. If you insert XML with an explicit XMLPARSE() and specify either thePRESERVE WHITESPACE or STRIP WHITESPACE option, the parserpreserves or strips boundary whitespace as directed

3. If you insert XML with explicit XMLPARSE() and do not specify either thePRESERVE WHITESPACE or STRIP WHITESPACE option, then boundarywhitespace is stripped because that is the default for XMLPARSE()

4. An implicit parse occurs when you insert XML as a string literal, hostvariable, or parameter marker without XMLPARSE() or XMLVALIDATE().(XMLVALIDATE() performs a validating parse, which is effectively explicitand ignores this rule.) In an implicit parse in a CLI application:

• You may issue the command SET CURRENT IMPLICIT XMLPARSEOPTION = 'value' during your session, where value is eitherPRESERVE WHITESPACE or STRIP WHITESPACE. If you do, thenthe parser preserves or strips boundary whitespace accordingly.

• Otherwise, if you modify your db2cli.ini file to include the lineCurrentImplicitXMLParseOption="parse-option"

in a [section] named after your database ([database-name]), thenthe parser preserves or strips boundary whitespace for that databaseaccordingly. parse-option is either PRESERVE WHITESPACE or




STRIP WHITESPACE. For example:[sample]CurrentImplicitXMLParseOption="PRESERVE WHITESPACE"

The db2cli.ini file is located in %DBPATH%. The section name,parameter name, and value are not case-sensitive. You can add thisentry by either editing the file directly, or by the DB2 statement:

UPDATE CLI CONFIGURATION FOR SECTION section-nameUSING CurrentImplicitXMLParseOption '"parse-option"';

5. The parser strips boundary whitespace

Listing 2. Example of Rule 5: Insert XML with empty element, implicit parse,strip whitespace

connect to sampleinsert into catalog values ('emptyelement', '<a> </a>')DB20000I The SQL command completed successfully.

select catlog from catalog where name = 'emptyelement'<a/>

Listing 3. Example of Rule 2: Insert XML with empty element, explicit parse,preserve boundary whitespace

insert into catalog values ('preservespace', xmlparse(document '<a> </a>'preserve whitespace))DB20000I The SQL command completed successfully.

xquery db2-fn:sqlquery('select catlog from catalog wherename = ''preservespace'' ')/a<a> </a>

Validation and ignorable whitespace

When you input XML with XMLVALIDATE(), DB2 parses the document, thenvalidates the parsed document against an XML Schema Definition (XSD). (In somecases, DB2 can parse and validate in one step with a validating parser instead of thedefault nonvalidating parser. But the processing described in "Parsing and boundarywhitespace" still occurs first.) Validation always removes ignorable whitespace,which is whitespace that meets one of the following criteria:

• An element-only complex type is an element defined in the XSD to have




only child elements and no text nodes. Whitespace between childelements in an element-only complex is ignorable. This kind of ignorablewhitespace is also boundary whitespace, which can make the apparentbehavior of parsing and validation confusing.

• If a simple element (containing only a text node) or an attribute is definedin the XSD as a non-string type (as xs:integer, for example), thenwhitespace in that element or attribute value is ignorable

If validation did not ignore this whitespace, a "pretty" document would fail validationbecause it contains text nodes where none are allowed and contains non-numericcharacters in numeric values. Whitespace that is part of a defined text node underan element or part of the value of an attribute of type xs:string is not ignorableand is not stripped.

For example, take another look at Listing 1 above. According to the XSD, the onlystring types are:

• Text nodes under the elements name, street, city, prov-state,pcode-zip, and phone

• The attributes country and type

There is ignorable whitespace in the following places:

• Between one starting tag and an immediately following starting tag -- forexample, between <customerinfo Cid=' 1000 '> and <name>

• Between one ending tag and an immediately following starting tag -- forexample, between </name> and <addr ...>

• Between one ending tag and an immediately following ending tag -- forexample, between </phone> and </customerinfo>)

• The XSD defines attribute Cid as an integer:<xs:attribute name="Cid" type="xs:integer" />

So the whitespace around "1000" is ignorable and will be stripped.

Whitespace in the elements name, street, city, prov-state, pcode-zip, andphone is not ignorable.

Tips:




• Some of the sample scripts you can find atSQLLIB\samples\xml\db2sample_xml.db2 in your DB2 install insert XMLlike this:

XMLVALIDATE( XMLPARSE ( DOCUMENT '...' PRESERVE WHITESPACE ) ...)

For example:

INSERT INTO CUSTOMER ( Cid, Info ) VALUES (1000,XMLVALIDATE( XMLPARSE ( DOCUMENT'<customerinfo xmlns="http://posample.org" Cid=''1000''>...'PRESERVE WHITESPACE ) ...)

XMLPARSE() preserves boundary whitespace; but XMLVALIDATE()strips ignorable whitespace.

• The DB2 CLP can strip whitespace in string literals, including XML, beforesending to DB2. See the section of this tutorial entitled "DB2 CommandLine Processor (CLP) behavior and options" for more information.

• The DB2 CLP is not a CLI application, so the CLI options do not apply toit.

Section 3. XML serialization

XML changes upon output

When DB2 serializes XML, or converts it from its internal structure back tocharacters, as with parsing, the XML standard specifies several changes to the data,so it may look different from what was input. The purpose and effect of thesechanges is to produce a valid XML document-string that can in turn be parsed again.

• If the target encoding does not equal DB2's internal encoding of UTF-8,the UTF-8 characters are transcoded to the target. Characters that werecharacter references on input remain as the actual character on output;they are not re-encoded as a reference. Characters not in the targetcharacter set are lost, replaced with a character such as "?". See thesection of this tutorial entitled "XML encoding" for more information.




• An XML declaration is generated if any of the following is true:

• You serialize explicitly with XMLSERIALIZE(XML-expressionINCLUDING XMLDECLARATION)

• The client is a CLI or embedded SQL (ESQL) application

• The client is a Java or .NET application, using the DB2 Driver forJDBC and SQLJ, that retrieves XML into a DB2Xml object using thecom.ibm.db2.jcc.DB2Xml class.

• A Byte Order Mark (BOM) is generated if the target encoding is UTF-16.

• The ampersand (&), less than (<), and greater than (>) characters arereplaced with their XML entities (&, <, and >, respectively).

• Within attributes, double quotes (") are replaced with their XML entity(").

• Within attribute values, certain whitespace characters -- such as CR, LF,and tab -- that were not normalized out of the XML upon input arereplaced with their numeric reference, &#n;, where n is the hex code.

• Because internal DTDs are discarded upon input, characters that wereexpanded from an entity defined in a DTD are not re-encoded as an entityreference, but remain in their expanded form.

• An empty element is one with no content -- that is, with no text or othernodes between its starting and ending tags. When serialized upon output,it will appear as a single empty element tag, <tag-name/>.

Tip: the DB2 CLP option -i "pretty-prints" the output of an XQuery, adding linebreaks and indentation. The option -d generates an XML declaration at the head ofthe document. See the section of this tutorial entitled "DB2 Command LineProcessor (CLP) behavior and options" for more information.

Section 4. XML encoding




Character encoding

Historically, the terms character set, character encoding, and code page have allhad a similar meaning: a set of characters and a set of binary codes, where eachcode represents a character. (Code page is a term from IBM for a for a character seton a mainframe or IBM PC.) The official character set names are maintained by theInternet Assigned Numbers Authority (IANA); see the Resources section for moreinformation.

Legacy character encodings

Some common legacy character sets in the Western world are US-ASCII, EBCDIC,code page 437, code page 1252, 8859-1, 1208, and Latin-1. These all encode onecharacter as one byte.

• Code page 437: On Microsoft Windows in the USA, the default codepage is 437; the IANA official encoding name is ibm-437. It has a fewnon-English characters (for example, the "ae" ligature, lower-case vowelswith accents, some Greek letters, French quotes ("<<" and ">>"), and 48graphic characters for shading and boxes (a legacy of DOS andcharacter-based programs).

• Code page 1252: DB2 has two items in its Start menu group underCommand Line Tools: the Command Line Processor and CommandWindow. Both start off by running a program db2clpcp.exe, whichchanges the code page from the default 437 to (again, in the USA) 1252.Code page 1252, which Microsoft also calls "Latin-I", has the "ae" ligature,upper- and lower-case accented characters, and the euro symbol (hex0x80), but no Greek letters.

• Code page 1208: When you CREATE DATABASE USING CODESETUTF-8, the corresponding database code page is 1208:

db2 get db cfg for sample | grep "code"Database code page = 1208Database code set = UTF-8Database country/region code = 1

• Character set iso-8859-1: This character set, also called Latin-1, is




common in English or Western European Web pages. It has the "ae"ligature, upper- and lower-case accented Latin letters, no Greek letters,and no euro sign.

See Resources for links to definitions of these and other character sets.

Unicode

Any single legacy encoding is limiting because it can represent text in only a smallset of languages. Managing multiple encodings is a headache for many reasons, notleast because most applications and databases were designed to handle only one.Unicode was invented to solve this problem. It is a single character set thatrepresents all characters in virtually all languages in use, with room to grow.

With Unicode, the simple idea of a character set was refined into four concepts:

• A character repertoire is merely the set of all characters that can beencoded; it does not specify the encoding.

• A coded character set is a set of unique integers to represent some or allof the characters in a character repertoire, one number (or code point) toone character. Also known as character set, charset, or code set. It issynonymous with code page. Unicode code points are in the range of 0 to0x10FFFF (about 1.1 million).

• Originally, Unicode defined a repertoire of fewer than 64,000 characters,so it could specify a coded character set with 16-bit (2-byte) code points.With the addition of more alphabets, the character repertoire exceeded64,000. Another issue was that many computer systems (notablyprograms written in C) that process US-ASCII or others use a null or zerobyte (\0 in C) to mark the end of a character string. But encoding Unicodecode points directly as 16-bit or larger numbers would include some nullbytes, which would break these programs.

So Unicode introduced a new concept, the character encoding form(CEF), often shortened to character encoding or simply encoding. A CEFis a mapping from the code points in the coded character set to a differentset of integers (or code units) in which the characters will actually beencoded. The code unit is the smallest unit used for encoding characters.A single character may be represented by one or more code units,allowing more characters to be encoded.




So the code points are merely for reference, so you can say "thatcharacter." The actual bytes in your Java String, C char[], or file maybe different, depending on your character encoding form. Some Unicodecharacter encoding forms are UTF-8, UTF-16, and UTF-32. The numbers8, 16, and 32 denote the number of bits in the code unit.

• UTF-8 (which DB2 uses) uses 1 to 4 code units (bytes, in this case)per character. This is important when sizing [VAR]CHAR columns in aUTF-8 database: one character may take up 1 to 4 bytes. The UTF-8character encoding form does not contain any null bytes, which ishandy for C.

• In the UTF-16 character encoding form (which the Java languageuses), the most commonly-used code points are below 64K, and theUTF-16 character code simply equals the code point. Higher codepoints require two code units.

• UTF-32 is the only fixed-size UTF, which simplifies some processingat the cost of increased space. The code unit always equals the codepoint.

Since the Unicode standard restricts legal code points such that all theUTF encodings are able to represent any code point, transcoding amongUTFs is guaranteed without loss.

Unicode code points are often written in documentation as U+hhhh ,where hhhh represents 4 hexadecimal digits for the 2-byte code point;for code points above 64K, use this syntax twice for 4 bytes. Note that thissyntax is used in documentation, but XML and programming languageshave their own syntax for specifying Unicode literals; see the sectionentitled "Characters in code" below for more information.

• A character encoding scheme tells how to order the bytes in a code unit inthe byte stream representing characters. UTF-16 and UTF-32 provideways to specify the byte ordering, big-endian (default) or little-endian. ForUTF-8, a code unit is 1 byte, so there is no ordering -- in other words, theordering is always the same regardless of the endian order of thecomputer.

Which encoding?

Anything that processes character data needs to know the character set, characterencoding form, and character encoding scheme of that data.




External encoding

Outside of XML, text has only external encoding. External encoding is defined by thecode page or locale in the environment; the application; the variable type; or anencoding specified in a function or method (such as the Java methodString.getBytes(String encoding)).

To determine the database code page under which a database has been created,run:db2 get db cfg for database-name

and check the value for the "Database code page" parameter.

On Windows, some aspects of encoding depend on the installed version ofoperating system. The Control Panel applet "Regional and Language Options" letsyou change some aspects of your locale and keyboard. You can see what your codepage is by opening a command window and typing the command CHCP.

On Linux and UNIX, the active environment is determined from the locale setting,which includes information about language, territory, and code set. To determine theactive code page, run:locale

Internal encoding

XML data can also have internal encoding. Internal encoding is internal to the XMLdocument. There are two kinds:

• Encoding attribute

• Byte Order Mark (BOM)

The encoding attribute to the XML declaration, at the top of the document, specifiesan official IANA encoding name:

<?xml version="1.0" encoding="UTF-8" ?>

A BOM is a distinctive series of bytes at the very start of the file indicating a Unicodeencoding. To read the XML declaration, an XML parser needs to know or guess theencoding. But it can read the BOM unambiguously.

Table 1. Byte Order Mark for Unicode encodings




BOM type BOM value Encoding

UTF-8 X'EFBBBF' UTF-8

UTF-16 Big Endian X'FEFF' UTF-16

UTF-16 Little Endian X'FFFE' UTF-16

UTF-32 Big Endian X'0000FEFF' UTF-32

UTF-32 Little Endian X'FFFE0000' UTF-32

Inconsistent encoding

If the actual encoding, external encoding, or internal encodings (BOM or XMLdeclaration) of an XML document do not agree, then the document is unreadable.One exception is when the external encoding is Unicode (for example, a JavaString in UTF-16): any internal encoding is ignored. A common problem occurswhen a process that is not XML aware transcodes (that is, changes actual encoding)or otherwise changes a document without being aware of the internal encoding.Some processing of strings in the Java language, CLI, and embedded SQLapplications can transcode without changing internal encoding. See the sectionbelow entitled "Recommendations" on how to avoid this.

Characters in code

In Windows, to enter text in a different language at your keyboard, you can changeyour input language and keyboard layout in the Windows Control Panel, under"Regional and Language Options"; click the Details button on the Languages tab.You can add multiple languages and keyboards, and switch among them with a hotkey. (Sorry, there's no Unicode "language" or keyboard -- it would have to be prettybig!)

The Windows Notepad utility can save files with an Encoding of "ANSI" (which iswindows-1252 or similar), "Unicode" (UTF-16 little endian, the endian order of Inteland AMD CPUs), "Unicode big endian" (the default UTF-16), and UTF-8. For theUTFs, Notepad prepends the file with a BOM (but no XML declaration).

In a Java String literal, which is in Unicode, use an escape sequence: \udddd ,where dddd represents the 4 hex digits for the Unicode code point.

In XML and HTML, you may use an XML numeric reference (or character reference)for a code point: &#n , where n is a decimal number for the Unicode code point.For example, the decimal reference for the euro currency symbol is €. Youmay instead specify the code point as a hexadecimal number by preceding thenumber with x: &#xn . For example, the euro would be €. A hex numeric




reference is often more convenient, as code points are often specified in hex. Fornumeric references, leading zeros may be omitted. Each character must be escapedor referenced separately: for example, "!"" is "!"" (exclamation mark,double quote), while "™" is "™" (trademark).

Table 2 illustrates a few sample characters, with their Unicode code point andcharacter code in the UTFs and two legacy Western code pages.

Encoding in the Java language

Java Strings (and identifiers such as class and method names) are encoded inUTF-16; but your source file is assumed to be in your current code page, notUTF-16. When compiling a Java program with javac, the compiler converts anyString literals from the encoding of the source file into UTF-16. To specify that thesource file is in another encoding, use the javac option -encodingcode-set-name . This allows you to use a different encoding in your identifiersand literal strings.

DB2 9 provides a helper class, com.ibm.db2.jcc.DB2Xml, that helps transferand convert data between the database and the Java application. It is more XMLaware; for example, some methods can convert the internal encoding of a serializedXML document along with the actual encoding, so they remain consistent.

Table 3. JDBC getter methods: Return type, encoding, declarationMethod Return type Encoding Adds XML declaration

with encodingattribute?

getDB2String() String UTF-16 No

getDB2XmlString() String ISO-10646-UCS-2 Yes

getDB2Bytes() byte[ ] UTF-8 No

getDB2XmlBytes(StringtargetEncoding)

byte[ ] As specified Yes

getDB2AsciiStream()InputStream ASCII No

getDB2XmlAsciiStream()InputStream ASCII Yes

getDB2CharacterStream()java.io.Reader UTF-16 No

getDB2XmlCharacterStream()java.io.Reader ISO-10646-UCS-2 Yes

getDB2BinaryStream()InputStream UTF-8 No

getDB2XmlBinaryStream(StringtargetEncoding)

InputStream As specified Yes



sidefile.html


DB2 9 comes with 2 JDBC drivers that can connect to DB2 in three different ways(DB2 9 does not support the JDBC Type 3 driver):

• DB2 Driver for JDBC and SQLJ, a universal JDBC driver, which canconnect as:

• A JDBC Type 4 driver

• A JDBC Type 2 driver

• DB2 JDBC Type 2 Driver (a legacy driver)

It is recommended that you use the universal DB2 driver for JDBC and SQLJ. Thelegacy Type 2 driver has been deprecated since DB2, Version 8.2. The legacy Type2 JDBC driver does not work with the DB2 class com.ibm.db2.jcc.DB2Xml, andit processes XML differently in some JDBC methods. For example, get methods (forexample, ResultSet.getBinaryStream(column),ResultSet.getCharacterStream(column),ResultSet.getString(column)) on a SELECT of an XML column (withoutexplicit XMLSERIALIZE()) add a BOM and XML declaration to serialized XML.Either this internal encoding (UTF-16) does not match the actual encoding, or theBOM is corrupted, making the document unusable.

See Table 4 for more information on JDBC driver versions.

For any type of JDBC connectivity, if you wish to connect as a user other than thecurrent one, you must specify the desired username and password, either in theconnection URL or in the Properties argument to getConnection().

DB2 provides sample JDBC applications in IBM\SQLLIB\samples\xml\java\jdbc,such as XmlInsert.java. If you use them to experiment, be aware that by default, theyconnect to DB2 with the legacy Type 2 driver. (See the helper class Db in the fileUtil.java.) To connect with the Type 4 driver, you must specify command-linearguments for server, port, user, and password. To see a syntax message, run theclass with the argument -help:

prog_name -u2 [dbAlias] [userId passwd] (use universal JDBC type 2 driver)prog_name [dbAlias] server portNum userId passwd (use universal JDBC type 4 driver)

For example:

java -cp ".;%CLASSPATH%" XmlInsert sample myhost 50000 myuser mypasswd

See the Downloads section for sample code that demonstrates how DB2, JDBC,and the DB2Xml class handle encoding.



sidefile2.html


Encoding in CLI

When you insert XML into DB2 and bind a variable to an XML column withSQLBindParameter(), CLI determines the encoding of the XML according to theinput rules in Table 5 below. If the encoding is determined by application code page,but the document also has an internal encoding (a BOM or XML declaration), theexternal and internal encodings must match. When you select XML out of DB2 intoan ESQL host variable, the data is encoded according to Table 5, with an internalencoding (an XML declaration with the encoding attribute).

Table 5. CLI typesData type Encoding assumed on input Encoding produced on output

SQL_C_CHAR Application's code page Same as input

SQL_C_DBCHAR Application's code page Same as input

SQL_C_WCHAR UCS-2 Same as input

SQL_C_BINARY Reads internal encoding UTF-8

Encoding in embedded SQL

When you insert XML into DB2 from an embedded SQL (ESQL) host variable, theencoding of the XML is determined according to the input rules in Table 6. If theencoding is determined by application code page, but the document also has aninternal encoding (BOM or XML declaration), the external and internal encodingsmust match. When you select XML out of DB2 into an ESQL host variable, the datais encoded according to Table 6, with an internal encoding (an XML declaration withthe encoding attribute).

Table 6. ESQL typesSQL TYPE IS Encoding assumed on input Encoding produced on output

XML AS CLOB, XML ASCLOB_FILE

Application's mixed code page Same as input

XML AS DBCLOB, XML ASDBCLOB_FILE

Application's graphic code page Same as input

XML AS BLOB, XML ASBLOB_FILE

Reads internal encoding (Note1)

UTF-8

A simple type like char[] Application's mixed code page(Note 2)

Same as input




Note 1: If a BLOB-type variable does not have any internal encoding, DB2 assumesUTF-8.

Note 2: To insert XML from a simple type like char, cast it to XML with the SQLfunction XMLCAST(? AS XML).

Recommendations

• Move string literals such as prompts and error messages outside yoursource code and into some kind of properties or initialization file or into adatabase table. This will make it easier to translate your application intoother languages. You may have to hard code one or two error messagesinto your applications, in case the application cannot open that messagefile or select from that message table.

• Avoid transcoding between non-Unicode character sets, lest you losecharacters that are not in the target character set.

• Test your applications not written in the Java language with different codepages in the application environment. You may not be able to control yourusers' code page. (The Java application code page is always UTF-16.)

• Test your application with some non-ASCII data. Code pages 437, 1252,8859-1, and 1208 (the encoding of UTF-8) all match US-ASCII in the first128 bytes, so US-ASCII data is less likely to find any problems encodingor transcoding. Check for loss of characters on transcoding, whichtypically does not produce an error message.

• Use the universal DB2 Driver for JDBC and SQLJ rather than legacy Type2 driver. If you cannot control the driver that your application uses, test itwith the type or types that will run in production, and avoid those methodsthat don't work in all types or work differently.

• For C applications, to process XML in the char[] data type, use theUTF-8 encoding, because it does not contain null (\0) as a valid byte.

• For CLI and ESQL applications, be aware that CLI returns XML data withinternal encoding. If you transcode and change the actual encoding, thedocument becomes unusable. To avoid this, bind your applicationvariable to the SQL_C_BINARY type; this also keeps the data in UTF,avoiding a lossy transcoding into your local code page.

• For any data type that will hold UTF characters, allow enough room,because one character may consume more than one byte.




Section 5. Validation of XML documents

Concept

An XML document is much more flexible in structure than a relational table. A tablehas a fixed structure, which SELECT statements can rely on. There is a precisenumber of columns with specific data types. An XML document is almost thecomplete opposite. It can vary in every detail -- node names, depth of the hierarchy,data types, attributes, etc. Query writers need to know about the structure of XMLdata. To provide orderly data in the XML world, there is the concept of validationprovided by XML schemas.

An XML Schema is like a rule book for other XML documents. It provides detailsabout what is allowed in the XML documents -- node names, order of nodes,attributes, data types, optional versus required, etc. Its counterpart in the DB2relational world is SYSCAT.COLUMNS. XML schemas are themselves XMLdocuments, with very specific rules about their own structure. An XML document thatconforms to the rules of an XML Schema is said to be validated or valid.

How to validate

In a database column of type XML, each row contains exactly one complete XMLdocument (or a null value). That document is either validated or not. Validation isperformed with the function XMLVALIDATE(). To perform validations, theappropriate XML Schema Documents (XSDs) must first be registered with thedatabase. Then the XMLVALIDATE() function can be used. This function acceptsan XML document as input, along with the identity of the appropriate XML Schema,and returns a validated XML document.

Ways to validate XML documents in an XML column (note that LOAD is notincluded):

• INSERT statement: Use of XMLVALIDATE()

• UPDATE statement: Use of XMLVALIDATE()

• IMPORT statement: Has syntax for validation




A complete example of registering an XML Schema and using XMLVALIDATE() canbe found in the article "Get off to a fast start with DB2 Viper" (developerWorks,March 2006).

Testing for validation

An XML column can be tested to determine whether or not it contains a validatedXML document with the predicate VALIDATED.

Example 1: Assume that the column XMLCOL is defined in the table T1. Retrieveonly the XML values that have been validated by any XML Schema.

SELECT xmlcolFROM

t1WHERE

xmlcol IS VALIDATED

Example 2: Assume that the column XMLCOL is defined in the table T1. Enforcethe rule that values cannot be inserted or updated unless they have been validated.

ALTER TABLE t1ADD CONSTRAINT ck_validated

CHECK (xmlcol IS VALIDATED)

Note that the constraint checks whether the XML column is validated with any XMLSchema. You cannot force validation of a specific schema.

To find out which specific XML Schema was used to validate an XML document, usethe XMLXSROBJECTID() function. It returns the key to a row in the tableSYSCAT.XSROBJECTS.

Section 6. XPath and XQuery

XPath and XQuery are already covered in several tutorials. See the following links inthe Resources section:



http://www.ibm.com/developerworks/db2/library/techarticle/dm-0603saracco/


• "DB2 9 Fundamentals exam 730 prep, Part 7: Introducing XQuery"(developerWorks, July 2006)

• "SQL and XQuery tutorial for IBM DB2, Part 7: Introduction to XML andXQuery" (developerWorks, August 2006)

• XML Related Technologies page (W3Schools)

• IBM Certified Solution Developer: XML 1.1 and Related Technologies

Read the example queries, predict the results, then check your answers by runningthe query against DB2.

Section 7. SQL/XML functions

Scalar functions XMLQUERY() and XMLEXISTS()

XMLQUERY() returns an XML value from the evaluation of an XQuery expressionpossibly using specified input arguments as XQuery variables.

That's straight out of the manual. So what does it mean practically? XMLQUERY()has two parts:

• An XQuery expression

• A PASSING clause, where data can be passed into the XQueryexpression

A common use of the function is to extract a portion of an XML document. An XMLcolumn in a table (if it's not null) contains a complete XML document. In a SELECTlist, if you select the XML column name, you get the whole XML document. If youwant a subset of the whole document, then you use XMLQUERY(). For the XQueryexpression, you may use a simple XPath expression like'$d/path1/path2/path3', or a full FLWOR expression. The $d refers to avariable in the PASSING clause. This clause allows data (columns, expressions,literals) to be passed into the XQuery expression. To extract a portion of an XMLdocument, the XML column name is included in the PASSING clause and given aname, like "d". The XQuery expression then refers to that as $d.

XMLEXISTS() is a predicate used in the WHERE clause of a SELECT statement. Its



http://www.ibm.com/developerworks/edu/dm-dw-db2-cert7307.html?S_TACT=105AGX19&S_CMP=db2certlp

http://www.ibm.com/developerworks/edu/dm-dw-dm-0608cao7-i.html?S_TACT=105AGX19&S_CMP=db2certlp

http://www.ibm.com/developerworks/edu/dm-dw-dm-0608cao7-i.html?S_TACT=105AGX19&S_CMP=db2certlp

http://www.w3schools.com/xml/xml_technologies.asp

http://www-03.ibm.com/certify/certs/xmsdreltop.shtml


syntax is similar to XMLQUERY(): it has an XQuery expression and a PASSINGclause. As with XMLQUERY(), the XQuery argument may be a simple XPath or fullFLWOR expression. If the XQuery expression returns at least one item, thenXMLEXISTS() returns TRUE.

XMLEXISTS() is normally used to test whether an XML document (in an XMLcolumn) contains something of interest. That something could be the existence of aparticular XML node, the existence of an attribute, a specific value for an XML node,or the specific value for an attribute. Multiple conditions can be tested. It is usuallymore efficient to combine multiple XML conditions into one XMLEXISTS() withXPath and and or, rather than make multiple calls to XMLEXISTS() combined withSQL AND and OR.

Many XML queries can be handled by either XQuery or XMLQUERY(). There aresome differences between the two:

• XQuery returns every item in the result sequence as a separate row.XMLQUERY() is a scalar function and part of a SELECT statement; itreturns only one value per call (that is, per XML document passed to it). Ifthere are multiple items in one XML document, XMLQUERY() must beable to return them in one row. It therefore returns a sequence containingall the items from that document.

• You cannot bind parameters or use host variables in XQuery. You can inXMLQUERY() and XMLEXISTS() with the PASSING clause.

• In XQuery, an XPath expression (for example, /path1/path2) performs therole of applying predicates ([] notation) and projecting (constructing theoutput list of elements) all together in one step. In a SELECT statement,the role of applying predicates to rows is handled by XMLEXISTS(); therole of applying predicates to nodes within a row, and of projecting, ishandled by XMLQUERY(). Often in an SQL/XML query, the same XPathpredicate is specified twice, in XMLEXISTS() and XMLQUERY().

• To apply predicates to relational columns, XQuery uses the XPathfunction db2-fn:sqlquery(). A regular SELECT with XMLQUERY()uses its normal WHERE clause, which can mix relational predicates withXMLEXISTS().

• Only XMLQUERY() can mix relational columns and XML data in the sameSELECT list.




See the article "Query DB2 XML data with SQL" (developerWorks, March 2006),which includes detailed examples of XMLQUERY() and XMLEXISTS() in action.

Scalar functions XMLELEMENT(), XMLATTRIBUTES(), andXMLNAMESPACES()

XMLELEMENT() creates an XML element, by wrapping an XML tag (for example,<xxx> and </xxx>) around something. The something can be any SQL expression(as long as it's not a structured type), including other XML elements. Namespacesand attributes can optionally be included. SQL expressions are automaticallyconverted into their character form, if needed, just as if they were enclosed in theCHAR() function.

XMLATTRIBUTES() creates one or more attributes within an XML element (forexample, <xxx a1="zzz">). This function can only be used within theXMLELEMENT() function.

XMLNAMESPACES() creates namespace declarations for an XML element. This canonly be used within other functions like XMLELEMENT(), XMLFOREST(), andXMLTABLE().

Example 1: XMLELEMENT(), simple

Listing 4. Example 1 SQL code

SELECT XMLELEMENT( NAME "ColName", colname),XMLELEMENT( NAME "Type", typename)

FROMsyscat.columns

WHEREtabschema = 'SYSCAT'

AND tabname = 'TABLES'ORDER BY

colno;

Listing 5. Example 1 sample output

<ColName>TABSCHEMA</ColName> <Type>VARCHAR</Type><ColName>TABNAME</ColName> <Type>VARCHAR</Type><ColName>OWNER</ColName> <Type>VARCHAR</Type><ColName>TYPE</ColName> <Type>CHARACTER</Type><ColName>STATUS</ColName> <Type>CHARACTER</Type><ColName>BASE_TABSCHEMA</ColName> <Type>VARCHAR</Type><ColName>BASE_TABNAME</ColName> <Type>VARCHAR</Type><ColName>ROWTYPESCHEMA</ColName> <Type>VARCHAR</Type>



http://www.ibm.com/developerworks/db2/library/techarticle/dm-0603saracco2/


<ColName>ROWTYPENAME</ColName> <Type>VARCHAR</Type><ColName>CREATE_TIME</ColName> <Type>TIMESTAMP</Type><ColName>INVALIDATE_TIME</ColName> <Type>TIMESTAMP</Type><ColName>STATS_TIME</ColName> <Type>TIMESTAMP</Type><ColName>COLCOUNT</ColName> <Type>SMALLINT</Type><ColName>TABLEID</ColName> <Type>SMALLINT</Type><ColName>TBSPACEID</ColName> <Type>SMALLINT</Type>

Example 2: XMLELEMENT(), two attributes


SELECT XMLELEMENT(

NAME "TableName",XMLATTRIBUTES(tableid AS "TableId",colcount AS "NumCols"

),RTRIM(tabschema) || '.' || tabname

)FROM

syscat.tablesWHERE

type = 'T'ORDER BY

tableid;


<TableName TableId="2" NumCols="67">SYSIBM.SYSTABLES</TableName><TableName TableId="2" NumCols="44">DB2QP.TRACK_QUERY_INFO</TableName><TableName TableId="2" NumCols="3">TOOLS.REL_CAT_MEMBER</TableName><TableName TableId="3" NumCols="41">SYSIBM.SYSCOLUMNS</TableName><TableName TableId="3" NumCols="13">DB2QP.MANAGE_QUERY_INFO</TableName><TableName TableId="3" NumCols="9">TOOLS.REL_TYPE</TableName><TableName TableId="4" NumCols="57">SYSIBM.SYSINDEXES</TableName><TableName TableId="4" NumCols="6">DB2QP.RESULT_INFO</TableName><TableName TableId="4" NumCols="5">SYSTOOLS.POLICY</TableName><TableName TableId="4" NumCols="8">TOOLS.REL_CAT</TableName><TableName TableId="5" NumCols="9">SYSIBM.SYSCOLPROPERTIES</TableName><TableName TableId="5" NumCols="23">DB2QP.QP_SYSTEM</TableName><TableName TableId="5" NumCols="27">SYSTOOLS.HMON_ATM_INFO</TableName><TableName TableId="5" NumCols="5">TOOLS.REL_LINK</TableName><TableName TableId="6" NumCols="5">SYSIBM.SYSINDEXCOLUSE</TableName>

Example 3: XMLELEMENT(), with default namespace


SELECT XMLELEMENT(NAME "ColName",




XMLNAMESPACES(

DEFAULT 'www.ibm.com'),colname

)FROM

syscat.columnsWHERE

tabschema = 'SYSCAT'AND tabname = 'TABLES'ORDER BY

colno;


<ColName xmlns="www.ibm.com">TABSCHEMA</ColName><ColName xmlns="www.ibm.com">TABNAME</ColName><ColName xmlns="www.ibm.com">OWNER</ColName><ColName xmlns="www.ibm.com">TYPE</ColName><ColName xmlns="www.ibm.com">STATUS</ColName><ColName xmlns="www.ibm.com">BASE_TABSCHEMA</ColName><ColName xmlns="www.ibm.com">BASE_TABNAME</ColName><ColName xmlns="www.ibm.com">ROWTYPESCHEMA</ColName><ColName xmlns="www.ibm.com">ROWTYPENAME</ColName><ColName xmlns="www.ibm.com">CREATE_TIME</ColName><ColName xmlns="www.ibm.com">INVALIDATE_TIME</ColName><ColName xmlns="www.ibm.com">STATS_TIME</ColName><ColName xmlns="www.ibm.com">COLCOUNT</ColName><ColName xmlns="www.ibm.com">TABLEID</ColName><ColName xmlns="www.ibm.com">TBSPACEID</ColName>

Example 4: XMLELEMENT(), with namespace


SELECT XMLELEMENT(NAME "db2:ColName",XMLNAMESPACES(

'www.ibm.com/db2' AS "db2"),colname

)FROM

syscat.columnsWHERE


colnoFETCH FIRST 15 ROWS ONLY;





<db2:ColName xmlns:db2="www.ibm.com/db2">TABSCHEMA</db2:ColName><db2:ColName xmlns:db2="www.ibm.com/db2">TABNAME</db2:ColName><db2:ColName xmlns:db2="www.ibm.com/db2">OWNER</db2:ColName><db2:ColName xmlns:db2="www.ibm.com/db2">TYPE</db2:ColName><db2:ColName xmlns:db2="www.ibm.com/db2">STATUS</db2:ColName><db2:ColName xmlns:db2="www.ibm.com/db2">BASE_TABSCHEMA</db2:ColName><db2:ColName xmlns:db2="www.ibm.com/db2">BASE_TABNAME</db2:ColName><db2:ColName xmlns:db2="www.ibm.com/db2">ROWTYPESCHEMA</db2:ColName><db2:ColName xmlns:db2="www.ibm.com/db2">ROWTYPENAME</db2:ColName><db2:ColName xmlns:db2="www.ibm.com/db2">CREATE_TIME</db2:ColName><db2:ColName xmlns:db2="www.ibm.com/db2">INVALIDATE_TIME</db2:ColName><db2:ColName xmlns:db2="www.ibm.com/db2">STATS_TIME</db2:ColName><db2:ColName xmlns:db2="www.ibm.com/db2">COLCOUNT</db2:ColName><db2:ColName xmlns:db2="www.ibm.com/db2">TABLEID</db2:ColName><db2:ColName xmlns:db2="www.ibm.com/db2">TBSPACEID</db2:ColName>

Example 5: XMLELEMENT() options for NULLs


SELECT XMLELEMENT(NAME "Table", tabname),XMLELEMENT(NAME "TbSpc1",tbspaceOPTION EMPTY ON NULL

),XMLELEMENT(NAME "TbSpc2",tbspaceOPTION NULL ON NULL

)FROM

syscat.tablesWHERE

tbspace IS NULLFETCH FIRST 15 ROWS ONLY;


<Table>CHECK_CONSTRAINTS</Table> <TbSpc1/>-

<Table>COLUMNS</Table> <TbSpc1/>-

<Table>COLUMNS_S</Table> <TbSpc1/>-

<Table>REFERENTIAL_CONSTRAINTS</Table> <TbSpc1/>-

<Table>REF_CONSTRAINTS</Table> <TbSpc1/>-

<Table>TABLE_CONSTRAINTS</Table> <TbSpc1/>-

<Table>TABLES</Table> <TbSpc1/>-

<Table>TABLES_S</Table> <TbSpc1/>-

<Table>USER_DEFINED_TYPES</Table> <TbSpc1/>-




<Table>UDT_S</Table> <TbSpc1/>-

<Table>VIEWS</Table> <TbSpc1/>-

<Table>PARAMETERS</Table> <TbSpc1/>-

<Table>PARAMETERS_S</Table> <TbSpc1/>-

<Table>ROUTINES</Table> <TbSpc1/>-

<Table>ROUTINES_S</Table> <TbSpc1/>-

Scalar function XMLFOREST()

The XMLFOREST() function creates a series of one or more XML elements,concatenated together. The differences between XMLELEMENT() andXMLFOREST() are:

• XMLELEMENT() creates exactly one element. XMLFOREST() can createany number of elements, concatenated together.

• The name of the XML element is required syntax for XMLELEMENT(), butcan be defaulted for XMLFOREST() for simple SQL expressions. This isdemonstrated in the example you'll see in a moment.

• XMLELEMENT() can create attributes; XMLFOREST() cannot.

XMLFOREST() is like a combination of XMLCONCAT() and XMLELEMENT() (exceptthat XMLFOREST() cannot specify attributes). It's a very convenient way to specify aseries of XML elements.

Example 6: XMLFOREST() (no namespaces)

In this example, notice that colno and typename both specify a name for an XMLelement, while colname does not. Omitting them makes the name the same as thecolumn, but in all uppercase.


SELECT XMLFOREST(colno AS "ColNum",colname,typename AS "DataType"

)FROM

syscat.columnsWHERE

tabschema = 'SYSCAT'




AND tabname = 'TABLES'ORDER BY



<ColNum>0</ColNum><COLNAME>TABSCHEMA</COLNAME><DataType>VARCHAR</DataType><ColNum>1</ColNum><COLNAME>TABNAME</COLNAME><DataType>VARCHAR</DataType><ColNum>2</ColNum><COLNAME>OWNER</COLNAME><DataType>VARCHAR</DataType><ColNum>3</ColNum><COLNAME>TYPE</COLNAME><DataType>CHARACTER</DataType><ColNum>4</ColNum><COLNAME>STATUS</COLNAME><DataType>CHARACTER</DataType><ColNum>5</ColNum><COLNAME>BASE_TABSCHEMA</COLNAME><DataType>VARCHAR</DataType><ColNum>6</ColNum><COLNAME>BASE_TABNAME</COLNAME><DataType>VARCHAR</DataType><ColNum>7</ColNum><COLNAME>ROWTYPESCHEMA</COLNAME><DataType>VARCHAR</DataType><ColNum>8</ColNum><COLNAME>ROWTYPENAME</COLNAME><DataType>VARCHAR</DataType><ColNum>9</ColNum><COLNAME>CREATE_TIME</COLNAME><DataType>TIMESTAMP</DataType><ColNum>10</ColNum><COLNAME>INVALIDATE_TIME</COLNAME><DataType>TIMESTAMP</DataType><ColNum>11</ColNum><COLNAME>STATS_TIME</COLNAME><DataType>TIMESTAMP</DataType><ColNum>12</ColNum><COLNAME>COLCOUNT</COLNAME><DataType>SMALLINT</DataType><ColNum>13</ColNum><COLNAME>TABLEID</COLNAME><DataType>SMALLINT</DataType><ColNum>14</ColNum><COLNAME>TBSPACEID</COLNAME><DataType>SMALLINT</DataType>

Example 7: XMLFOREST() (with default namespace)


SELECT XMLFOREST(XMLNAMESPACES(

DEFAULT 'www.ibm.com'),colno AS "ColNum",colname

)FROM

syscat.columnsWHERE




Example 8: XMLFOREST() with option EMPTY ON NULL


SELECT XMLFOREST(tabname AS "Table",



sidefile3.html


tbspace AS "TbSpc"OPTION EMPTY ON NULL

)FROM

syscat.tablesWHERE



<Table>CHECK_CONSTRAINTS</Table><TbSpc/><Table>COLUMNS</Table><TbSpc/><Table>COLUMNS_S</Table><TbSpc/><Table>REFERENTIAL_CONSTRAINTS</Table><TbSpc/><Table>REF_CONSTRAINTS</Table><TbSpc/><Table>TABLE_CONSTRAINTS</Table><TbSpc/><Table>TABLES</Table><TbSpc/><Table>TABLES_S</Table><TbSpc/><Table>USER_DEFINED_TYPES</Table><TbSpc/><Table>UDT_S</Table><TbSpc/><Table>VIEWS</Table><TbSpc/><Table>PARAMETERS</Table><TbSpc/><Table>PARAMETERS_S</Table><TbSpc/><Table>ROUTINES</Table><TbSpc/><Table>ROUTINES_S</Table><TbSpc/>

Example 9: XMLFOREST() with option NULL ON NULL


SELECT XMLFOREST(tabname AS "Table",tbspace AS "TbSpc"OPTION NULL ON NULL

)FROM

syscat.tablesWHERE



<Table>CHECK_CONSTRAINTS</Table><Table>COLUMNS</Table><Table>COLUMNS_S</Table><Table>REFERENTIAL_CONSTRAINTS</Table><Table>REF_CONSTRAINTS</Table><Table>TABLE_CONSTRAINTS</Table><Table>TABLES</Table><Table>TABLES_S</Table><Table>USER_DEFINED_TYPES</Table>




<Table>UDT_S</Table><Table>VIEWS</Table><Table>PARAMETERS</Table><Table>PARAMETERS_S</Table><Table>ROUTINES</Table><Table>ROUTINES_S</Table>

Scalar function XMLCONCAT()

XMLCONCAT() concatenates two or more XML elements together. This evenincludes the output from functions like XMLCOMMENT(), XMLTEXT(), and XMLPI().There are at least a couple of situations where XMLCONCAT() is very useful:

• As input to the function XMLAGG(), which only accepts one argument

• Construct mixed nodes (for example, John <mi>Q</mi> Smith)

Example 10: XMLCONCAT() with multiple XMLELEMENT()


SELECT XMLCONCAT(XMLELEMENT(NAME "Name", indname),XMLELEMENT(NAME "NumCols", colcount),XMLELEMENT(NAME "Uniq", uniquerule)

)FROM

syscat.indexesFETCH FIRST 15 ROWS ONLY;


<Name>INDTABLES01</Name><NumCols>2</NumCols><Uniq>U</Uniq><Name>INDTABLES02</Name><NumCols>1</NumCols><Uniq>D</Uniq><Name>INDTABLES03</Name><NumCols>1</NumCols><Uniq>D</Uniq><Name>INDTABLES04</Name><NumCols>1</NumCols><Uniq>D</Uniq><Name>INDTABLES05</Name><NumCols>4</NumCols><Uniq>U</Uniq><Name>INDTABLES06</Name><NumCols>2</NumCols><Uniq>D</Uniq><Name>INDTABLES07</Name><NumCols>2</NumCols><Uniq>D</Uniq><Name>INDTABLES08</Name><NumCols>1</NumCols><Uniq>D</Uniq><Name>INDCOLUMNS01</Name><NumCols>3</NumCols><Uniq>U</Uniq><Name>INDCOLUMNS02</Name><NumCols>2</NumCols><Uniq>D</Uniq><Name>INDCOLUMNS03</Name><NumCols>1</NumCols><Uniq>D</Uniq><Name>INDINDEXES01</Name><NumCols>2</NumCols><Uniq>U</Uniq><Name>INDINDEXES02</Name><NumCols>3</NumCols><Uniq>U</Uniq><Name>INDINDEXES03</Name><NumCols>2</NumCols><Uniq>D</Uniq><Name>INDINDEXES04</Name><NumCols>3</NumCols><Uniq>D</Uniq>

Example 11: Using XMLCONCAT() to create a mixed node





SELECT XMLCONCAT(XMLELEMENT(NAME "Index", indname),XMLTEXT(' has '),XMLELEMENT(NAME "NumCols", colcount)

)FROM

syscat.indexesFETCH FIRST 15 ROWS ONLY;


<Index>INDTABLES01</Index> has <NumCols>2</NumCols><Index>INDTABLES02</Index> has <NumCols>1</NumCols><Index>INDTABLES03</Index> has <NumCols>1</NumCols><Index>INDTABLES04</Index> has <NumCols>1</NumCols><Index>INDTABLES05</Index> has <NumCols>4</NumCols><Index>INDTABLES06</Index> has <NumCols>2</NumCols><Index>INDTABLES07</Index> has <NumCols>2</NumCols><Index>INDTABLES08</Index> has <NumCols>1</NumCols><Index>INDCOLUMNS01</Index> has <NumCols>3</NumCols><Index>INDCOLUMNS02</Index> has <NumCols>2</NumCols><Index>INDCOLUMNS03</Index> has <NumCols>1</NumCols><Index>INDINDEXES01</Index> has <NumCols>2</NumCols><Index>INDINDEXES02</Index> has <NumCols>3</NumCols><Index>INDINDEXES03</Index> has <NumCols>2</NumCols><Index>INDINDEXES04</Index> has <NumCols>3</NumCols>

Scalar function XMLAGG()

XMLAGG() is an aggregate function, just like SUM(), MIN(), and MAX(). It takesone XML expression as an argument, and, for each group of records, concatenatesthe values all together. There is an optional ORDER BY clause. XMLAGG() is veryuseful when building up complicated XML documents that involve one-to-manyrelationships. In the example that follows, each row of SYSCAT.INDEXES iscombined with many rows from SYSCAT.INDEXCOLUSE. Numerous, nestedone-to-many relationships can be handled by XMLAGG() when converting relationaldata into XML.

Example 12: XMLAGG()


WITHt1 AS(

SELECT indschema,




indname,colseq,XMLELEMENT(NAME "ColName",XMLATTRIBUTES(colseq AS "Seq"),colname

) AS xml_colFROM

syscat.indexcoluse),t2 (indschema, indname, list_cols) AS(

SELECT indschema,indname,XMLAGG(xml_col ORDER BY colseq)

FROMt1

GROUP BYindschema,indname

)SELECT XMLELEMENT(

NAME "Index",XMLATTRIBUTES(i.indname AS "Name"),t2.list_cols

)FROM

syscat.indexes I,t2 T2

WHEREi.colcount BETWEEN 2 AND 4

AND t2.indschema = i.indschemaAND t2.indname = i.indnameFETCH FIRST 15 ROWS ONLY;


Row #1:

<Index Name="ATM_UNIQ"><ColName Seq="1">SCHEMA</ColName><ColName Seq="2">NAME</ColName><

</Index>

Row #2:

<Index Name="HI_OBJ_UNIQ"><ColName Seq="1">HI_ID</ColName><ColName Seq="2">OBJ_NAME1</ColName><ColName Seq="3">OBJ_NAME2</ColName><ColName Seq="4">OBJ_NAME3</ColName>

</Index>

Row #3:

<Index Name="IMDTA00"><ColName Seq="1">TOWNER00</ColName><ColName Seq="2">TASKNAME00</ColName>

</Index>

Row #4:




<Index Name="IMDTA03"><ColName Seq="1">TASKID00</ColName><ColName Seq="2">SUFFIX00</ColName><ColName Seq="3">MODIFIED00</ColName>

</Index>

Row #5:

<Index Name="IMDTA04"><ColName Seq="1">TASKID00</ColName><ColName Seq="2">SUFFIX00</ColName>

</Index>

Notes about Example 12

• This example can be in coded several different ways. Common tableexpressions were used to break up the code and hopefully make itclearer. The alternative coding techniques involve nesting many levels offunctions within each other and/or the use of subqueries, either of whichwould have been harder to read.

• The sample output was reformatted (with indentations and line breaksadded) for clarity.

• This example illustrated just one one-to-many relationship, but numerousone-to-many relationships can be handled.

• The first CTE (common table expression), T1, obtains the detailed datafor the child table (INDEXCOLUSE).

• The second CTE, T2, aggregates the data from INDEXCOLUSE into asingle row per index. It is possible to do the work of T1 and T2 together inone SELECT statement, by nesting XMLELEMENT() within XMLAGG().That would be manageable, but nesting more than a couple layers offunctions quickly becomes hard to read and makes it difficult to line upmatching parentheses.

• The final SELECT joins INDEXES with the aggregated data fromINDEXCOLUSE and wraps an XML element around it.

Scalar function XMLDOCUMENT()




XMLDOCUMENT() converts an XML node into an XML document. The XML nodemust be well formed, which mainly means that it must have a single root element.Tables with columns of type XML always contain well-formed XML documents. TheXMLQUERY() function returns XML nodes (not documents). The main situation inwhich you would need the XMLDOCUMENT() function is when you are inserting theresults of XMLQUERY() into a table with an XML column. Example 13 illustrates this.

Example 13: XMLDOCUMENT()


CREATE TABLE demo.table1(

key INT,xml_col XML

);


DB20000I The SQL command completed successfully.


INSERT INTO demo.table1 VALUES(1,'<person><first>Joe</first><last>Smith</last></person>');




SELECT XMLQUERY('$d/person/first'PASSING xml_col AS "d"

)FROM

demo.table1WHERE

key = 1;





<first>Joe</first>


INSERT INTO demo.table1SELECT 2,

XMLQUERY('$d/person/first'PASSING xml_col AS "d"

)FROM

demo.table1WHERE

key = 1;


DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:SQL20345N The XML value is not a well-formed document with a single rootelement. SQLSTATE=2200L


INSERT INTO demo.table1SELECT 2,

XMLDOCUMENT(XMLQUERY(

'$d/person/first'PASSING xml_col AS "d"

))

FROMdemo.table1

WHEREkey = 1

;




SELECT * FROM demo.table1;




KEY XML_COL---- -------------------------------

1 <person><first>Joe</first><last>Smith</last></person>2 <first>Joe</first>

Scalar function XMLTEXT()

XMLTEXT() creates a text node. Example 11 above demonstrates how to use it. Itsmain purpose is to help create mixed type nodes.

Scalar function XMLCOMMENT()

XMLCOMMENT() creates a comment node. Comments are strings with tworestrictions:

• They cannot contain two consecutive dashes (--)

• They cannot end with a dash (-)

Example 14: XMLCOMMENT() (valid)


SELECT XMLCONCAT(XMLELEMENT(NAME "Dummy1", 1200),XMLCOMMENT('Some comment'),XMLELEMENT(NAME "Dummy2", 'hello')

)FROM

sysibm.sysdummy1;


<Dummy1>1200</Dummy1><Dummy2>hello</Dummy2>

Example 15: XMLCOMMENT() with disallowed consecutive dashes (not valid)

SELECT XMLCONCAT(XMLELEMENT(NAME "Dummy1", 1200),XMLCOMMENT('Bad -- comments'),XMLELEMENT(NAME "Dummy2", 'hello')

)FROM

sysibm.sysdummy1;


SQL20331N The XML comment value "Bad -- comments" is not valid.SQLSTATE=2200S

Example 16: XMLCOMMENT() with disallowed final dash (not valid)


SELECT XMLCONCAT(XMLELEMENT(NAME "Dummy1", 1200),XMLCOMMENT('Bad comments -'),XMLELEMENT(NAME "Dummy2", 'hello')

)FROM

sysibm.sysdummy1;


SQL20331N The XML comment value "Bad comments -" is not valid. SQLSTATE=2200S

Scalar function XMLPI()

XMLPI() creates an XML processing instruction. There are a couple of restrictions:

• The PI name cannot be xml anywhere, in any combination of upper andlower case

• The string cannot contain ?> anywhere

Example 17: XMLPI() (valid)





SELECT XMLCONCAT(XMLELEMENT(NAME "Dummy1", 1200),XMLPI(NAME "ValidName", 'some string'),XMLELEMENT(NAME "Dummy2", 'hello')

)FROM

sysibm.sysdummy1;


<Dummy1>1200</Dummy1><?ValidName some string?><Dummy2>hello</Dummy2>

Example 18: XMLPI() with invalid name (not valid)


SELECT XMLCONCAT(XMLELEMENT(NAME "Dummy1", 1200),XMLPI(NAME "xmL", 'some string'),XMLELEMENT(NAME "Dummy2", 'hello')

)FROM

sysibm.sysdummy1;


SQL20275N The XML name "xmL" is not valid. Reason code = "5". SQLSTATE=42634

Example 19: XMLPI() with invalid string (not valid)


SELECT XMLCONCAT(XMLELEMENT(NAME "Dummy1", 1200),XMLPI(NAME "Valid", 'bad ?> string'),XMLELEMENT(NAME "Dummy2", 'hello')

)FROM

sysibm.sysdummy1;


SQL20332N The XML processing instruction value "bad ?> string" is not valid.




SQLSTATE=2200T

Section 8. DBA-related tasks related to XML: Indexes,constraints, and views

The DBA certification tutorial doesn't cover XML indexes, so DBAs may come to youfor help indexing XML columns. Here are some tips:

• See the "Common XML indexing issues" in the DB2 Information Center .

• Indexes must be at least as broad or inclusive as the XPath in the querythat you want to run faster. An XPath that uses the wild cards * or // ismore broad than one that specifies the exact path to the desired node.

• XML indexes may be UNIQUE, which is the only way to enforce a UNIQUEconstraint on a field in XML. As with a relational UNIQUE index,uniqueness is enforced across the whole table, not just within onedocument.

• DB2 does not support composite XML indexes, but you may be able towork around this by putting the desired elements under a common parentelement, and creating an XML index on the parent, like so:

<name><family>Payne</family><given>Donald</given><middle>Edwin</middle>

</name>

An index on the XPath name will index the concatenation of the text in thechild elements. Another alternative is the DB2 Net Search Extender; thishas been enhanced to fully support XML columns, including proximity andwild card searches.

• There is no direct way to create a referential integrity constraint on XMLdata; you may work around this by shredding selected fields to relationalcolumns that have the desired constraint.



http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/c0024203.htm


• You may create an XML view on relational data with SQL/XML functionslike XMLDOCUMENT(), XMLELEMENT(), etc. You may also create arelational view on XML data, with XMLTABLE(). But it is usually moreefficient to query XML data through XQuery or SQL XMLQUERY(), andrelational data through SQL SELECT. If you query an XML view onrelational data, the query compiler and optimizer cannot take advantageof relational indexes, or vice versa.

Section 9. DB2 9 enhancements for developers

DB2 9 includes enhancements for XML data:

• Database Add-Ins for Microsoft Visual Studio 2005

• DB2 Developer Workbench (DWB)

• SQL Assist

The Database Add-Ins for Microsoft Visual Studio 2005 include features for XML:

• Use an XML data type for columns and procedures

• Provide an XML index for an XML column

• Have the capability to visualize XML data

• Update, import, and export XML data

• Validate an XML database against a registered XML Schema

• Register and unregister XML schemas

• Generate sample data based on an XML Schema

• Create and register annotated XML schemas

• Execute and visualize XQuery and SQL/XML scripts

• Apply XSLT to XML data for customized visualization

See the Resources section for a link to a developerWorks tutorial on DWB and forthe Information Online article "What's new for V9.1: IBM Database Add-Ins for




Microsoft Visual Studio 2005 enhancements" for details.

Section 10. DB2 Command Line Processor (CLP)behavior and options

If you run the DB2 Command Line Processor (CLP) to learn about pureXML, beaware of the following CLP behavior and CLP options that change that behavior.This is not likely to be on the exam, but is worth mentioning in this tutorial, since thisbehavior might mislead you about how DB2 processes XML.

• The CLP returns XML from a SELECT statement as a 4 KB charactercolumn. Short XML output is padded with trailing spaces. The headingand result set can scroll off the screen.

• The CLP db2 cannot always serialize XML data that it successfullyinserts:

C:\>chcpActive code page: 1252

Σ is the XML numeric reference for the Greek letter capital sigma.The CLP returns an error when you select this XML document, whetheryour code page is 1252 or 437, even though sigma is a character in codepage 437.

C:\>db2 -v "insert into catalog values('Ref3', '<sigma>Σ</sigma>')"insert into catalog values ('Ref3','<sigma>Σ</sigma>')DB20000I The SQL command completedsuccessfully.

C:\>db2 -x "select catlog from catalog wherename = 'Ref3'"SQL0969N There is no message textcorresponding to SQL error "-20412" in themessage file on this workstation. The errorwas returned from module"SQLABOLB" with original tokens "".SQLSTATE=2200W

C:\>db2 ? 2200WSQLSTATE 2200W: An XML value contained data




that could not be serialized.

C:\>chcp 437Active code page: 437

C:\>db2 -x "select catlog from catalog wherename = 'Ref3'"SQL0969N There is no message textcorresponding to SQL error "-20412" in themessage file on this workstation. The errorwas returned from module"SQLABOLB" with original tokens "".SQLSTATE=2200W

A JDBC application can read this XML. Serializing the XML AS VARCHARavoids the CLP error, but the sigma is lost, replaced by the DOSend-of-file character, Control-Z, which appears as a box.

C:\>db2 -x "select xmlserialize(catlog as varchar(90))from catalog where name = 'Ref3'"</sigma>[box]</sigma>

• The CLP normally strips any whitespace that contains a new line inincoming string literals, replacing it with a single space. This behavior maymask the processing of whitespace in XML parsing and validation (seethe earlier section of this tutorial on that topic for more information). It isnot XML specific, so the CLP does this replacement not only to XMLboundary whitespace, but to any string of whitespace that contains atleast one new line. The CLP does not change whitespace that does notinclude a new line. Two CLP options, -n and -q, change this behavior.

To demonstrate, here is an SQL script that inserts an XML document. Thetext in the XML documents the whitespace following it; for example,following 1 space + NL + 1 space:, there is one space, a new lineand one space. In the following listings of code and output, a dollar sign isshown at the end of each line to show where the line ends and wherethere is whitespace (as in the vi command :%l). A line that must bebroken across two lines for readability ends with a backslash (\) andcontinues on the next line at the left margin. The long string of spaces thatpads the XML result to 4 KB is omitted.

delete from catalog where name = 'foo';$insert into catalog (name, catlog) values ($'foo',$xmlparse(document '<a>1 space + NL + 1 space: $3 spaces: NL:$

</a>' preserve whitespace)$);$select catlog from catalog where name = 'foo';$




If you run this without options -q or -n, the behavior described occurs:

C:\>db2 -xtvf testWhitespace.sql > testws-no_opts.txtdelete from catalog where name = 'foo'$DB20000I The SQL command completed successfully.$$insert into catalog (name, catlog) values ( 'foo', xmlparse(document \'<a>1 space + NL + 1 space: 3 spaces: NL: </a>' preserve whitespace) )$DB20000I The SQL command completed successfully.$$select catlog from catalog where name = 'foo'$<a>1 space + NL + 1 space: 3 spaces: NL: </a>$

• One space plus new line plus one space was changed to one space.

• Three spaces without any new lines was unchanged.

• One new line was changed to one space.If you run the same script with -n, CLP removes new lines but leavesunchanged any spaces adjacent to the new lines:

C:\>db2 -xntvf testWhitespace.sql > testws-n.txt

delete from catalog where name = 'foo'$DB20000I The SQL command completed successfully.$$insert into catalog (name, catlog) values ( 'foo', xmlparse(document \'<a>1 space + NL + 1 space: 3 spaces: NL:</a>' preserve whitespace) )$DB20000I The SQL command completed successfully.$$select catlog from catalog where name = 'foo'$<a>1 space + NL + 1 space: 3 spaces: NL:</a>$$

• One space plus new line plus one space was changed to two spaces.

• Three spaces without any new line was unchanged, as before.

• One new line was removed.With the -q option (which overrides -n), CLP preserves all whitespaceexactly as is:

C:\>db2 -xqtvf testWhitespace.sql > testws-q.txtdelete from catalog where name = 'foo'$DB20000I The SQL command completed successfully.$$insert into catalog (name, catlog) values ( 'foo', xmlparse(document \'<a>1 space + NL + 1 space: $3 spaces: NL:$

</a>' preserve whitespace) )$DB20000I The SQL command completed successfully.$$select catlog from catalog where name = 'foo'$<a>1 space + NL + 1 space: $




3 spaces: NL:$</a>$$

This is important if you insert "pretty" XML containing new lines, or insertXML with explicit XMLPARSE() with the argument PRESERVEWHITESPACE to preserve boundary whitespace (see the earlier section ofthis tutorial on that topic for more information).

This behavior affects statements in the sample script that you can find atSQLLIB\samples\xml\db2sample_xml.db2 in your DB2 install. This scriptinserts into the CUSTOMER and PRODUCT tables with the clauseXMLVALIDATE( XMLPARSE( DOCUMENT '...' PRESERVEWHITESPACE ) ). If you run db2sample_XML.db2 without -q, then theCLP strips most whitespace before it gets to the XML parser.

• The CLP option -v echoes the command before execution. This is usefulto confirm the exact SQL that DB2 executes -- for instance, to determine ifyou escape characters like quote marks on the command line, or use theoptions -n or -q.

• The CLP option -i "pretty-prints" the result of an XQuery, with indentationas follows:

• Each tag and each text node stands alone on its own line

• Each starting tag lines up vertically with its corresponding ending tag

• Each element or text node is indented from its parent elementTo accomplish this, CLP adds not only boundary whitespace butwhitespace to non-empty elements. This option has no effect on an SQLSELECT: in an ordinary SELECT, CLP returns data in columns with adefinite length and must therefore return XML as a regular characterstring.

• -x omits column headings, the line of dashes, and the message numberrecord(s) selected, reducing the volume of output. CLP still padsXML with spaces to 4 KB on SELECT.

• -d retrieves and displays XML declarations in the result. On Windows, theencoding attribute is windows-1252, regardless of your current codepage (set with the DOS command CHCP). If you select the XML with




XMLSERIALIZE(column INCLUDING XMLDECLARATION), this optionhas no effect: DB2 generates the declaration (with encoding="UTF-8"),not CLP. The option -d, like the clause INCLUDING XMLDECLARATION,works regardless of whether the original inserted XML had a declaration:the declaration is generated, not retrieved from storage.

• -td<terminator> specifies an SQL statement terminator characterother than the semicolon. This is necessary when running an XQuerycontaining a semicolon. Depending on the content of your queries,possible terminators include pound sign (#), grave accent (`), and caret(^).

• You may reduce the size of data that CLP returns from a SELECT byadding an explicit XMLSERIALIZE() or CAST() of the column AS a CHARor VARCHAR type. Beware that this can change the way in which thedocument is encoded.

Section 11. Conclusion

With this tutorial, you've learned how to manipulate XML data in DB2. You shouldnow know:

• How DB2 handles boundary whitespace on input

• How DB2 determines the encoding of an XML document on input andoutput

• How DB2 can validate XML on input

• How to execute an XQuery or XPath, and identify its results

• How to decompose, or shred, XML input to relational rows

• How to publish relational data as XML

• How to publish XML data as relational

Good luck with the exam!




Downloads

Description Name Size Download method

Sample JDBC, SQL db2CertExamV9AppDevXmlSample.zip72KB HTTP

Information about download methods



http://download.boulder.ibm.com/ibmdl/pub/software/dw/dm/db2/db2-cert7333/db2CertExamV9AppDevXmlSample.zip

http://www.ibm.com/developerworks/library/whichmethod.html


Resources

Learn

• DB2 9 Application Development exam 733 prep tutorial series: Check out theother parts of this tutorial series designed to help you prepare for the DB2 9Family Application Development Certification exam (Exam 733). The seriesteaches about all aspects of programming with DB2, including those that arecommon to XML and regular relational data. The complete list of all tutorials inthis series includes:

• Database objects and programming methods

• Data manipulation

• XML data manipulation

• Embedded SQL programming

• ODBC/CLI programming

• .NET programming

• Java programming

• Advanced programming

• User-defined routines

• DB2 9 Fundamentals certification prep tutorial series: Before you take the DB2 9Application Development certification exam (Exam 733), you should have alreadytaken and passed the DB2 9 Fundamentals certification exam (Exam 730). Usethis series of seven tutorials to prepare for that exam; topics include:

• DB2 planning

• DB2 security

• Accessing DB2 data

• Working with DB2 data

• Working with DB2 objects

• Data concurrency

• Introducing Xquery

• Application Development, DB2, Java: IBM Information Center, "Deprecated



http://www.ibm.com/developerworks/offers/lp/db2cert/db2-cert733.html?S_TACT=105AGX11&S_CMP=tut

http://www.ibm.com/developerworks/offers/lp/db2cert/db2-cert730.html?S_TACT=105AGX11&S_CMP=tut


and discontinued features," heading "Application programming," sub-headings"DB2 JDBC type 2 driver is deprecated" and "DB2 JDBC Type 3 is discontinued."

• XML: The developerWorks XML zone has over 800 articles. For example, asearch in the XML zone for the words "parse whitespace" returns 21 hits, "parseencoding" returns 74, and "XQuery," 101.

• DB2, XML: The developerWorks Information Management zone has over 100articles and tutorials related to XML. The oldest of these (around February 2006or older) are about the XML Extender for DB2 7 and 8. Because DB2 9introduces so many new XML features, some of the information and advice inthose articles are obsolete with DB2 9. You might sort any search list bydescending date to get the most recent articles first. Some material refers to DB29 as "DB2 Viper," its code name before its release in July 2006.

• XPath, XQuery: "SQL and XQuery tutorial for IBM DB2, Part 7: Introduction toXML and XQuery" (developerWorks, August 2006): Get an introduction topureXML and XQuery, write XQueries to retrieve XML elements, filter data basedon XML values, transform XML output, use various clauses to select data moreprecisely, and mix XQuery and SQL.

• SQL/XML: "Query DB2 XML data with SQL" (developerWorks, March 2006):Learn how to query data stored in XML columns using SQL and SQL/XML, withexamples on XMLQUERY() and XMLEXISTS().

• XML: "XML Tutorial" (W3Schools): Learn the basics of XML and find links toother tutorials on XQuery, XML Schema, and so on. Some pages relevant to thisdeveloperWorks tutorial follow.

• XPath, XQuery, XSL, XSLT, XML Schema: XML Related Technologies page(W3Schools): Find links to tutorials on XPath, XQuery, XSL, XSLT, and XSD.

• Application Development, DB2, XML, XPath, XQuery: IBM offers ProfessionalCertification as an IBM Certified Solution Developer - XML 1.1 and RelatedTechnologies. Under the "Training resources" tab, there are links to manyself-study materials to help you prepare for the certification exam, including a fewon XQuery and DB2's pureXML features.

• Encoding: XML Encoding page (W3Schools): See a few examples of issues inencoding documents. Note: Where it refers to "foreign" characters, it appears tomean "non-English" or "non-ASCII."

• Encoding: DB2 Information Online, Unicode character encoding: Find a shortintroduction to Unicode and its encodings (UCS-2, UTF-8, UTF-16).

• Encoding: DB2 Information Center, Unicode implementation in DB2 Databasefor Linux, UNIX, and Windows:



http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0004670.htm

http://www.ibm.com/developerworks/xml

http://www.ibm.com/developerworks/db2/zones/db2udb/

http://www.ibm.com/developerworks/edu/dm-dw-dm-0608cao7-i.html

http://www.ibm.com/developerworks/edu/dm-dw-dm-0608cao7-i.html

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0603saracco2/

http://www.w3schools.com/xml/default.asp

http://www.w3schools.com/xml/xml_technologies.asp



http://www.w3schools.com/xml/xml_encoding.asp





• Encoding: DB2 Information Online, Entering Unicode literals.

• Encoding: Unicode.org:

• Glossary

• Browse and search Unicode code points. The characters for English andEuropean languages are under "Latin."

• Chapter 3 of The Unicode Standard, Version 4.0, issued by the UnicodeConsortium. Tables 3-4 and 3-5 contain conversion algorithms and "bitdistributions" (a table that shows how to rearrange the bits of a code pointinto a UTF character code).

• Sample C code to convert between encodings.

• Encoding: JavaScript that converts Unicode code points to UTF-8.

• Encoding: The official character set names are maintained by the InternetAssigned Numbers Authority (IANA), and are listed athttp://www.iana.org/assignments/character-sets.

• Encoding: Character sets and code pages:

• Code page 437.

• Code page 1252, which Microsoft also calls "Latin-I."

• Character set iso-8859-1, also called Latin-1.

• Other Windows code pages.

• Encoding: IBM Globalize your On Demand Business page: Learn about theInternational Components for Unicode (ICU), "the premier library for softwareinternationalization." This page includes code charts, demos and downloadablecode in C, Java and JNI (Java Native Interface) for components to manipulateUnicode.

• Encoding:The Document Character Set (w3.org, HTML DocumentRepresentation): See how to specify the character encoding of an HTMLdocument and how to represent characters outside of this encoding withcharacter references.

• XML, Encoding, Whitespace: The World Wide Web Consortium (W3C orw3.org): Extensible Markup Language (XML) 1.0 (Fourth Edition). The XMLstandard. Sections relevant to this tutorial include:

• 2.10 White Space Handling




http://www.unicode.org/glossary/

http://www.unicode.org/charts/

http://www.unicode.org/versions/Unicode4.0.0/ch03.pdf#G7404

ftp://www.unicode.org/Public/PROGRAMS/CVTUTF/

http://macchiato.com/unicode/chart/

http://www.iana.org/assignments/character-sets

http://www.microsoft.com/globaldev/reference/oem/437.mspx

http://www.microsoft.com/globaldev/reference/sbcs/1252.mspx

http://www.microsoft.com/globaldev/reference/iso/28591.mspx

http://www.microsoft.com/globaldev/reference/cphome.mspx

http://www.ibm.com/software/globalization/icu/index.jsp

http://www.w3.org/TR/html4/charset.html#h-5.1

http://www.w3.org/TR/REC-xml/

http://www.w3.org/TR/REC-xml/#sec-white-space


• 2.11 End-of-Line Handling

• 2.8 Prolog and Document Type Declaration

• 3.3.3 Attribute-Value Normalization: Processing of whitespace in an attribute

• 4.3.3 Character Encoding in Entities, Encoding Declaration

• Appendix D: Expansion of Entity and Character References

• Appendix F: Autodetection of Character Encodings (Non-Normative):Processing internal encoding: BOM and encoding attribute of the xmldeclaration

• Validation, Whitespace: DB2 Information Online, XML validation.

• Encoding: Unicode for Programmers (draft), including Unicode in Java: See howto write Unicode literals in Java; specify the encoding, both of the source (to thecompiler) and of data files and strings (to the various methods for reading, writing,converting and processing); read and write Unicode; and choose methods to readand write Unicode data.

• DB2, XML: "DB2 9 pureXML Guide" (IBM Redbook, January 2007): Chapter 6 is"Application Development." The content overlaps with the DB2 InformationCenter on-line documentation, but this Redbook conveniently gathersXML-related information in one place.

• DB2, XML, Encoding: IBM Publication - DB2 9.1 XML Guide (document IDSC10-4254; August 16, 2006), found in the IBM Publications Center. Selectcountry; select Search for publications; search on publication numberSC10-4254. You may order a hard copy or download a PDF file. Chapter 7 is"Application Development," which covers mostly the same material as theRedbook "DB2 9 pureXML Guide." The Redbook is newer, but only thisdocument covers XML Encoding, in Chapter 8.

• DB2, Application Development: "DB2 Express-C: The Developer Handbook forXML, PHP, C/C++, Java, and .NET" (IBM Redbook, September 2006): Learn thefundamentals of DB2 application development with DB2 Express-C. Chapter 2 is"Application development with DB2 pureXML."

• DB2, XQuery: "DB2 Developer Workbench, Part 3: Developer Workbench andXML" (developerWorks, October 2006): Learn how the DWB resources,perspectives, views, editors, and wizards assist you to work with the XMLfunctionality in DWB.

• DB2: Tutorial: "Create XML-based stored procedures using the DB2 Developer



http://www.w3.org/TR/REC-xml/#sec-line-ends

http://www.w3.org/TR/REC-xml/#sec-prolog-dtd

http://www.w3.org/TR/REC-xml/#AVNormalize

http://www.w3.org/TR/REC-xml/#NT-EncodingDecl

http://www.w3.org/TR/REC-xml/#sec-entexpand

http://www.w3.org/TR/REC-xml/#sec-guessing

http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.apdv.embed.doc/doc/c0023539.htm

http://www.jorendorff.com/articles/unicode/index.html

http://www.jorendorff.com/articles/unicode/java.html

http://www.redbooks.ibm.com/abstracts/SG247315.html?Open

http://www.elink.ibmlink.ibm.com/publications/servlet/pbi.wss



http://www.ibm.com/developerworks/edu/dm-dw-dm-0610eaton-i.html

http://www.ibm.com/developerworks/edu/dm-dw-dm-0610eaton-i.html


Workbench" (developerWorks, August 2006): Create a stored procedure thatretrieves XML data using IBM DB2 9 Developer Workbench. Take a look at howDB2 DWB provides a comprehensive development environment for creating,editing, debugging, deploying, and testing DB2 stored procedures.

• DB2, DBA, Indexing: DB2 Information Center, Common XML indexing issues.

• Browse the technology bookstore for books on these and other technical topics.

Get products and technologies

• DB2 Express-C: Download a no-charge version of DB2 Express Edition for thecommunity that offers the same core data features as DB2 Express Edition andprovides a solid base to build and deploy applications.

• Application Development: Microsoft Visual C++ Express: Download a free Ccompiler. To write win32 (as opposed to .NET) applications, you need todownload and install Visual C++ 2005 Express Edition with the Microsoft PlatformSDK.

• Application Development: Cygwin: Download a free Linux-like environment forWindows including a shell.

• Application Development: Vim ("VI Improved"): Download a charity-ware vi-likeeditor. VIM colors and auto-indents code, including XML, which can make iteasier read, write and spot mistakes.

• Download IBM trial software and get your hands on application development toolsand middleware products from DB2®, Lotus®, Rational®, Tivoli®, andWebSphere®.

Discuss

• Check out developerWorks blogs and get involved in the developerWorkscommunity.

About the author

Donald E. PayneDonald Payne is an Advisory IT Specialist with IBM. He has consulted for customersand taught computer classes on relational databases and extensibility. He first workedwith XML in 2001. He has assisted early customers using the pureXML features ofDB2 V9.



http://www.ibm.com/developerworks/edu/dm-dw-dm-0608balani2-i.html


http://www.ibm.com/developerworks/apps/SendTo?bookstore=safari

http://www.ibm.com/developerworks/downloads/im/udbexp/index.html?S_TACT=105AGX19&S_CMP=db2cert

http://msdn.microsoft.com/vstudio/express/downloads/

http://msdn.microsoft.com/vstudio/express/visualc/usingpsdk/default.aspx

http://msdn.microsoft.com/vstudio/express/visualc/usingpsdk/default.aspx

http://www.cygwin.com/

http://www.vim.org/

http://www.ibm.com/developerworks/downloads/?S_TACT=105AGX11&S_CMP=db2cert

http://www.ibm.com/developerworks/blogs/

http://www.ibm.com/developerworks/community

http://www.ibm.com/developerworks/community
