DB2 10.5 for Linux, UNIX, and Windows

xmlcolumn function

The db2-fn:xmlcolumn function retrieves a sequence from a column in the currently connected DB2® database.

Syntax


>>-db2-fn:xmlcolumn(string-literal)----------------------------><

string-literal: Specifies the name of the column from which the sequence is retrieved. The column name must be qualified by a table name, view name, or alias name, and it must reference a column with the XML data type. The SQL schema name is optional. If you do not specify the SQL schema name, the CURRENT SCHEMA special register is used as the implicit qualifier for the table or view. The string-literal is case sensitive. string-literal must use the exact characters that identify the column name in the database.

Returned value

The returned value is a sequence that is the concatenation of the non-null XML values in the column that is specified by string-literal. If there are no rows in the table or view, db2-fn:xmlcolumn returns the empty sequence.

The number of items in the sequence that is returned by the db2-fn:xmlcolumn function can be different from the number of rows in the specified table or view because some of these rows can contain null values or sequences with multiple items.

The db2-fn:xmlcolumn function is related to the db2-fn:sqlquery function, and both can produce the same result. However, the arguments of the two functions differ in case sensitivity. The argument in the db2-fn:xmlcolumn function is processed by XQuery, and so it is case sensitive. Because table names and column names in a DB2 database are in uppercase by default, the argument of db2-fn:xmlcolumn is usually in uppercase. The argument of the db2-fn:sqlquery function is processed by SQL, which automatically converts identifiers to uppercase.

The following function calls are equivalent and return the same results:

db2-fn:xmlcolumn('SQLSCHEMA.TABLENAME.COLNAME')
db2-fn:sqlquery('select colname from sqlschema.tablename')

Examples

Example that returns a sequence of documents: The following function returns a sequence of XML documents that are stored in the XML column DESCRIPTION in the table named PRODUCT, which, for this example, is in the SQL schema SAMPLE.

db2-fn:xmlcolumn('SAMPLE.PRODUCT.DESCRIPTION')

Example that uses an implicit SQL schema: In the following example, the CURRENT SCHEMA special register in a DB2 database is set to SAMPLE, and so the function returns the same results as the previous example:

db2-fn:xmlcolumn('PRODUCT.DESCRIPTION')

Example that uses an SQL delimited identifier: The following function returns a sequence of documents that are stored in the "Thesis" column of the "Student" table, assuming that the table is in the schema currently assigned to CURRENT SCHEMA. Because the table name and column name contain lowercase characters, there are two ways that they can be specified in the string literal argument of the db2-fn:xmlcolumn function:

Specified as SQL-delimited identifiers (enclosed in double quotation marks):
```
db2-fn:xmlcolumn('"Student"."Thesis"')
```
Specified as a string without indication that they are SQL-delimited identifiers:
```
db2-fn:xmlcolumn('Student.Thesis')
```

By contrast, the same table and column information that is used in the db2-fn:sqlquery function is required to use the SQL-delimited identifiers as follows:

db2-fn:sqlquery('select "Thesis" from "Student"')