DB2 Version 9.7 for Linux, UNIX, and Windows

Using XML in a non-Unicode database

Beginning with Version 9.5, XML data can be stored and retrieved from a database that does not use a Unicode code page.

Internally, XML data is always managed by the DB2® database server in a Unicode format, regardless of the database code page. Non-XML, relational data is managed in the database code page. In cases where SQL or XQuery statements involve both XML data and SQL relational data, such as in the casting of one data type to another, or in comparisons involving both the XML data type and SQL data types, code page conversion is often required. Comparisons of XML data to XML data do not require code page conversion because both sets of data are already in UTF-8 format. Similarly, comparisons of SQL data to SQL data do not require code page conversion because both sets of data are already in the database code page.

For operations involving XML data and SQL data, the need for code page conversion is eliminated in a Unicode database because the database uses the same encoding for all data types. In a non-Unicode database, however, operations that involve code page conversion can potentially result in the corruption or loss of data. If the XML data undergoing conversion contains characters with code points that are not part of the database code page, character substitution occurs. As a result, casting or comparison operations can have an unexpected outcome, and XML data retrieved from the database may contain incorrect values. Below are discussed different means of avoiding code page conversion problems so as to ensure the integrity of stored XML data and the operations that involve it.

XML document insertion and code page conversion

Whenever XML data is inserted into a DB2 database server through a host variable or parameter marker that has a character data type (a data type of CHAR, VARCHAR or CLOB that is not a FOR BIT DATA type), code page conversion occurs if the database code page differs from that of the client or application issuing the request. A second conversion occurs as the inserted character data is converted from the database code page to Unicode, the format in which XML data is managed internally.

The following table shows the various possible encoding combinations between a database and an XML document string inserted from a client or application. The XML document encoding is the same as the client code page because the client inserts the XML data via a character data type. For each combination, the implications for code page conversion and the possibility of resultant character substitution during XML document insertion are described.

Table 1. Encoding scenarios between a database and an inserted XML document string
Scenario	XML document encoding	Database encoding	Code pages match?
1.	Unicode (UTF-8)	Unicode (UTF-8)	yes
2.	Non-Unicode	Unicode (UTF-8)	no
3.	Non-Unicode	Non-Unicode	yes
4.	Unicode (UTF-8)	Non-Unicode	no
5.	Non-Unicode	Non-Unicode	no

In Scenario 1, an XML document and a database share a Unicode encoding. No character conversion occurs as the XML document is inserted. It is always safe to insert XML data in this way.
In Scenario 2, a non-Unicode XML document is converted to UTF-8 for insertion into a Unicode database. No character substitution occurs through this process. It is always safe to insert XML data in this way
In Scenario 3, an XML document and a database share the same non-Unicode encoding. In this case, the XML document can contain only code points that are part of the database code page, so no character substitution occurs during code page conversion. It is always safe to insert XML data in this way
In Scenario 4, a Unicode XML document is inserted into a non-Unicode database. Code page conversion occurs if the XML document is inserted from a UTF-8 client or application through either a host variable or a parameter marker that has a character data type. Any characters in the XML document that do not have matching code points in the database code page will be substituted.
In Scenario 5, an XML document is inserted into a database server where the two have different encodings, neither of which is UTF-8. In this case, like in Scenario 4, if the XML document is inserted using a character data type, character substitution occurs in any case where the XML document contains characters that are not valid in the database code page.

Safely inserting XML data into a non-Unicode database

The safest way to ensure the integrity of XML data is to use a Unicode database. However, if this is not possible, there are other ways to prevent character substitution from occurring. The following list describes various methods of inserting XML data safely, whether or not a Unicode database is used:

Use a Unicode database, or ensure that the database and client use the same encoding

As illustrated in Table 1, code page conversion issues for XML data are always avoided when:

The database is Unicode
The database and the client share the same encoding, whether or not this is Unicode

Avoid using a host variable or parameter marker with a character data type

When a Unicode database cannot be used, code page conversion of XML data can also be avoided by binding the XML data using a host variable or parameter marker of type XML or any binary data type. That is, specifying a data type other than CHAR, VARCHAR, or CLOB for the XML data allows it to be passed directly from the client or application code page to Unicode, bypassing conversion to the database code page.

The ENABLE_XMLCHAR configuration parameter allows you to control whether or not inserting is allowed via character data types. Setting ENABLE_XMLCHAR to “NO” will block the usage of character data types during XML document insertion, preventing possible character substitution and ensuring the integrity of stored XML data. The BLOB and FOR BIT DATA types are still allowed, since these data types are safe from code page conversion. By default, ENABLE_XMLCHAR is set to “YES” so that insertion of character data types is allowed.

When a Unicode database is used code page conversion is never a problem, so in this case the ENABLE_XMLCHAR configuration parameter has no effect; character data types can be used for XML document insertion regardless of the setting for ENABLE_XMLCHAR.

Use character entity references for characters that are not in the database code page

In cases where code page conversion cannot be avoided and a character data type must be used for the XML data stream, it is best to ensure that all characters in the XML document have matching code points in the database code page. For any characters in the XML data that do not have matching code points in the target database you can use a character entity reference to specify the Unicode code point of the character. Code page conversion is always bypassed for character entity references, so that the correct character is preserved in the XML data. For example, the character entity references &#X003E; and > are, respectively, the hexadecimal and decimal equivalents of the greater than sign (">").

Querying XML data in a non-Unicode database

