DB2 Version 9.7 for Linux, UNIX, and Windows

XML schema to SQL types compatibility for annotated schema decomposition

Annotated XML schema decomposition enables XML values to be stored in columns of tables. XML values can be decomposed only into compatible SQL columns. The following table lists which XML schema types are compatible with which SQL column types.

Table 1. Compatible XML schema and SQL data types
XML schema type	SQL type
XML schema type	S M A L L I N T	I N T E G E R	B I G I N T	R E A L	D E C I M A L	D O U B L E	D E C F L O A T ( 1 6 )	D E C F L O A T ( 3 4 )	D A T E	T I M E	T I M E S T A M P	C H A R	V A R C H A R	L O N G V A R C H A R	C L O B	G R A P H I C	V A R G R A P H I C	L O N G V A R G R A P H I C	D B C L O B	C H A R F B D^†	B L O B	V A R C H A R F B D^†	L O N G V A R C H A R F B D^†
string, normalizedString, token	1	1	1	1	1	1	1	1	2	3	4	6	5	5	5	6a	5a	5a	5a	7a	7	7	7
base64Binary, hexBinary	-	-	-	-	-	-	-	-	-	-	-	8a	8	8	8	-	-	-	-	8c	8b	8b	8b
byte, unsigned byte	0a	0a	0a	0a	0a	0a	0a	0a	-	-	-	9a*	9*	9*	9*	-	-	-	-	-	-	-	-
integer, positiveInteger, negativeInteger, nonNegativeInteger, nonPositiveInteger	10	10	10	11	11	11	10	10	-	-	-	9a*	9*	9*	9*	-	-	-	-	-	-	-	-
int	10	0a	0a	11	11	0a	0a	0a	-	-	-	9a*	9*	9*	9*	-	-	-	-	-	-	-	-
unsignedInt	10	10	0a	11	11	0a	0a	0a	-	-	-	9a*	9*	9*	9*	-	-	-	-	-	-	-	-
long	10	10	0a	11	11	11	10	0a	-	-	-	9a*	9*	9*	9*	-	-	-	-	-	-	-	-
unsignedLong	10	10	10	11	11	11	10	0a	-	-	-	9a*	9*	9*	9*	-	-	-	-	-	-	-	-
short	0a	0a	0a	0a	0a	0a	0a	0a	-	-	-	9a*	9*	9*	9*	-	-	-	-	-	-	-	-
unsignedShort	10	0a	0a	0a	0a	0a	0a	0a	-	-	-	9a*	9*	9*	9*	-	-	-	-	-	-	-	-
decimal	21	21	21	11	11	11	11	11	-	-	-	9a*	9*	9*	9*	-	-	-	-	-	-	-	-
float	22	22	22	17	16	17	0a	0a	-	-	-	9a*	9*	9*	9*	-	-	-	-	-	-	-	-
double	22	22	22	16	16	17	11	11	-	-	-	9a*	9*	9*	9*	-	-	-	-	-	-	-	-
boolean	12	12	12	12	12	12	12	12	-	-	-	9a*	9*	9*	9*	-	-	-	-	-	-	-	-
time	-	-	-	-	-	-	-	-	-	14	-	13a*	13*	13*	13*	-	-	-	-	-	-	-	-
dateTime	-	-	-	-	-	-	-	-	15	15	19	13a*	13*	13*	13*	-	-	-	-	-	-	-	-
duration, gMonth, gYear, gDay, gMonthDay, gYearMonth	-	-	-	-	-	-	-	-	-	-	-	13a	13	13	13	-	-	-	-	-	-	-	-
date	-	-	-	-	-	-	-	-	20	-	-	13a*	13*	13*	13*	-	-	-	-	-	-	-	-
Name, NCName, NOTATION, ID, IDREF, QName, NMTOKEN, ENTITY	-	-	-	-	-	-	-	-	-	-	-	6	5	5	5	6a	5a	5a	5a	7a	7	7	7
ENTITIES, NMTOKENS, IDREFS, list types	-	-	-	-	-	-	-	-	-	-	-	6b	5b	5b	5b	6c	5c	5c	5c	7c	7b	7b	7b
anyURI	-	-	-	-	-	-	-	-	-	-	-	18a	18	18	18	-	-	-	-	7a	7	7	7
language	-	-	-	-	-	-	-	-	-	-	-	6	5	5	5	-	-	-	-	7a	7	7	7
anySimpleType, union types	-	-	-	-	-	-	-	-	-	-	-	6d	5d	5d	5d	6e	5e	5e	5e	7e	7d	7d	7d
anyType	-	-	-	-	-	-	-	-	-	-	-	6d	5d	5d	5d	6e	5e	5e	5e	7e	7d	7d	7d

