Introduction to annotated XML schema decomposition using the DB2 Visual Studio 2005 Add-in

Use DB2 XML shredding technology

The annotated XML schema decomposition feature introduced in DB2® 9, can be used to decompose entire, or fragments of, XML documents into relational tables. DB2 9 XML shredding technology uses annotations in XML schema as the mapping language to map information in an XML document to relational tables. The DB2 XML decomposition uses annotated XML schema to obtain mapping information. Annotations added to the XML schema do not participate in the validation of XML documents. Therefore, you can use the same XML schema for both mapping and the validation of XML documents. In this article, you'll find detailed examples of how to use DB2 Visual Studio 2005 Add-ins to build an application that use DB2 9's XML shredding technology, which allows XML to be shredded, stored, and validated into a relational table.

Farzad Farahbod, DB2 Developer, IBM, Software Group

Farzad Farahbod photoFarzad 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.



09 November 2006

Also available in Chinese

XML shredding support in DB2

The new DB2 9 XML decomposition feature requires that the annotated XML schema be registered in the XML schema repository. The XML schema repository is a new feature introduced in DB2 9 that is a repository for XML schemas that may consist of one or more XML schema documents. XML schema that is registered with XML schema repository can be used to:

  1. Validate XML documents as they are inserted into XML columns or decomposed into relational tables
  2. Hold mapping information in the form of annotation that is to be used for decomposing XML documents into relational tables

This article uses the DB2 Visual Studio 2005 Add-in as examples. Code samples in this article refer to SHIPTO and ITEM tables that tracks customer’s order information. Listing 1 shows how these tables are defined.

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 9.

During DB2 9 installation, ensure that TCP/IP is enabled. If you are not sure whether TCP/IP is enabled after your DB2 installation, perform the following to check:

  1. From your Windows Start menu, navigate to Programs > IBM DB2 > DB2 (default) [or your DB2 instance name] > Command Line Tools > Command Window.
  2. In the DB2 9 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:

  1. db2set db2comm=tcpip
  2. db2 update dbm cfg using svcename 50000
  3. db2stop
  4. db2start

After DB2 installation, you will be 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 File menu, navigate to New > Project. In the New Project dialog box, you should see IBM Projects in the left panel. Close the dialog box. In 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. SHIPTO and ITEM table definition
CREATE TABLE SHIPTO (
	ORDERID VARCHAR ( 256 ) NOT NULL , 
	NAME VARCHAR ( 256 ) , 
	ADDRESS VARCHAR ( 256 ) , 
	CITY VARCHAR ( 256 ) , 
	COUNTRY VARCHAR ( 256 ) , 
	CONSTRAINT TABLE1_PK PRIMARY KEY (ORDERID));
	
CREATE TABLE ITEM (
	ITEMID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY 
		(START WITH 1, INCREMENT BY 1, CACHE 20), 
	TITLE VARCHAR ( 256 ) , 
	NOTE VARCHAR ( 256 ) , 
	PRICE DECIMAL , 
	QUANTITY INTEGER,
	CONSTRAINT ITEM_PK PRIMARY KEY (ITEMID))
Listing 2. Sample XML document to be shredded into SHIPTO and ITEM table. (sampleship.xml)
       <shiporder xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
       orderid='orderid1'>
       <orderperson>orderperson1</orderperson>
        <shipto>
          <name>name1</name>
          <address>address1</address>
          <city>city1</city>
          <country>country1</country>
        </shipto>
        <item>
          <title>title1</title>
          <note>note1</note>
          <quantity>1</quantity>
          <price>1</price>
        </item>
        <item>
           <title>title2</title>
           <note>note2</note>
           <quantity>2</quantity>
           <price>2</price>
         </item>
       </shiporder>
Listing 3. XML schema to be used for annotation and mapping to SHIPTO and ITEM table. (shipOrder.xsd)
<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:sql="http://www.ibm.com/xmlns/prod/db2/xdb1" 
          xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="shiporder">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="orderdesc" type="xs:string" />
        <xs:element name="shipto">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="name" type="xs:string" />
              <xs:element name="address" type="xs:string" />
              <xs:element name="city" type="xs:string" />
              <xs:element name="country" type="xs:string" />
              <xs:element name="zip" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element maxOccurs="unbounded" name="item">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="title" type="xs:string" />
              <xs:element minOccurs="0" name="note" 
                 type="xs:string" />
              <xs:element name="quantity" 
                    type="xs:positiveInteger" />
              <xs:element name="price" type="xs:decimal" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
      <xs:attribute name="orderid" type="xs:string" 
        use="required" />
    </xs:complexType>
  </xs:element>
