Example: XML and XQuery support in C# .NET CLR procedure

When you have a good understanding of the basics of procedures and the essentials of .NET common language runtime (CLR) routines, XQuery, and XML, you can create and call CLR procedures with XML features.

Attention: With the release of Db2® 11.5.9, support for Microsoft .Net common language runtime (CLR) routines is deprecated and might be removed in the future. If you are currently running routines that have a dependency on .NET CLR, rewrite the routine logic in a supported language and then recreate the routines.

The following example demonstrates a creation of C# .NET CLR procedure with parameters of type XML, which update and query XML data.

Prerequisites

Before you work with the CLR procedure example, you might want to read the following concept topics:

The examples use a table that is named xmlDataTable, which is created and populated with the following statements:

  CREATE TABLE xmlDataTable
  (
     num INTEGER,
     xdata XML
  )

  INSERT INTO xmlDataTable VALUES
     (1, XMLPARSE(DOCUMENT '<doc>
                                 <type>car</type>
                                 <make>Pontiac</make>
                                 <model>Sunfire</model>
                                 </doc>' PRESERVE WHITESPACE)),
     (2, XMLPARSE(DOCUMENT '<doc>
                                 <type>car</type>
                                 <make>Mazda</make>
                                 <model>Miata</model>
                                 </doc>' PRESERVE WHITESPACE)),
     (3, XMLPARSE(DOCUMENT '<doc>
                                 <type>person</type>
                                 <name>Mary</name>
                                 <town>Vancouver</town>
                                 <street>Waterside</street>
                                 </doc>' PRESERVE WHITESPACE)),
     (4, XMLPARSE(DOCUMENT '<doc>
                                 <type>person</type>
                                 <name>Mark</name>
                                 <town>Edmonton</town>
                                 <street>Oak</street>
                                 </doc>' PRESERVE WHITESPACE)),
     (5, XMLPARSE(DOCUMENT '<doc>
                                 <type>animal</type>
                                 <name>dog</name>
                                 </doc>' PRESERVE WHITESPACE)),
     (6, NULL),
     (7, XMLPARSE(DOCUMENT '<doc>
                                 <type>car</type>
                                 <make>Ford</make>
                                 <model>Taurus</model>
                                 </doc>' PRESERVE WHITESPACE)),
     (8, XMLPARSE(DOCUMENT '<doc>
                                 <type>person</type>
                                 <name>Kim</name>
                                 <town>Toronto</town>
                                 <street>Elm</street>
                                 </doc>' PRESERVE WHITESPACE)),
     (9, XMLPARSE(DOCUMENT '<doc>
                                 <type>person</type>
                                 <name>Bob</name>
                                 <town>Toronto</town>
                                 <street>Oak</street>
                                 </doc>' PRESERVE WHITESPACE)),
     (10, XMLPARSE(DOCUMENT '<doc>
                                  <type>animal</type>
                                  <name>bird</name>
                                  </doc>' PRESERVE WHITESPACE))@

Procedure

You can use the following example as references when you are making your own C# CLR procedures:

The C# external code file
Example 1: C# parameter style GENERAL procedure with XML features

The C# external code file

The example consists of two parts: the CREATE PROCEDURE statement and the external C# code implementation of the procedure from which the associated assembly can be built.

The C# source file that contains the procedure implementations of the following examples is named gwenProc.cs and has the following format:

  using System;
  using System.IO;
  using System.Data;
  using IBM.Data.DB2;
  using IBM.Data.DB2Types;
 
  namespace bizLogic
  {
     class empOps
     {          ... 
       // C# procedures 
                ...
     }
  }

The file inclusions are indicated at the top of the file. The IBM.Data.DB2 inclusion is required if any of the procedures in the file contain SQL. The IBM.Data.DB2Types inclusion is required if any of the procedures in the file contain parameters or variables of type XML. There is a namespace declaration in this file and a class empOps that contains the procedures. The use of namespaces is optional. If a namespace is used, the namespace must appear in the assembly path name that is provided in the EXTERNAL clause of the CREATE PROCEDURE statement.

It is important to note the name of the file, the namespace, and the name of the class, that contains the procedure implementation. These names are important because the EXTERNAL clause of the CREATE PROCEDURE statement for each procedure must specify this information so that the database manager can locate the assembly and class of the CLR procedure.

Example: C# parameter style GENERAL procedure with XML features

The following example contains the C# code for a parameter style GENERAL procedure with XML parameter and corresponding CREATE PROCEDURE statement. The xmlProc1 procedure takes two input parameters, inNum and inXML. The input parameter values are inserted into the table xmlDataTable and values are then retrieved using XQuery. The xmlProc1 procedure also retries another XML value using an SQL statement. The retrieved XML values are assigned to two output parameters, outXML1 and outXML2. No result sets are returned.

  CREATE PROCEDURE  xmlProc1   ( IN inNUM  INTEGER,
                               IN inXML  XML as CLOB (1K),
                               OUT inXML  XML as CLOB (1K),
                               OUT inXML  XML as CLOB (1K)
                             )
  LANGUAGE CLR
  PARAMETER STYLE GENERAL
  DYNAMIC RESULT SETS 0
  FENCED
  THREADSAFE
  DETERMINISTIC
  NO DBINFO
  MODIFIES SQL DATA
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenProc.dll:bizLogic.empOps!xmlProc1' ;

  //*************************************************************************
  //  Stored Procedure: xmlProc1
  //
  //  Purpose:   insert XML data into XML column
  //
  //  Parameters:
  //
  //   IN:    inNum -- the sequence of XML data to be insert in xmldata table
  //          inXML -- XML data to be inserted
  //   OUT:   outXML1 -- XML data returned - value retrieved using XQuery
  //          outXML2 -- XML data returned - value retrieved using SQL
  //*************************************************************************

      public static void xmlProc1 (     int      inNum, DB2Xml   inXML,
                                   out DB2Xml   outXML1, out DB2Xml   outXML2 )
      {
         // Create new command object from connection context
         DB2Parameter parm;
         DB2Command cmd;
         DB2DataReader reader = null;
         outXML1 = DB2Xml.Null;
         outXML2 = DB2Xml.Null;

         // Insert input XML parameter value into a table 
         cmd = DB2Context.GetCommand();
         cmd.CommandText = "INSERT INTO "
                           + "xmlDataTable( num , xdata ) "
                           + "VALUES( ?, ? )";

         parm = cmd.Parameters.Add("@num", DB2Type.Integer );
         parm.Direction = ParameterDirection.Input;
         cmd.Parameters["@num"].Value = inNum;
         parm = cmd.Parameters.Add("@data", DB2Type.Xml);
         parm.Direction = ParameterDirection.Input;
         cmd.Parameters["@data"].Value = inXML ;
         cmd.ExecuteNonQuery();
         cmd.Close();

         // Retrieve XML value using XQuery 
                        and assign value to an XML output parameter 
         cmd = DB2Context.GetCommand();
         cmd.CommandText = "XQUERY for $x " +
                           "in db2-fn:xmlcolumn(\"xmlDataTable.xdata\")/doc "+
                           "where $x/make = \'Mazda\' " +
                           "return <carInfo>{$x/make}{$x/model}</carInfo>";
         reader = cmd.ExecuteReader();
         reader.CacheData= true;

         if (reader.Read())
         {  outXML1 = reader.GetDB2Xml(0);  }
         else
         {  outXML1 = DB2Xml.Null;  }	

         reader.Close();
         cmd.Close();
 
         // Retrieve XML value using SQL 
                        and assign value to an XML output parameter value 
         cmd = DB2Context.GetCommand();
         cmd.CommandText = "SELECT xdata "
                            + "FROM xmlDataTable "
                            + "WHERE num = ?";

         parm = cmd.Parameters.Add("@num", DB2Type.Integer );
         parm.Direction = ParameterDirection.Input;
         cmd.Parameters["@num"].Value = inNum;
         reader = cmd.ExecuteReader();
         reader.CacheData= true;

         if (reader.Read())
         {  outXML2 = reader.GetDB2Xml(0);  }
         else
         {  outXML = DB2Xml.Null;  }	

         reader.Close() ;
         cmd.Close();

         return;               
      }