As with inserting XML data into a database, the safest way to ensure data integrity during a query involving XML data is to use a Unicode database. If this is not possible, character substitution is avoided by making sure that all XML data is representable in the database code page, or by using character entity references for characters that are not in the database code page.

In the event that a query contains XML content that includes characters not representable in the database code page, the following two types of character substitution may occur, potentially causing unexpected results for the query:

Replacement by default substitution character: The default substitution character for the code page is introduced in place of the unmatchable character in the XML data. For example, if a Chinese character is passed into an ASCII encoded database (ISO-8859-1), the original character is substituted with the ASCII code point 0x1A, a control character which typically displays as a question mark ('?') when displayed on a client. When the XML data is converted from the database code page to Unicode, the substitution character is preserved.
Replacement by nearest character equivalent ("folding"): The original input character is replaced by a character in the target code page that is similar, but not necessarily identical, to the original character. It is sometimes the case that two or more characters with distinct Unicode code points map onto a single code point in a database code page (the nearest character equivalent in the target code page), so that after insertion into a database the distinction between the values is lost. This scenario is illustrated in Example 2, below.

Examples

The following examples demonstrate the possible effects of code page conversion when a client or application with a UTF-8 encoding is used to query XML data in a non-Unicode database. In these examples, assume that the database is created using code page ISO8859-7 (Greek). XQuery expressions are used to match XML data stored in table T1, where the stored XML data consists of the Unicode Greek sigma character (Σ_G) and the Unicode mathematical sigma character (Σ_M). The code point 0xD3 identifies the sigma character in the ISO8859-7 database.

The table T1 is created and populated using the following commands:

CREATE TABLE T1 (DOCID BIGINT NOT NULL, XMLCOL XML);
INSERT INTO T1 VALUES (1, XMLPARSE(
       document '<?xml version="1.0" encoding="utf-8" ?> <Specialchars>
	<sigma>Σ_G</sigma>
	<summation>Σ_M</summation>
	</Specialchars>' 
       preserve whitespace));

Example 1: Successful code page conversion (character is representable in database code page)

XQUERY for $test in db2-fn:xmlcolumn("T1.XMLCOL")//*[. = "Σ_G"] return $test

This expression produces the desired result:

<sigma>Σ_G</sigma>

In this case, the expression Σ_G begins at the client as the Unicode code point for the Greek sigma character (U+03A3), is converted to the sigma character in the Greek database code page (0xD3), and is then converted back to the correct Unicode character for XML processing. Because the Greek sigma character is representable in the database code page, the expression matches correctly. This character conversion is shown in the following table:

Table 2. Character data conversion (Example 1)
	Client (UTF-8)		Database (ISO8859-7)		XML parser (UTF-8)
Character	U+03A3 (Greek sigma)	→	0xD3 (Greek sigma)	→	U+03A3 (Greek sigma)

Example 2: Unsuccessful code page conversion (character is not representable in database code page)

XQUERY for $test in db2-fn:xmlcolumn("T1.XMLCOL")//*[. = "Σ_M"] return $test

This expression does not produce the desired result:

<sigma>Σ_G</sigma>

In this case, the expression Σ_M begins at the client as the Unicode code point for the mathematical symbol sigma (U+2211), is converted to the sigma character in the Greek database code page (0xD3) and then matches the Σ_G character when the XML comparison occurs. For the return expression, the process is identical to that in Example 1. The Unicode XML character Σ_G converts first to the sigma character in the Greek database code page (Σ_A), and then back to the Greek sigma character in the client UTF-8 code page (Σ_G). This character conversion is shown in the following table:

Table 3. Character data conversion (Example 2)
	Client (UTF-8)		Database (ISO8859-7)		XML parser (UTF-8)
Character	U+2211 (Mathematical sigma)	→	0xD3 (Greek sigma)	→	U+03A3 (Greek sigma)

Example 3: Bypassing code page conversion using a character entity reference

XQUERY for $test in db2-fn:xmlcolumn("T1.XMLCOL")//*[. = "&#2211;"] 
   return $test

This expression produces the desired result:

<summation>Σ_M</summation>

In this case, the expression Σ_M begins at the client as the Unicode code point for the mathematical symbol sigma (U+2211), and because it is escaped as character reference &#2211, the Unicode code point is preserved when it is passed to the XML parser, allowing for successful comparison against the stored XML value Σ_M. The bypassing of character conversion is shown in the following table:

Table 4. Character data conversion (Example 3)
	Client (UTF-8)		Database (ISO8859-7)		XML parser (UTF-8)
Character	U+2211 (character reference for mathematical sigma)	→	"&#2211" (character reference for mathematical sigma)	→	U+2211 (mathematical sigma)

Example 4: Unsuccessful code page conversion (character is not representable in database code page)

This example is like Example 1, except that here an ASCII encoded database is used and the default substitution character for the code page is introduced into the XML expression.

XQUERY for $test in db2-fn:xmlcolumn("T1.XMLCOL")//*[. = "Σ_G"] return $test

This query fails to match the correct value in table T1. In this case, the Unicode character U+2211 (Greek sigma) does not have a matching code point in the ASCII code page so a default substitution character is introduced, in this case the question mark character ('?'). This character conversion is shown in the following table:

Table 5. Character data conversion (Example 4)
	Client (UTF-8)		Database (ISO8859-1)		XML parser (UTF-8)
Character	U+2211 (mathematical sigma)	→	0x003F ('?')	→	0x003F ('?')