</xs:schema>

Create an annotated XML schema

  1. Start a new instance of Visual Studio 2005, and make a connection to the DB2 sample database from Server Explorer. Figure 1 shows the Server Explorer connection to the DB2 sample database. Once the connection is being made, right-click on the XML Schema Repository node and select Add Annotated XML Schema from the context menu.

    Figure 1. Create an annotated XML schema
    Create an annotated XML schema
  2. Once DB2 Mapping Editor is shown, select the Use document from file option and locate shiporder.xsd.

    Figure 2. Annotated XML schema source dialog
    Annotated XML schema source dialog
  3. After selecting the source XML schema, right-click on the DB2 Mapping Editor, and select Add Table. Add the ShipTo and Item tables. Drag and drop from the element of the XML schema to the target table. For example, drag the name element from ShipTo to the SHIPNAME column of the ShipTo table. Once the link between the element and the column is established, a blue line indicates the link between the two. Drag and drop the rest of the elements to their related table column. Figure 3 shows the mapping between the ShipTo and Item table to the ShipOrder.xsd element.

    Figure 3. Annotated XML schema mapping editor
    Annotated XML schema mapping editor
  4. Once the mapping is complete, click the Visual Studio IDE Save button to launch the register XML schema dialog allowing you to save the annotated XML schema into DB2 XML schema repository. Figure 4 shows the register annotated XML schema dialog after. Once you have finished,click the IDE Save button.

    Figure 4. Register annotated XML schema
    Register annotated XML schema
  5. Once the annotated XML schema is saved into the DB2 XML schema repository, the Mapping Editor allows you to test the annotated XSD. Simply right-click on the DB2 Mapping Editor and select Test. In Test dialog, select the sample XML document to be shredded for testing and select from the validation drop-down menu whether DB2 is to validate the XML document or not, and then click Test. If annotation and mapping is done properly, you get a success message, and when you look at the your ShipTo and Item table you notice that sample XML document is shredded and data is inserted into both tables. Figure 5 shows the DB2 Mapping Editor Test dialog.

    Figure 5. Test annotated XML schema
    Test annotated XML schema
  6. Now that you have created an annotated XML schema and registered it into DB2 XML schema repository, you can call into DB2 the XML shredding procedure and start populating your relational table. 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:

    1. Launch the Visual Studio 2005.
    2. Create a new project by selecting File > New > Project. Then, select Visual C# > Windows Application.
    3. Make a reference to the DB2 .Net data provider IBM.Data.DB2. Right-click on the References node in 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.

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

Execute DB2 XML shredding procedure

DB2 9 enables users to create and register annotated XML schemas for the purpose of XML document shredding and the validation of input documents against these schemas prior to insertion. DB2 Visual Studio 2005 Add-in tooling provides an easy way to create and register annotated XML schema using a simple mapping designer as explained earlier. This article shows you how annotated XML schema can be used in connection with shredding an XML document in .Net code. Once annotated XML schema is registered in the DB2 XML schema repository, it can be used for shredding and validating XML documents. Listing 5 shows one way of calling a DB2 XML shredding procedure using .Net code.

