How to check the information about the XML schemas in the XSR?


DB2 stores information about XML Schemas in a system catalog view SYSCAT.XSROBJECTS. This view can be queried to retrieve information like the object schema, object name, target namespace, and schema location. The actual schema documents are stored in catalog SYSCAT.XSROBJECTCOMPONENT. The SYSCAT.XSROBJECTS catalog view contains 1 row per XML Schema. On the other hand, SYSCAT.XSROBJECTCOMPONENT catalog has 1 row per schema document.
You can use following queries to get the information:

SELECT objectschema, objectname, targetnamespace, schemalocation FROM syscat.xsrobjects
SELECT objectschema, objectname, targetnamespace, schemalocation from syscat.xsrobjectcomponents


A registered schema can be dropped by using Drop XSRObject command. The schema repository does not have a notion of individual schema documents. It only has a notion of an XML Schema, which is a collection of 1 or more schema documents. Thus, to drop an XSR Object implies deleting all schema documents associated with that XSR Object.


Below example generates an "employee" element for each employee. The employee element is associated with XML namespace "urn:bo', which is bound to prefix "bo". The element contains attributes for names and a hiredate subelement.

XMLNAMESPACES('urn:bo' as "bo"), XMLATTRIBUTES(e.lastname, e.firstnme), XMLELEMENT(NAME "bo:hiredate", e.hiredate)) AS CLOB(50)) FROM employee e where e.edlevel = 12;

The result of the query would be similar to the following result:

<bo:employee xmlns:bo="urn:bo" LASTNAME="PARKER" FIRSTNME="JOHN">

Another example: generate two elements for each employee using XMLFOREST. The first "lastname" element is associated with the default namespace "http://hr.org", and the second "job" element is associated with XML namespace "http://fed.gov", which is bound to prefix "d".

SELECT empno, XMLSERIALIZE(XMLFOREST(XMLNAMESPACES(DEFAULT 'http://hr.org', 'http://fed.gov' AS "d"),lastname, job AS "d:job") AS CLOB(50)) FROM employee where edlevel = 12;

The result of the query would be similar to the following result:

<LASTNAME xmlns=http://hr.org xmlns:d="http://fed.gov
">PARKER</LASTNAME><d:job xmlns="http://hr.org" xmlns:d="http://fed.gov">OPERATOR</d:job>
<LASTNAME xmlns="http://hr.org"
xmlns:d="http://fed.gov">SETRIGHT</LASTNAME><d:job xmlns="http://hr.org" xmlns:d="http://fed.gov">OPERATOR</d:job>

MTK can be downloaded at
http://www-306.ibm.com/software/data/db2/migration/mtk/. More information can be found at


Could you please explain how DB2 validate XML documents against DTDs?


You can’t validate XML document against DTDs; DTDs validation not supported. You can validate your XML document against XML schemas only. You can still insert documents that contain a DOCTYPE or that refer to DTDs.

There are tools available, such as those in IBM Rational Application Developer that help you generate XML schemas from various sources, including DTDs, existing tables, or XML documents.

Before you can validate, you must register your XML schema with the built-in XML schema repository (XSR). This process involves registering each XML schema document that makes up the XML schema. Once all XML schema documents have been successfully registered, you must complete the registration. One method of registering an XML schema is through commands.

Register and complete registration of the posample.customer XML schema as follows, providing the absolute path to the sqllib/samples/xml directory on your system:
REGISTER XMLSCHEMA 'http://posample.org' FROM 'file:///<c:/sqllib/samples/xml>customer.xsd' AS posample.customer COMPLETE

You can verify that the XML schema was successfully registered by using follow query:


MTK can be downloaded at
http://www-306.ibm.com/software/data/db2/migration/mtk/. More information can be found at


How to validate XML data with a XML schema while inserting XML data?


The following INSERT statement inserts a new XML document into the Info column of the Customer table, only if the document is valid according to the posample.customer XML schema previously registered.

'<customerinfo xmlns="http://posample.org" Cid="1003">
<name>Robert Shoemaker</name>
<addr country="Canada">
<street>1596 Baseline</street>
<pcode-zip>N8X 7F8</pcode-zip>
<phone type="work">905-555-7258</phone>
<phone type="home">416-555-2937</phone>
<phone type="cell">905-555-8743</phone>
<phone type="cottage">613-555-3278</phone>
</customerinfo>' PRESERVE WHITESPACE )
ACCORDING TO XMLSCHEMA ID posample.customer ))

XMLVALIDATE operates on XML data. Because the XML document in this example is passed as character data, and character data can only be assigned directly to XML in INSERT, UPDATE, or DELETE statements, the XMLPARSE function must be used in this case. The XMLPARSE function parses its argument as an XML document and returns an XML value.

To verify that the validation and insert were successful, query the Info column:

SELECT Info FROM Customer
This query should return three XML documents, one of which is the document just inserted.


What are the advantages of using XSDs validation over DTDs?


SQL/XML scores over XQuery in below areas:

