/// <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); } }
private void button6_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"; OracleCommand _cmdObj = new OracleCommand(_sql, _connObj); OracleDataReader _rdrObj = _cmdObj.ExecuteReader(); if (_rdrObj.HasRows) { while (_rdrObj.Read()) { OracleXmlType _oracleXmlType = _rdrObj.GetOracleXmlType(_rdrObj.GetOrdinal("INFO")); string _xsl; _xsl = "<?xml version=\"1.0\"?>" + "<xsl:stylesheet version=\"1.0\" " + " xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\">" + " <xsl:template match=\"/\">" + " <ProductExtraInfo>" + " <xsl:apply-templates select=\"PRODUCT\"/>" + " </ProductExtraInfo>" + " </xsl:template>" + " <xsl:template match=\"PRODUCT\">" + " <xsl:apply-templates select=\"CATEGORY\"/>" + " <xsl:apply-templates select=\"REGIONAL_PRICING\"/>" + " </xsl:template>" + " <xsl:template match=\"CATEGORY\">" + " <CategoryName>" + " <xsl:value-of select=\".\"/>" + " </CategoryName>" + " </xsl:template>" + " <xsl:template match=\"REGIONAL_PRICING\">" + " <xsl:apply-templates select=\"EASTASIA\"/>" + " <xsl:apply-templates select=\"AMERICAS\"/>" + " </xsl:template>" + " <xsl:template match=\"EASTASIA\">" + " <EastAsianPrice>" + " <xsl:value-of select=\".\"/>" + " </EastAsianPrice>" + " </xsl:template>" + " <xsl:template match=\"AMERICAS\">" + " <AmericanPrice>" + " <xsl:value-of select=\".\"/>" + " </AmericanPrice>" + " </xsl:template>" + "</xsl:stylesheet>"; OracleXmlType _transformedXML = _oracleXmlType.Transform(_xsl, ""); MessageBox.Show(_transformedXML.Value); } } _rdrObj.Close(); _connObj.Close(); _connObj.Dispose(); _connObj = null; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }