OracleXmlQueryProperties Class

An OracleXmlQueryProperties object represents the XML properties used by the OracleCommand class when the XmlCommandType property is Query.

Class Inheritance

System.Object

  System.OracleXmlQueryProperties

Declaration

public sealed class OracleXmlQueryProperties : ICloneable

Thread Safety

All public static methods are thread-safe, although instance methods do not guarantee thread safety.

Remarks

OracleXmlQueryProperties can be accessed, and modified using the XmlQueryProperties property of the OracleCommand class. Each OracleCommand object has its own instance of the OracleXmlQueryProperties class in the XmlQueryProperties property.

Use the default constructor to get a new instance of the OracleXmlQueryProperties. Use the OracleXmlQueryProperties.Clone() method to get a copy of an OracleXmlQueryProperties instance.

Example

This example retrieves relational data as XML.

// C#
 
using System;
using System.IO;
using System.Data;
using System.Xml;
using System.Text;
using Oracle.DataAccess.Client;
 
class OracleXmlQueryPropertiesSample
{
  static void Main()
  {
    int rows = 0;
    StreamReader sr = null;
 
    // Define the XSL document for doing the transform.
    string xslstr = "<?xml version='1.0'?>\n" +
      "<xsl:stylesheet version=\"1.0\"" +
      " xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\">\n" +
      "  <xsl:output encoding=\"utf-8\"/>\n" +
      "  <xsl:template match=\"/\">\n" +
      "    <EMPLOYEES>\n" +
      "      <xsl:apply-templates select=\"ROWSET\"/>\n" +
      "    </EMPLOYEES>\n" +
      "  </xsl:template>\n" +
      "  <xsl:template match=\"ROWSET\">\n" +
      "      <xsl:apply-templates select=\"ROW\"/>\n" +
      "  </xsl:template>\n" +
      "  <xsl:template match=\"ROW\">\n" +
      "    <EMPLOYEE>\n" +
      "    <EMPLOYEE_ID>\n" +
      "      <xsl:apply-templates select=\"EMPNO\"/>\n" +
      "    </EMPLOYEE_ID>\n" +
      "    <EMPLOYEE_NAME>\n" +
      "      <xsl:apply-templates select=\"ENAME\"/>\n" +
      "    </EMPLOYEE_NAME>\n" +
      "    <HIRE_DATE>\n" +
      "      <xsl:apply-templates select=\"HIREDATE\"/>\n" +
      "    </HIRE_DATE>\n" +
      "    <JOB_TITLE>\n" +
      "      <xsl:apply-templates select=\"JOB\"/>\n" +
      "    </JOB_TITLE>\n" +
      "    </EMPLOYEE>\n" +
      "  </xsl:template>\n" +
      "</xsl:stylesheet>\n";
 
    // Create the connection.
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    // Set the date, and timestamp formats for Oracle 9i Release 2, or later.
    // This is just needed for queries.
    if (!con.ServerVersion.StartsWith("9.0") && 
      !con.ServerVersion.StartsWith("8.1"))
    {
      OracleGlobalization sessionParams = con.GetSessionInfo();
      sessionParams.DateFormat = "YYYY-MM-DD\"T\"HH24:MI:SS";
      sessionParams.TimeStampFormat = "YYYY-MM-DD\"T\"HH24:MI:SS.FF3";
      sessionParams.TimeStampTZFormat = "YYYY-MM-DD\"T\"HH24:MI:SS.FF3";
      con.SetSessionInfo(sessionParams);
    }
 
    // Create the command.
    OracleCommand cmd = new OracleCommand("", con);
 
    // Set the XML command type to query.
    cmd.XmlCommandType =  OracleXmlCommandType.Query;
 
    // Set the SQL query.
    cmd.CommandText = "select * from emp e where e.empno = :empno";
 
    // Set command properties that affect XML query behaviour.
    cmd.BindByName = true;
 
    // Bind values to the parameters in the SQL query.
    Int32 empNum = 7369;
    cmd.Parameters.Add("empno", OracleDbType.Int32, empNum,
      ParameterDirection.Input);
      
    // Set the XML query properties.
    cmd.XmlQueryProperties.MaxRows =  1;
    cmd.XmlQueryProperties.RootTag =  "ROWSET";
    cmd.XmlQueryProperties.RowTag =  "ROW";
    cmd.XmlQueryProperties.Xslt =  xslstr;
 
    // Test query execution without returning a result.
    Console.WriteLine("SQL query: select * from emp e where e.empno = 7369");
    Console.WriteLine("Maximum rows: all rows (-1)");
    Console.WriteLine("Return Value from OracleCommand.ExecuteNonQuery():");
    rows = cmd.ExecuteNonQuery();
    Console.WriteLine(rows);
    Console.WriteLine("\n");
 
    // Get the XML document as an XmlReader.
    Console.WriteLine("SQL query: select * from emp e where e.empno = 7369");
    Console.WriteLine("Maximum rows: all rows (-1)");
    Console.WriteLine("XML Document from OracleCommand.ExecuteXmlReader():");
 
    XmlReader xmlReader =  cmd.ExecuteXmlReader();
    XmlDocument xmlDocument = new XmlDocument();
    xmlDocument.PreserveWhitespace = true;
    xmlDocument.Load(xmlReader);
    Console.WriteLine(xmlDocument.OuterXml);
    Console.WriteLine("\n");
 
    // Change the SQL query, and set the maximum number of rows to 2.
    cmd.CommandText = "select * from emp e";
    cmd.Parameters.Clear();
    cmd.XmlQueryProperties.MaxRows =  2;
 
    // Get the XML document as a Stream.
    Console.WriteLine("SQL query: select * from emp e");
    Console.WriteLine("Maximum rows: 2");
    Console.WriteLine("XML Document from OracleCommand.ExecuteStream():");
    Stream stream = cmd.ExecuteStream();
    sr = new StreamReader(stream, Encoding.Unicode);
    Console.WriteLine(sr.ReadToEnd());
    Console.WriteLine("\n");
 
    // Get all the rows.
    cmd.XmlQueryProperties.MaxRows =  -1;
 
    // Append the XML document to an existing Stream.
    Console.WriteLine("SQL query: select * from emp e");
    Console.WriteLine("Maximum rows: all rows (-1)");
    Console.WriteLine("XML Document from OracleCommand.ExecuteToStream():");
    MemoryStream mstream = new MemoryStream(32);
    cmd.ExecuteToStream(mstream);
    mstream.Seek(0, SeekOrigin.Begin);
    sr = new StreamReader(mstream, Encoding.Unicode);
    Console.WriteLine(sr.ReadToEnd());
    Console.WriteLine("\n");
 
    // Clean up.
    cmd.Dispose();
    con.Close();
    con.Dispose();
  }
}

Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

