Once the basics of procedures, the essentials of .NET common language runtime routines, XQuery, and XML are understood, you can create and call CLR procedures with XML features.
The following example demonstrates a creation of C# .NET CLR procedure with parameters of type XML, which update and query XML data.
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))@
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.
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.
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;
}