Legend

^†: FOR BIT DATA
*: The db2-xdb:normalization annotation is used to determine the format of the string that is inserted into the database.
–: Data types are not compatible for annotated XML schema decomposition.
0: Data types are compatible.
0a: Compatible, and where -0 is in the value space of the XML type, -0 is stored as 0 in the database.
1: Compatible if the string is in an acceptable lexical form for the target SQL type and can be converted to a numeric value in the range of the SQL type. Loss of significant digits can occur.
2: Compatible if the string is of a valid date format: yyyy-mm-dd, mm/dd/yyyy, or dd.mm.yyyy.
3: Compatible if the string is of a valid time format: hh.mm.ss, hh:mm AM or PM, or hh:mm:ss.
4: Compatible if the string is of a valid timestamp format: yyyy-mm-dd-hh.mm.ss.nnnnnn or yyyy-mm-dd hh.mm.ss.nnnnnn.
5: Compatible if the length of the XML input string, in bytes, is less than or equal to the length of the target column in bytes. If the input string is longer than the target column, then the string is compatible only if db2-xdb:truncate is set to "true" or "1" for this column mapping. String length is computed after normalization, where the input string is normalized according to the whitespace facet of the XML schema type.
5a: Compatible according to the conditions described in 5. Additionally, the input string must be composed of double-byte characters.
5b: Compatible according to the conditions described in 5. Additionally, the value that is inserted into the target column is the string of concatenated list items, each separated by a single space (in accordance with the "collapse" whitespace facet for lists).
5c: Compatible according to the conditions described in 5a. Additionally, the value that is inserted into the target column is the string of concatenated list items, each separated by a single space (in accordance with the "collapse" whitespace facet for lists).
5d: Compatible if the length of the XML input string, in bytes, is less than or equal to the length of the target column in bytes. If the input string is longer than the target column, then the string is compatible only if db2-xdb:truncate is set to "true" or "1" for this column mapping. The value that is inserted into the target column in either case is the character content of the element or attribute.
5e: Compatible according to the conditions described in 5d. Additionally, The input string must be composed of double-byte characters.
6: Compatible if the length of the XML input string, in bytes, is less than or equal to the length of the target column in bytes. If the input string is longer than the target column, then the string is compatible only if db2-xdb:truncate is set to "true" or "1" for this column mapping. String length is computed after normalization, where the input string is normalized according to the whitespace facet of the XML schema type. If the length of the input XML string is less than the defined length of the target column, then the string is right-padded with blanks when inserted.
6a: Compatible according to the conditions described in 6. Additionally, the input string must be composed of double-byte characters.
6b: Compatible according to the conditions described in 6. Additionally, the value that is inserted into the target column is the string of concatenated list items, each separated by a single space (in accordance with the "collapse" whitespace facet for lists).
6c: Compatible according to the conditions described in 6a. Additionally, the value that is inserted into the target column is the string of concatenated list items, each separated by a single space (in accordance with the "collapse" whitespace facet for lists).
6d: Compatible if the length of the XML input string, in bytes, is less than or equal to the length of the target column in bytes. If the input string is longer than the target column, then the string is compatible only if db2-xdb:truncate is set to "true" or "1" for this column mapping. The value that is inserted into the target column in either case is the character content of the element or attribute. If the length of the input XML string is less than the defined length of the target column, then the string is right-padded with blanks when inserted.
6e: Compatible according to the conditions described in 6d. Additionally, The input string must be composed of double-byte characters.
7: Compatible if the length of the XML input string, in bytes, is less than or equal to the length of the target column in bytes. If the input string is longer than the target column, then the string is compatible only if db2-xdb:truncate is set to "true" or "1" for this column mapping. String length is computed after normalization, where the input string is normalized according to the whitespace facet of the XML schema type.
7a: Compatible according to the conditions described in 7. Additionally, if the length of the input XML string is less than the defined length of the target column, then the string is right-padded with blanks when inserted.
7b: Compatible according to the conditions described in 7. Additionally, the value that is inserted into the target column is the string of concatenated list items, each separated by a single space (in accordance with the "collapse" whitespace facet for lists).
7c: Compatible according to the conditions described in 7b. Additionally, if the length of the input XML string is less than the defined length of the target column, then the string is right-padded with blanks when inserted.
7d: Compatible if the length of the XML input string, in bytes, is less than or equal to the length of the target column in bytes. If the input string is longer than the target column, then the string is compatible only if db2-xdb:truncate is set to "true" or "1" for this column mapping. The value that is inserted into the target column in either case is the character content of the element or attribute.
7e: Compatible according to the conditions described in 7d. Additionally, if the length of the input XML string is less than the defined length of the target column, then the string is right-padded with blanks when inserted.
8: Compatible if the length of the XML input string, in bytes, is less than or equal to the length of the target column in bytes. If the input string is longer than the target column, then the string is compatible only if db2-xdb:truncate is set to "true" or "1" for this column mapping. The encoded (original) string is inserted.
8a: Compatible according to the conditions described in 8. Additionally, if the length of the input XML string is less than the defined length of the target column, then the string is right-padded with blanks when inserted.
8b: Compatible if the length of the XML input string, in bytes, is less than or equal to the length of the target column in bytes. If the input string is longer than the target column, then the string is compatible only if db2-xdb:truncate is set to "true" or "1" for this column mapping. The value that is inserted into the target column is the decoded string.
8c: Compatible according to the conditions described in 8b. Additionally, if the length of the input XML string is less than the defined length of the target column, then the string is right-padded with blanks when inserted.
9: Compatible if the length of the XML input string, computed after processing according to the db2-xdb:normalization setting, is less than or equal to the length of the target column. Also compatible if db2-xdb:truncate is set to "true" or "1" for this column mapping.
9a: Compatible according to the conditions described in 9. Additionally, if the length of the input XML string is less than the defined length of the target column, then the string is right-padded with blanks when inserted.
10: Compatible if the XML type is in the range of the SQL type. Where -0 is in the value space of the XML type, -0 is stored as 0 in the database.

