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.

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

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

				
<?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
   
   
   

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
   
   
   

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
   
   
   

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
   
   
   

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
   
   
   

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.

				
        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.

				
       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;
        }
      }
        

				
        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

• "Develop proof-of-concept .NET applications": 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 an explanation on 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): Find steps for creating 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): Find steps for creating 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.
  
  
• 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.
  
  
• Microsoft Visual Studio Developer Center: Find information on Microsoft® Visual Studio.
  
  
• 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: Exchange ideas and share solutions with your peers in the IBM DB2 9 community..
  
  
• 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.

Rate this article

Comments

Back to top

Table of contents

• XML shredding support in DB2
• Environment setup
• Create an annotated XML schema
• Connect to a database
• Execute DB2 XML shredding procedure
• Summary
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers