Using TdXml To Retrieve Xml

TdXml can only be used to retrieve Xml data from Teradata in deferred mode. The steps involved in retrieving Xml from the Advanced SQL Engine are as follows:

Execute a query that contains an Xml column by calling TdCommand.ExecuteReader.
The command must be executed using deferred mode.
Call the GetTdXml method from the TdDataReader instance returned from the call to ExecuteReader.
In order to retrieve the Xml, the TdXml.CreateXmlReader method must be called. This method returns a System.XmlReader that is used to retrieve the Xml from the SQL Engine. XmlReader.Close must be called after the Xml has been processed using the XmlReader in order to release all the provider's resources.
The Xml can also be retrieved by using the TdXml.Value property. This property returns the Xml as a string.

Using GetXmlReader to Retrieve Xml

Use the TdDataReader.GetXmlReader method to return a System.XmlReader directly.

This method creates a temporary TdXml object that is Disposed before the method returns. As stated above, you must dispose the XmlReader after you have finished using it.

The following is an example of using TdXml to retrieve Xml from the SQL Engine:

C#	Copy Code
public void RetrieveXml(TdCommand cmd) { cmd.CommandText = "select XmlColumn1 from XmlTable"; TdXml tdXml; using (TdDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { tdXml = reader.GetTdXml(0); using (XmlReader xmlReader = tdXml.CreateXmlReader()) { // Using XmlReader methods, the application // processes the Xml retrieved from Teradata. } } } }

Copy Code

public void RetrieveXml(TdCommand cmd)
{
    cmd.CommandText = "select XmlColumn1 from XmlTable";

    TdXml tdXml;

    using (TdDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            tdXml = reader.GetTdXml(0);

            using (XmlReader xmlReader = tdXml.CreateXmlReader())
            {
                // Using XmlReader methods, the application
                // processes the Xml retrieved from Teradata.
            }
        }
    }
}

Using GetString and GetChars To Retrieve Xml

If TdDataReader.GetString is used to retrieve the Xml from the SQL Engine, the entire Xml will be returned as a System.String.

An advantage with using TdDataReader.GetChars versus TdDataReader.GetString is that the Xml can be retrieved in chunks. If the Xml is large, chunks of the data can be retrieved and processed by the application. If TdDataReader.GetString where to be used, the entire Xml would be returned as a System.String which may affect the performance of the application.

The following example uses GetString and GetChars to retrieve Xml from the SQL Engine:

C#	Copy Code
public void RetrieveXml(TdCommand cmd) { // The Xml data will be returned in 32k chunks when calling TdDataReader.GetChars const Int32 charArraySize = 1024 * 32; cmd.CommandText = "select XmlColumn1, XmlColumn2 from XmlTable"; String xmlString; using (TdDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { // The entire Xml contained in the first column // will be returned in a String xmlString = reader.GetString(0); // Retrieving the Xml contained in the second column Int32 xmlLength = reader.GetChars(0, null, 0, 1); Char [] xmlData = new Char[charArraySize]; Int32 charsRetrieved = 0; Int32 offset = 0; while (true) { // Retrieving Xml in charArraySize chunks charsRetrieved = reader.GetChars(1, offset, xmlData, 0, charArraySize); // processes the Xml retrieved from Teradata. // checking whether there is any more chunking needed xmlLength -= charArraySize; if (xmlLength <= 0) { break; } // adjusting length and offset to get next chunk of data offset += charsRead; } } } }

Copy Code

public void RetrieveXml(TdCommand cmd)
{
    // The Xml data will be returned in 32k chunks when calling TdDataReader.GetChars
    const Int32 charArraySize = 1024 * 32;

    cmd.CommandText = "select XmlColumn1, XmlColumn2 from XmlTable";

    String xmlString;
    using (TdDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            // The entire Xml contained in the first column
            // will be returned in a String
            xmlString = reader.GetString(0);

            // Retrieving the Xml contained in the second column
            Int32 xmlLength = reader.GetChars(0, null, 0, 1);

            Char [] xmlData = new Char[charArraySize];

            Int32 charsRetrieved = 0;

            Int32 offset = 0;
            while (true)
            {
                // Retrieving Xml in charArraySize chunks
                charsRetrieved = reader.GetChars(1, offset, xmlData, 0, charArraySize);

                // processes the Xml retrieved from Teradata.

                // checking whether there is any more chunking needed
                xmlLength -= charArraySize;
                if (xmlLength <= 0)
                {
                    break;
                }

                // adjusting length and offset to get next chunk of data
                offset += charsRead;
            }
        }
    }
}