 | Level: Intermediate Farzad Farahbod (ffarahbo@us.ibm.com), Technical Lead, IBM DB2 .NET XML and LINQ Tooling, IBM
30 Nov 2006
A repository, such as a relational database, provides the environment to securely share XML documents and XML schemas with authorized users. The authorized users have secure access to important XML documents and XML schemas at any time, from anywhere. By using a repository system that can contain one or more relational database systems, users can locate and retrieve the latest version of XML document(s) and XML schema documents. DB2® 9 offers new support for XML, which .Net application developers can start to use out of the box with minimal effort. That's because DB2 9 enables programmers to query, insert, update, and delete XML data in the same fashion as relational data — using familiar ADO .Net statements and standard query language.
This article shows you detailed examples of how to use .Net to build applications that uses DB2's XML technology, which allows XML to be stored, validated, and queried in its hierarchical format.
XML support in DB2
DB2 9 introduces new features and mechanisms for managing, storing, and querying XML data:
- XML data type enables DB2 to store XML documents in it’s native hierarchical format.
- XML query language support is based on industry standards, including new XML extensions to SQL (also called SQL/XML).
- Support for validating XML data based on user-supplied schemas, which allows application developers and database administrators to enforce data integrity constraints for XML data stored in DB2. The DB2 Visual Studio 2005 Add-in is used for the examples in this article.
Code samples in this article refer to the CARPOOL table, which tracks carpool information for San Francisco and San Jose. Listing 1 shows how this table is defined. Also ensure that the database is XML-enabled.
Environment setup
Note: You need to ensure that your SAMPLE database was created correctly with XML enabled (see below).
You need Visual Studio .NET 2005 and DB2 9. The installation of these products should be straightforward. It is suggested that you install Visual Studio .NET first, and then DB2 9. You might want to take note of the user ID and password that you enter for the DB2 installation, since you can choose to use them later for connecting to DB2.
During DB2 installation, ensure TCP/IP is enabled. If you are not sure whether TCP/IP is enabled after your DB2 installation, perform the following to check:
-
From your Windows Start menu, navigate to Programs > IBM DB2 > DB2 (default) [or your DB2 instance name] > Command Line Tools > Command Window.
-
In the DB2 command window, you should see the command prompt
C:\Program Files\IBM\SQLLIB\BIN> (if you took the default installation path). Enter db2set. On the return, you should see the line DB2COMM=tcpip.
If your TCP/IP is not enabled for DB2, enable it by entering the following commands:
-
db2set db2comm=tcpip
-
db2 update dbm cfg using svcename 50000
-
db2stop
-
db2start
After DB2 installation, you are given a choice to create the DB2 SAMPLE database. Do so, accept the defaults, but be sure to select the XML and SQL objects and data option.
To check whether your system setup is successful, start Visual Studio .NET 2005. From the Visual Studio .NET, navigate to File > New > Project. In the New Project dialog box, you should see IBM Projects in the left panel. Close the dialog box. In the Server Explorer, connect to the DB2 SAMPLE database (for these steps, "Develop proof-of-concept .NET applications, Part 1: Create database objects in DB2 Viper using .NET" (developerWorks, May 2006). Make sure that you see the XML Schema Repository tree node in the Server Explorer. If you do not see it, you might have to recreate your SAMPLE database to enable the XML features.
Listing 1. CARPOOL table definition
CREATE TABLE CARPOOL (
ID INTEGER NOT NULL,
FIRSTNAME VARCHAR ( 256 ) ,
LASTNAME VARCHAR ( 256 ) ,
TITLE VARCHAR ( 15 ) ,
PHONE VARCHAR ( 20 ) ,
CARPOOLINFO XML ,
CONSTRAINT CARPOOL_PK PRIMARY KEY (ID))
|
We have two XML schemas, CarpoolInfo.xsd and USAddressType.xsd, where CarpoolInfo.xsd is referencing on USAddressType.
Listing 2. XML schema to be used for validating the XML document in CARPOOL table (CarpoolInfo.xsd)
<?xml version="1.0" encoding="utf-8"?>
<xs:schema targetNamespace="http://tempuri.org/XMLSchema.xsd"
elementFormDefault="qualified" xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:include id="USAddress.xsd" schemaLocation="USAddress.xsd" />
<xs:element name="CARPOOLINFO">
<xs:complexType>
<xs:sequence>
<xs:element name="StartTime" type="xs:string" />
<xs:element name="Address" type="USAddress" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
|
Listing 3. CarpoolInfo's dependent XML schema (USAddress.xsd)
<?xml version="1.0" encoding="utf-8"?>
<xs:schema targetNamespace="http://tempuri.org/XMLSchema.xsd"
elementFormDefault="qualified" xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:mstns="http://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="USAddress">
<xs:sequence>
<xs:element name="Add1" type="xs:string">
</xs:element>
<xs:element name="Add2" type="xs:string">
</xs:element>
<xs:element name="City" type="xs:string">
</xs:element>
<xs:element name="State" type="xs:string">
</xs:element>
<xs:element name="Zip" type="xs:string">
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:schema>
|
CARPOOL table contains columns based on both SQL data types and one column based on the new DB2 XML data type. This latter column, CARPOOLINFO, stores XML documents that include information such as a carpooler’s address and start time. Figure 1 shows sample carpoolnfo XML document.
Figure 1. Sample XML document to be stored in the CARPOOL table.
You will now see how to insert, update, and validate an XML document in the CARPOOLINFO column. Listing 2 shows XML schema that is used for the purpose of validating an XML document before insertion into the CARPOOLINFO column.
To compile and run any .Net application, you need to create a new Visual Studio .Net project. If you've never worked with Visual Studio .Net before, here's a quick overview of how to accomplish those tasks:
- Launch the Visual Studio 2005.
- Create a new project. Select File > New > Project. Select Visual C# > Windows Application.
- Make a reference to the DB2 .Net data provider IBM.Data.DB2. Right-click on the References node in the Visual Studio .Net project explorer, and select Add Reference. Select the Browse tab and find IBM.Data.DB2.dll, which is located under the DB2 installation directory \SQLLIB\BIN\netf20\IBM.Data.DB2.dll
Connecting to a database
First, you need to establish a connection to your target database. Listing 4 shows an excerpt from a method that establishes a DB2 database connection.
Listing 4. Establish a DB2 database connection
private DB2Connection m_conn = null;
m_conn = new DB2Connection("database=sample");
m_conn.Open();
|
Register XML schema
DB2 9 enables users to register XML schemas and validate input documents against these schemas prior to insertion. XML schemas are part of the World Wide Web Consortium (W3C) industry standard; they enable users to specify the desired structure of compliant XML documents, such as the order and data types of acceptable XML elements, and the use of specific XML namespaces.
DB2 Visual Studio 2005 Add-in tooling provides an easy way to register XML schema using a simple registration designer, but this article shows you how XML schema can be registered using .Net code. Once XML the schema is registered in the DB2 XML schema repository, it can be used for validating XML documents. Listing 5 shows one way of registering XML schema using the .Net code.
Listing 5. Register XML schema
//We have two xsds, CarpoolInfo.xsd and USAddress.xsd,
//where CarpoolInfo.xsd is dependent on USAddress.xsd.
//This XML Schema is identified as schema.xsdname.
String[] xsdFiles = new String[2]
{
"CarpoolInfo.xsd",
"USAddress.xsd"
};
String[] schemaLocs = new String[2]
{
"http://tempuri.org/CarpoolInfo.xsd",
"USAddress.xsd"
};
// Register XML schema using DB2 .Net provider connection’s
// RegisterXmlSchema API
m_conn.RegisterXmlSchema(schema,
xsdname,
schemaLocs,
xsdFiles,
null,
null,
false);
|
Insert and validate XML data
Now that you have established DB2 connection and registered your XML schema, you can write SQL INSERT or UPDATE statements to write new XML data to tables that contain XML columns, and at the same time let DB2 validate XML data before insertion. DB2 can store any well-formed XML document up to 2GB.
Listing 6 shows one way of inserting a row into the CARPOOL table. In this case, the XML document for the CARPOOL info column is read from the string.
Listing 6. Method to insert and update XML data
// Update the database based on the user's action in the dataGrid.
// Performs INSERT, UPDATE and DELETE.
private void update()
{
mydataSourceDT = carpoolInfo.DataSource as DataTable;
dtChanges = mydataSourceDT.GetChanges();
if (dtChanges == null)
return;
// Need to generate insert/update/delete commands to //validate against
// carpoolinfo.xsd
DB2Command insert = new DB2Command
("INSERT INTO CARPOOL" +
"(FIRSTNAME,LASTNAME,TITLE,PHONE,CARPOOLINFO)" +
" VALUES(?,?,?,?," +
"XMLVALIDATE(xmlparse (DOCUMENT CAST" +
" ( ? as CLOB) PRESERVE WHITESPACE )" +
"ACCORDING TO XMLSCHEMA ID " + schema + ".CARPOOLINFO ))");
DB2Command update = new DB2Command
("UPDATE CARPOOL SET FIRSTNAME=?,LASTNAME=?,TITLE=?,PHONE=?," +
"CARPOOLINFO=XMLVALIDATE(xmlparse (DOCUMENT CAST " +
"( ? as CLOB) PRESERVE WHITESPACE ) " +
"ACCORDING TO XMLSCHEMA ID " + schema + ".CARPOOLINFO ) WHERE ID=?");
DB2Command delete = new DB2Command
("DELETE FROM CARPOOL WHERE ID=?");
//Add the Parameters and bind them to the DataTable's //corresponding columns.
DB2Parameter fn1 = new DB2Parameter("fn1", DB2Type.VarChar);
DB2Parameter fn2 = new DB2Parameter("fn2", DB2Type.VarChar);
fn1.SourceColumn = "FIRSTNAME";
fn2.SourceColumn = "FIRSTNAME";
insert.Parameters.Add(fn1);
update.Parameters.Add(fn2);
DB2Parameter ln1 = new DB2Parameter("ln1", DB2Type.VarChar);
DB2Parameter ln2 = new DB2Parameter("ln2", DB2Type.VarChar);
ln1.SourceColumn = "LASTNAME";
ln2.SourceColumn = "LASTNAME";
insert.Parameters.Add(ln1);
update.Parameters.Add(ln2);
DB2Parameter tl1 = new DB2Parameter("tl1", DB2Type.VarChar);
DB2Parameter tl2 = new DB2Parameter("tl2", DB2Type.VarChar);
tl1.SourceColumn = "TITLE";
tl2.SourceColumn = "TITLE";
insert.Parameters.Add(tl1);
update.Parameters.Add(tl2);
DB2Parameter ph1 = new DB2Parameter("ph1", DB2Type.VarChar);
DB2Parameter ph2 = new DB2Parameter("ph2", DB2Type.VarChar);
ph1.SourceColumn = "PHONE";
ph2.SourceColumn = "PHONE";
insert.Parameters.Add(ph1);
update.Parameters.Add(ph2);
DB2Parameter info1 = new DB2Parameter("info1", DB2Type.Clob);
DB2Parameter info2 = new DB2Parameter("info2", DB2Type.Clob);
info1.SourceColumn = "CARPOOLINFO";
info2.SourceColumn = "CARPOOLINFO";
insert.Parameters.Add(info1);
update.Parameters.Add(info2);
DB2Parameter i1 = new DB2Parameter("i1", DB2Type.Integer);
i1.SourceColumn = "ID";
update.Parameters.Add(i1);
DB2Parameter i2 = new DB2Parameter("i2", DB2Type.Integer);
i2.SourceColumn = "ID";
delete.Parameters.Add(i2);
da.InsertCommand = insert;
da.UpdateCommand = update;
da.DeleteCommand = delete;
// Perform the update.
da.Update(dtChanges);
mydataSourceDT.AcceptChanges();
// Refill the dataset, refresh the dataGridView.
ds.Clear();
da.Fill(ds, xsdname);
}
|
Now examine this code. After establishing a database connection, the method creates three DB2Commands; one for insert, one for update, and one for delete. Insert and update commands contain four parameter markers for the regular column values and the fifth parameter marker for the XML column, the method also uses the DB2 XMLVALIDATE function and passes Carpoolinfo XML schema to it for validation.
Query XML data
Now that you have stored data in the CARPOOLINFO table, you are ready to query it. DB2 enables you to write different types of queries to extract both relational and XML data. You can write a simple query that retrieves entire XML documents, or a query that retrieves portions of XML documents based on XML and relational query predicates. This article demonstrates a query that:
- Filters data based on XML predicates
- Retrieves portions of qualifying XML documents along with data stored in a traditional SQL column
In this article, DB2’s XMLExists()function is being used. The sample application in this article uses XMLExists() to illustrate a common programming task: retrieving portions of XML documents.
The example shown in Listing 7 returns the carpool information for carpoolers who live in the city of San Francisco or San Jose. As such, this example projects and restricts both traditional SQL and XML data.
Listing 7. Query XML data
// Populate the dataGrid.
// If "All" is selected, all data from the table will be displayed.
// if a city's name is selected, only rows whose CARPOOLINFO contain
// <City>cityName</City> will be displayed.
public void populate(String cityName)
{
if ( cityName.Equals("All"))
{
carpoolInfo.DataSource = ds.Tables[xsdname];
}
else
{
xq = "select * from CARPOOL where " +
"xmlexists('declare namespace
def=\"http://tempuri.org/XMLSchema.xsd\";" +
"$c/def:CARPOOLINFO/def:Address[def:City=" + "\"" + cityName +
"\"]' passing CARPOOL.CARPOOLINFO as \"c\")" +
"order by ID";
DB2DataAdapter da1 = new DB2DataAdapter(xq, m_conn);
DataSet ds1 = new DataSet();
da1.Fill(ds1, xsdname);
carpoolInfo.DataSource = ds1.Tables[xsdname];
}
}
|
The WHERE clause uses DB2’s XMLExists() function to restrict the data to be returned. It specifies that returned XML documents include only those found in rows in which the CARPOOLINFO’s city is of a certain value (San Francisco or San Jose).
In this sample query, XMLExists() instructs DB2 to determine if a given XML document contains a CARPOOL address that includes the specified city. The PASSING clause specifies where XML documents can be found (in the carpoolinfo column).
Summary
IBM DB2 enables programmers to update and delete XML data using familiar SQL statements. To update and delete XML data stored in DB2, you use SQL UPDATE and DELETE statements. These statements can include SQL/XML functions that restrict the target rows and columns based on the XML element values stored within XML columns. For example, you can delete rows containing information about carpoolers who live in a specific city or update XML (and non-XML data) only for carpoolers whose carpool’s start time is within in a given time.
Because the syntax for using SQL/XML functions in UPDATE and DELETE statements is the same syntax for using them in SELECT statements, the full code samples won't be repeated.
Resources Learn
Get products and technologies
-
Download a free trial version of DB2 9. Download the "DB2 9 data server" among the downloading choices. With time, you may acquire a more mature version of this release of DB2 and no longer need to use the early release candidate.
-
Build your next development project with IBM trial software, available for download directly from developerWorks.
Discuss
About the author | 
| | Farzad Farahbod is an Advisory Software Engineer working at the IBM Silicon Valley Lab, providing tools for seamless integration with IBM Servers. He is Visual Studio .NET XML tooling lead. |
Rate this page
| |