i) If your query need to return data from relational columns and from XML columns at the same time.
ii) If your query require full-text search conditions.
iii) If you want results returned as sets and missing XML elements represented with nulls.
iv) If you want to use parameter markers, because XQuery does not supports external parameters.
v) SQL/XML is good for applications that need to integrate relational and XML data. It provides the easiest means to join XML data and relational data.
vi) SQL/XML is good for grouping and aggregating of XML. The XQuery language does not provide an explicit group-by construct. Although grouping and aggregation can be expressed in XQuery using self-joins, it is quite awkward.


What are XML schema definition (XSDs) language built-in datatypes?


XSDs built-in datatypes are those which are defined below, and can be either primitive or derived.
Conceptually there is no difference between the built-in and derived datatypes.

Built-in primitive datatypes:
i) duration, dateTime, time, date, gYearMonth, gYear, gMonthDay, gDay, gMonth, boolean, base64Binary, hexBinary, float, double, anyURL, QName, NOTATION, string, decimal.

Built-in derived datatypes:
ii) normalizedString, integer, token, nonPositiveInteger, long, nonNegativeInteger, language, Name, NMTOKEN, negativeInteger, int, unsignedLong, positiveInteger, NCName, NMTOKENS, short, unsignedInt, ID, IDREF, ENTITY, IDREFS, ENTITIES, byte, unsignedShort, unsignedByte.

Each built-in datatype can be uniquely addressed via a URL reference constructed as below:

i) the base URL is the URL of the XML Schema namespace
ii) the fragment identifier is the name of the datatype or facet definition element

For example, to address the "int" datatype, the URL is:


For example, to address the "maxInclusive" facet, the URL is:


Each facet usage in a built-in datatype definition can be uniquely addressed via a URL constructed as follows:

The fragment identifier is the name of the datatype, followed by a period (".") followed by the name of the facet
For example, to address the usage of the “maxInclusive” facet in the definition of “int”, the URL is:


You can read more at:


I want to validate my XML data by creating a new XML schema. Can I get a working example to create a new XML schema?


In this example XML Schema is written to validate the below XML document:

		<name>electron tube light</name>	
		<barcode>102.54</ barcode>

First we need to write a XML Schema Definition (XSD) file: electronicStockPrice.xsd

<?xml version="1.0" encoding="ISO-8859-1"?> 
<xs:schema targetNamespace="http://wwww.mycompany.com/products" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 

<xsd:simpleType name='BarcodeType'>
    <restriction base='string'>
      <pattern value='\d{3}-[A-Z]{2}'/>
<xsd:simpleType name="PriceType">
	<xsd:restriction base="xsd:decimal">	
		<xsd:minInclusive value="0"/>
		<xsd:maxInclusive value="100000"/>
		<xsd:totalDigits value="9"/>
		<xsd:fractionDigits value="3"/>		
<xsd:complexType name="StockPriceType"> 
		<xsd:element name="name" type="xsd:string">
		<xsd:element name="ask" type="PriceType">		
		<xsd:element name="bid" type="PriceType">		
		<xsd:element name="barcode" type="BarcodeType">				
<xsd:element name="electronicStockPrice" type="StockPriceType">

The next step is to register and complete registration of the XML schema as follows, providing the absolute path to the sqllib/samples/xml directory on your system and a URL com.mycompany.products :

REGISTER XMLSCHEMA http://wwww.mycompany.com/products FROM 'file:///<c:/sqllib/samples/xml> electronicStockPrice.xsd' AS com.mycompany.products COMPLETE


I have a problem while validating “end_date” element of XML data. How do I validate “end_date” element of XML data? Did anyone face this problem before?


This is a type of check constraint ("END_DATE > START_DATE") that cannot be validated using only resources of the XML schema. In order to solve this problem, the best alternative is to use the Schematron rule based language. The link between XML schema and the Schematron is done using the annotation/appinfo element.

In below example we can see the definition of END_DATE element with the integrity of Schematron notation, the check constraint is ("END_DATE > START_DATE").

<xsd:element name="END_DATE" type="DATE" nillable="true"> 
	  <sch:pattern name="Check constraint end_date > start_date"> 
	        <sch:rule context="END_DATE"> 
<sch:assert test="( ./@xsi:nil='true' or (number(translate(./text(),'-','')) >  number(translate(../START_DATE/text(),'-',''))))">  
				END_DATE must be greater than START_DATE.

The business rule is revealed with the use of an XPATH expression in the selected context END_DATE.
To validate the Schematron rule it is necessary to use an XSLT processor (for example, the saxon).

The list below shows the necessary steps in validating the Schematron rules using the saxon.

i) saxon new_project.sch XSD2SCHTRN.XSL > proj_sch.xsd
ii) saxon proj_sch.xsd schematron-basic.xsl > validator.xsl
iii) saxon project.xml validator.xsl > result2.txt

You can find more information at:


I am working on project that uses Java. I want to know mapping between DB2 data types and XML data types. Can you give me the list of data type mapping?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.