ODP.NET Version: ODP.NET for .NET Framework 2.0 or ODP.NET for .NET Framework 4

---

OracleXmlQueryProperties Members

OracleXmlQueryProperties members are listed in the following tables.

OracleXmlQueryProperties Constructors

The OracleXmlQueryProperties constructors are listed in Table 6-2.

OracleXmlQueryProperties Properties

The OracleXmlQueryProperties properties are listed in Table 6-3.

OracleXmlQueryProperties Public Methods

The OracleXmlQueryProperties public methods are listed in Table 6-4.

---

OracleXmlQueryProperties Constructor

The OracleXmlQueryProperties constructor instantiates a new instance of the OracleXmlQueryProperties class.

Declaration

// C#
public OracleXmlQueryProperties();

---

OracleXmlQueryProperties Properties

The OracleXmlQueryProperties properties are listed in Table 6-5.

MaxRows

This property specifies the maximum number of rows from the result set of the query that can be represented in the result XML document.

Declaration

// C#
public int MaxRows {get; set;}

Property Value

The maximum number of rows.

Exceptions

ArgumentException - The new value for MaxRows is not valid.

Remarks

Default value is -1.

Possible values are:

• -1 (all rows).

• A number greater than or equal to 0.

RootTag

This property specifies the root element of the result XML document.

Declaration

// C#
public string RootTag {get; set;}

Property Value

The root element of the result XML document.

Remarks

The default root tag is ROWSET.

To indicate that no root tag is be used in the result XML document, set this property to null or "" or String.Empty.

If both RootTag and RowTag are set to null, an XML document is returned only if the result set returns one row and one column.

RowTag

This property specifies the value of the XML element which identifies a row of data from the result set in an XML document.

Declaration

// C#
public string RowTag {get; set;}

Property Value

The value of the XML element.

Remarks

The default is ROW.

To indicate that no row tag is be used in the result XML document, set this property to null or "" or String.Empty.

If both RootTag and RowTag are set to null, an XML document is returned only if the result set returns one row and one column.

Xslt

This property specifies the XSL document used for XML transformation using XSLT.

Declaration

// C#
public string Xslt {get; set;}

Property Value

The XSL document used for XML transformation.

Remarks

Default value is null.

The XSL document is used for XML transformation of the XML document generated from the result set of the query.

XsltParams

This property specifies parameters for the XSL document.

Declaration

// C#
public string XsltParams {get; set;}

Property Value

The parameters for the XSL document.

Remarks

Default value is null.

The parameters are specified as a string of "name=value" pairs of the form "param1=value1; param2=value2;..." delimited by semicolons.

---

OracleXmlQueryProperties Public Methods

The OracleXmlQueryProperties public methods are listed in Table 6-6.

Clone

This method creates a copy of an OracleXmlQueryProperties object.

Declaration

// C#
public object Clone();

Return Value

An OracleXmlQueryProperties object

Implements

ICloneable