private void button2_Click(object sender, EventArgs e)
{
string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
try
{
OracleConnection _connObj = new OracleConnection(_connstring);
_connObj.Open();
string _sql = "SELECT INFO FROM PRODUCT_EXTRAINFO";
string _message = "";
OracleCommand _cmdObj = new OracleCommand(_sql, _connObj);
OracleDataReader _rdrObj = _cmdObj.ExecuteReader();
if (_rdrObj.HasRows)
{
while (_rdrObj.Read())
{
OracleXmlType _oracleXmlType = _rdrObj.GetOracleXmlType(_rdrObj.GetOrdinal("INFO"));
if (!_oracleXmlType.IsNull)
{
string _xPath = "/PRODUCT/CATEGORY";
string _nsMap = null;
if (_oracleXmlType.IsExists(_xPath, _nsMap))
{
OracleXmlType _oracleXmlTypeNode = _oracleXmlType.Extract(_xPath, _nsMap);
if (!_oracleXmlTypeNode.IsEmpty)
{
_message = "Category tag:\t" +
_oracleXmlTypeNode.Value;
}
}
_message += "Raw XML:\n" + _oracleXmlType.Value;
MessageBox.Show(_message);
}
}
}
_rdrObj.Close();
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// Demonstrate the properties and methods on OracleXmlType
/// </summary>
/// <param name="connectStr"></param>
/// <returns></returns>
public static void PropDemo(OracleXmlType xml)
{
try
{
// Value property
Console.WriteLine("Value property: ");
Console.WriteLine(xml.Value);
Console.WriteLine("");
// Get an Oracle XML Stream
OracleXmlStream strm = xml.GetStream();
Console.WriteLine("GetStream() method: ");
Console.WriteLine(strm.Value);
Console.WriteLine("");
strm.Dispose();
// Get a .NET XmlReader
XmlReader xmlRdr = xml.GetXmlReader();
XmlDocument xmlDocFromRdr = new XmlDocument();
xmlDocFromRdr.Load(xmlRdr);
Console.WriteLine("GetXmlReader() method: ");
Console.WriteLine(xmlDocFromRdr.OuterXml);
Console.WriteLine("");
xmlDocFromRdr = null;
xmlRdr = null;
// Get a .NET XmlDocument
XmlDocument xmlDoc = xml.GetXmlDocument();
Console.WriteLine("GetXmlDocument() method: ");
Console.WriteLine(xmlDoc.OuterXml);
Console.WriteLine("");
xmlDoc = null;
// IsExists method
string xpathexpr = "/PO/SHIPADDR";
string nsmap = null;
if (xml.IsExists(xpathexpr, nsmap))
{
// Extract method
OracleXmlType xmle = xml.Extract(xpathexpr, nsmap);
// IsEmpty property
if (xmle.IsEmpty)
{
Console.WriteLine("Extract() method returns empty xml data");
}
else
{
Console.WriteLine("Extracted XML data: ");
Console.WriteLine(xmle.Value);
}
Console.WriteLine("");
xmle.Dispose();
}
// Use XSLT on the OracleXmlType
StringBuilder blr = new StringBuilder();
blr.Append("<?xml version=\"1.0\"?> ");
blr.Append("<xsl:stylesheet version=\"1.0\" ");
blr.Append("xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\"> ");
blr.Append(" <xsl:template match=\"/\"> ");
blr.Append(" <NEWPO> ");
blr.Append(" <xsl:apply-templates select=\"PO\"/> ");
blr.Append(" </NEWPO> ");
blr.Append(" </xsl:template> ");
blr.Append(" <xsl:template match=\"PO\"> ");
blr.Append(" <xsl:apply-templates select=\"CUSTNAME\"/> ");
blr.Append(" </xsl:template> ");
blr.Append(" <xsl:template match=\"CUSTNAME\"> ");
blr.Append(" <CNAME> <xsl:value-of select=\".\"/> </CNAME> ");
blr.Append(" </xsl:template> </xsl:stylesheet> ");
string pmap = null;
OracleXmlType xmlt = xml.Transform(blr.ToString(), pmap);
//Print the transformed xml data
Console.WriteLine("XML Data after Transform(): ");
Console.WriteLine(xmlt.Value);
// Update the CNAME in the transformed xml data
xpathexpr = "/NEWPO/CNAME/text()";
xmlt.Update(xpathexpr, nsmap, "NewName");
// See the updated xml data
Console.WriteLine("XML Data after Update(): ");
Console.WriteLine(xmlt.Value);
Console.WriteLine("");
xmlt.Dispose();
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e.Message);
}
}