Listing 5. Calling DB2 XML shredding procedure:
       private void executeShredding()
        {
            DB2Transaction   dbtrans = null;
            ///sysproc.xdbDecompXML10MB(IN rschema VARCHAR(128),
            //IN xmlschemaname VARCHAR(128),
            //IN xmldoc BLOB(10M), IN documentid VARCHAR(1024),
            //IN validation INTEGER, IN reserved for future use BLOB(64K),
            //IN reserved for future use BLOB(64K), IN reserved INTEGER,
            //out struct sqlca *ca)) // implicit output parameter

       try{
            if (m_conn == null) return;
            DB2Command cmd = new DB2Command();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SYSPROC.XDBDECOMPXML";
            cmd.Connection = m_conn;

            IBM.Data.DB2.DB2Parameter p1 = new IBM.Data.DB2.DB2Parameter();
            p1.DB2Type = DB2Type.VarChar;
            p1.Direction = ParameterDirection.Input;
		    // Database owner or relational 
            // schema where XSD registered
            p1.Value = “DB2Admin”; 

            IBM.Data.DB2.DB2Parameter p2 = new IBM.Data.DB2.DB2Parameter();
            p2.DB2Type = DB2Type.VarChar;
            p2.Direction = ParameterDirection.Input;
		    // Name of the registered annotated XML schema
            p2.Value = xsrname;
 
            IBM.Data.DB2.DB2Parameter p3 = new IBM.Data.DB2.DB2Parameter();
            p3.DB2Type = DB2Type.Blob;
            p3.Direction = ParameterDirection.Input;
		    // Convert the sample XML document to be shredded
		    //into byte array since this parameter is of type blob
            byte[ ] b = getByteArrayFromFile(@”ShiporderSample.xml”);

            if (b == null) return ;
            p3.Value = b;

            IBM.Data.DB2.DB2Parameter p4 = new IBM.Data.DB2.DB2Parameter();
            p4.DB2Type = DB2Type.VarChar;
            p4.Direction = ParameterDirection.Input;

            p4.Value = xsrdocId;
 
		    // Tell DB2 whether use the same XML 
            // schema for validation before shredding 
            IBM.Data.DB2.DB2Parameter p5 = new IBM.Data.DB2.DB2Parameter();
            p5.DB2Type = DB2Type.Integer;
            p5.Direction = ParameterDirection.Input;
            p5.Value = 0; //False
           // p5.Value=1; //True

            IBM.Data.DB2.DB2Parameter p6 = new IBM.Data.DB2.DB2Parameter();
            p6.DB2Type = DB2Type.Blob;
            p6.Direction = ParameterDirection.Input;
            p6.Value = null;
          

            IBM.Data.DB2.DB2Parameter p7 = new IBM.Data.DB2.DB2Parameter();
            p7.DB2Type = DB2Type.Blob;
            p7.Direction = ParameterDirection.Input;
            p7.Value = null;

            IBM.Data.DB2.DB2Parameter p8= new IBM.Data.DB2.DB2Parameter();
            p8.DB2Type = DB2Type.Integer;
            p8.Direction = ParameterDirection.Input;
            p8.Value = null;

            cmd.Parameters.Add(p1);
            cmd.Parameters.Add(p2);
            cmd.Parameters.Add(p3);
            cmd.Parameters.Add(p4);
            cmd.Parameters.Add(p5);
            cmd.Parameters.Add(p6);
            cmd.Parameters.Add(p7);
            cmd.Parameters.Add(p8);

          	//Begin a new transactionb
            dbtrans =  cmd.Connection.BeginTransaction();
            cmd.Transaction = dbtrans as DB2Transaction ;

            int i = cmd.ExecuteNonQuery();
            
            if (dbtrans != null) dbtrans.Commit(); 
   
       }
       catch (Exception exp)
       {
           
            if (dbtrans != null) dbtrans.Rollback();
            return;
        }
      }
Listing 6. Convert a file to byte array:
        private Byte[ ] getByteArrayFromFile(string FileName)
        {
            byte[ ] b = null;

            System.IO.FileStream fs = new System.IO.FileStream(FileName, Open,Read);
            b = new byte[fs.Length];
            fs.Read(b, 0, b.Length);
            fs.Close();
            return b;


        }

Now examine this code. After establishing a database connection, the method creates a DB2Command for calling the "SYSPROC.XDBDECOMPXML" stored procedure. DB2Command contains eight parameter markers. The first parameter marker is to hold the relational schema or the DB owner name of the registered annotated XML schema. The second parameter marker holds the name of the registered annotated XML schema. The third parameter marker is to hold the source of the XML document to be shredded in the form of byte array. The fourth parameter marker is a flag 0/1 that tells DB2 if the XSD used in the shredding is also to be used for validating the source XML document. The rest of the parameter markers sixth, seventh, and eighth are not used.

Summary

Any XML schema registered in the XML schema repository can be enabled to be used with XML decomposition if the XML schema has at least one decomposition related annotation present. When an XML schema is enabled for decomposition, checks are made to ensure the correctness of the annotations, compatibility of XML schema types to the DB2 column type, and the existence of relational tables and columns specified in annotations. If the annotated XML schema is deemed valid, the mapping information is extracted and stored in catalog tables in a ready-to-use binary format. IBM DB2 Visual Studio 2005 .Net Add-in provides an easy way to create and maintain annotated XML schema using an easy to use Mapping Editor and annotated XML schema testing utility.

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

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, XML
ArticleID=173478
ArticleTitle=Introduction to annotated XML schema decomposition using the DB2 Visual Studio 2005 Add-in
publish-date=11092006