XML data validation using IBM DB2 Visual Studio 2005 .Net Add-in

Use DB2 XML validation technology

Farzad Farahbod, Technical Lead, IBM DB2 .NET XML and LINQ Tooling, IBM

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.

Summary: 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.

Tag this!

Date: 30 Nov 2006
Level: Intermediate

Activity: 1669 views
Comments:

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();

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

"Develop proof-of-concept .NET applications" (developerWorks): Create proof-of-concept applications to access relational and XML data in DB2 9, using Microsoft Visual Studio .NET 2005.
- "Develop proof-of-concept .NET applications, Part 1: Create database objects in DB2 Viper using .NET" (developerWorks, May 2006): Find the application specifications, database design, and how to create DB2 relational database objects required in the applications.
- "Develop proof-of-concept .NET applications, Part 2: Wire DB2 data to Windows applications" (developerWorks, June 2006): Create a Windows desktop application in .NET to consume the DB2 data.
- "Develop proof-of-concept .NET applications, Part 3: Wire DB2 data to Web applications" (developerWorks, June 2006): Create a Web application in ASP.NET and wiring the DB2 data to the Web application running in a browser.
DB2 9 product site: Get more information about DB2 9, the new data server (formerly known as Viper) that seamlessly integrates XML and relational data.
DB2 for .NET: Learn more about DB2 Add-ins for Visual Studio .NET.
IBM DB2 Database for Linux, UNIX, and Windows Information Center: Find information that you need to use the DB2 Information Management family of products and features.
Visual Studio 2005 Developer Center: MSDN resources for Microsoft Visual Studio .NET 2005.
Stay current with developerWorks technical events and Webcasts.
developerWorks Information Management zone: Expand your skills on IBM Information Management products.
developerWorks Information Management and Visual Studio .NET zone: Expand your skills in developing .NET applications for DB2 and other IBM Information Management products.
developerWorks DB2 for Linux, UNIX, and Windows zone: Access a wide variety of articles about DB2 9 and other DB2 resources.

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

Participate in the discussion forum.
IBM DB2 9 Forum.
Participate in developerWorks blogs and get involved in the developerWorks community.

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.

Comments

Trademarks | My developerWorks terms and conditions

Close [x]

Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Close [x]

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/

SITE_ID=1

Zone=Information Management, XML

ArticleID=177099

ArticleTitle=XML data validation using IBM DB2 Visual Studio 2005 .Net Add-in

publish-date=11302006

author1-email=ffarahbo@us.ibm.com

author1-email-cc=