IBM DB2 pureXML: XML Schema Repository and XML Validation

XML documents usually come with their data type definitions attached. Thanks to this information some frameworks can validate them automatically and check the correctness of the data.

Some XML-aware databases too...

IBM DB2 pureXML engine provides not only well-formed validation, but also type definition validation utilities.

It employs the XML Schema Repository (XSR) to leverage the full benefits of XML in a relational database.

Developers can register their schemas (XML Schema and/or DTD) and later-on validate XML documents automatically during execution of INSERT/UPDATE statements.

Some say that XML Schema is equivalent to, or even more powerful than a relational domain constraints mechanism. For example, XML Schema allows you to create reg-ex restriction patterns which are not supported by standard relational databases.

And today I will show you how to use XML validation in IBM DB2 pureXML database.

IBM DB2 Developer Workbench = IBM Data Studio

During writing my Master's Thesis (year 2007) I used Eclipse-based IBM DB2 Developer Workbench. It's new name is IBM Data Studio.

It's a tool dedicated for developing, testing, and deploying database applications for IBM DB2.

The key features are:

• editing, debugging, and running SQL and XQuery statements (graphical tool for creating queries)
• editing, debugging, testing, and deploying UDF and stored procedures (SQL and Java)
• visualising and optimising execution plans
• full support for pureXML technology
• support for SOAP and REST Web Services

IBM Data Studio can be downloaded from here: http://www-01.ibm.com/software/data/studio/.

Deploying XML Schemas to XSR

To deploy XML Schema to XSR you can use IBM DB2 Developer Workbench as as shown here:



It can handle automatically registering, updating, and removing XML Schemas.

Of course the deployment of XSD/DTD files is also possible with the IBM DB2 Control Center tool.

Or if you enjoy text command lines:

REGISTER XMLSCHEMA 'ERS' FROM C:\Studies\MEng\workspace\DataWeaverXML\src\xsd\ea\Patient.xsd AS PATIENT COMPLETE ;

Here is a complete XML Schema I used:

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.dataweaver.org/patient"
xmlns="http://www.dataweaver.org/patient"
elementFormDefault="qualified">
<xs:element name="patient" type="patientType"/>
<xs:element name="firstName" type="firstNameType"/>
<xs:element name="lastName" type="lastNameType"/>
<xs:element name="pesel" type="peselType"/>
<xs:element name="sex" type="sexType"/>
<xs:element name="phone" type="phoneType"/>
<xs:element name="birthDate" type="xs:date"/>
<xs:element name="birthPlace" type="birthPlaceType"/>
<xs:element name="email" type="emailType"/>
<xs:element name="address" type="addressType"/>
<xs:element name="postalCode" type="postalCodeType"/>
<xs:element name="city" type="cityType"/>
<xs:element name="street" type="streetType"/>
<xs:element name="country" type="countryType"/>
<xs:simpleType name="firstNameType">
<xs:restriction base="xs:string">
<xs:minLength value="3"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="lastNameType">
<xs:restriction base="xs:string">
<xs:minLength value="3"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="birthPlaceType">
<xs:restriction base="xs:string">
<xs:minLength value="3"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="cityType">
<xs:restriction base="xs:string">
<xs:minLength value="3"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="streetType">
<xs:restriction base="xs:string">
<xs:minLength value="3"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="countryType">
<xs:restriction base="xs:string">
<xs:minLength value="3"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="peselType">
<xs:restriction base="xs:string">
<xs:length value="11"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="phoneType">
<xs:restriction base="xs:string">
<xs:pattern value="[0-9]{3}(-[0-9]{3}){2}"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="emailType">
<xs:restriction base="xs:string">
<xs:pattern value="([\.a-zA-Z0-9_-])+@([a-zA-Z0-9_-])+(([a-zA-Z0-9_-])*\.([a-zA-Z0-9_-])+)+"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="sexType">
<xs:restriction base="xs:string">
<xs:pattern value="K|M"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="postalCodeType">
<xs:restriction base="xs:string">
<xs:pattern value="[0-9]{2}-[0-9]{3}"/>
</xs:restriction>
</xs:simpleType>
<xs:complexType name="addressType">
<xs:sequence>
<xs:element ref="postalCode"/>
<xs:element ref="city"/>
<xs:element ref="street"/>
<xs:element ref="country"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="patientType">
<xs:sequence>
<xs:element ref="firstName" minOccurs="1" maxOccurs="3"/>
<xs:element ref="lastName"/>
<xs:element ref="pesel"/>
<xs:element ref="sex"/>
<xs:element ref="birthDate"/>
<xs:element ref="birthPlace"/>
<xs:element ref="phone" minOccurs="0" />
<xs:element ref="email" minOccurs="0" />
<xs:element ref="address"/>
</xs:sequence>
</xs:complexType>
</xs:schema>

XML validation

Once the XML Schema is successfully deployed in XSR, you can use it to validate XML
documents before inserting/updating records in the database.

In order to validate an XML document you have to use XMLVALIDATE function. It takes an XML document as an argument (followed by the ACCORDING TO XMLSCHEMA ID YOUR_XML_SCHEMA_NAME phrase):

INSERT INTO ERS_PATIENTS_XML ( PATIENT_INFO )
VALUES ( XMLVALIDATE (
XMLPARSE ( DOCUMENT '
<patient xmlns="http://www.dataweaver.org/patient">
<firstName>Lukasz</firstName>
<lastName>Budnik</lastName>
<pesel>83030712970</pesel>
<sex>M</sex>
<birthDate>1983-03-07</birthDate>
<birthPlace>Wejherowo</birthPlace>
<address>
<postalCode>84-200</postalCode>
<city>Wejherowo</city>
<street>Wczasowa 7</street>
<country>Polska</country>
</address>
</patient>')
ACCORDING TO XMLSCHEMA ID PATIENT ))

Run it from IBM DB2 Control Center or IBM DB2 Developer Workbench:

DB20000I  The SQL command completed successfully.

Now remove the <firstName /> element and run it again. There will be an error:

Parsing or validation did not complete.
User response:
Correct the XML document and try the operation again.
sqlcode: -16196
sqlstate: 2200M

Summary

Now, you know how to

That is not all.

I still have some more information to share about IBM DB2 pureXML.

Stay tuned,
Łukasz