11: Compatible if the XML value is in the range of the SQL type. Loss of significant digits can occur. Where -0 is in the value space of the XML type, -0 is stored as 0 in the database.

12: Compatible, and the value inserted is '0' (for false) or '1' (for true).
13: Compatible if the length of the XML input string, computed after processing according to the db2-xdb:normalization setting, is less than or equal to the length of the target column. Also compatible if db2-xdb:truncate is set to "true" or "1" for this column mapping.
13a: Compatible according to the conditions described in 13. Additionally, if the length of the input XML string is less than the defined length of the target column, then the string is right-padded with blanks when inserted.
14: For XML values that contain subseconds, compatible only if the decomposition annotation specifies db2-xdb:truncate as "true" or "1". For XML values with time zone indicators, compatible if db2-xdb:truncate is set to "true" or "1"; values are inserted without the time zone.
15: Compatible if the year is composed of four digits and is not preceded by the '-' sign. Compatible if the XML value does not have a time zone indicator. If the XML value has a time zone indicator, then the values are compatible if db2-xdb:truncate is set to "true" or "1".
16: Compatible if the value is in the range of the SQL type and is not "INF", "-INF" or "NaN". Where -0 is in the value space of the XML type, -0 is stored as 0 in the database. Loss of significant digits can occur.
17: Compatible if the value is not "INF", "-INF" or "NaN". Where -0 is in the value space of the XML type, -0 is stored as 0 in the database.
18: Compatible if the string length of the URI, in bytes, is less than or equal to the length of the target column in bytes. If the input string is longer than the target column, then the string is compatible only if db2-xdb:truncate is set to "true" or "1" for this column mapping. Note that the URI itself, not the resource the URI points to, is inserted.
18a: Compatible according to the conditions described in 18. Additionally, if the length of the input XML string is less than the defined length of the target column, then the string is right-padded with blanks when inserted.
19: Compatible if the year is composed of four digits and is not preceded by the '-' sign. For XML values with time zone indicators, compatible if db2-xdb:truncate is set to "true" or "1". (Values are inserted without the time zone in this case.) If subseconds are specified with more than six digits, compatible if db2-xdb:truncate is set to "true" or "1".
20: Compatible if the year is composed of four digits and is not preceded by the '-' sign. For XML values with time zone indicators, compatible if db2-xdb:truncate is set to "true" or "1". (Date values are inserted without the time zone in this case.)
21: The fractional part of the number is truncated. Compatible if the whole part is in the range of the SQL type. Where -0 is in the value space of the XML type, -0 is stored as 0 in the database.
22: The fractional part of the number is truncated. Compatible if the whole part is in the range of the SQL type, and the value is not "INF", "-INF" or "NaN". Where -0 is in the value space of the XML type, -0 is stored as 0 in the database.