Retrieving Data Using the XmlReader

New: 5 December 2005

The XmlReader class, part of the System.Xml namespace for the Microsoft .NET Framework Class Library, is similar to the AdomdDataReader class in that the XmlReader class also provides fast, non-cached, forward-only access to data. If there is no need for an in-memory, analytical view of the data using the CellSet object, the XmlReader object is perfect for retrieving XML data, especially for large quantities of data. Because XmlReader streams data, XmlReader does not have to retrieve and cache all the data before exposing the data to the caller, as would be the case if a CellSet object were used to convert the XML for Analysis response into an analytical object model representation.

The XmlReader class provides direct access to the XML for Analysis response received by ADOMD.NET when the ExecuteXmlReader method of the AdomdCommand object is called. Because the retrieved data is raw XML, you must parse the data and metadata manually. As soon as the data has been retrieved, the XmlReader object should be closed.

Retrieving Data and Metadata

To use the XmlReader class to retrieve data, you follow these steps:

1. Create a new instance of the object.
   To create a new instance of the XmlReader class, you call the Execute or ExecuteXmlReader method of the AdomdCommand object.
2. Retrieve data.
   After the command runs the query and returns an XmlReader, you must parse the data and metadata. The XML data and metadata is presented in the native format used by the XML for Analysis provider. For most XML for Analysis providers, the native format is the MDDataSet format. The MDDataSet format provides both data and metadata for cellsets in a well-structured format. For more information about the MDDataSet format, see the XML for Analysis specification.
3. Close the reader.
   You should always call the Close method when you have finished using the XmlReader object. While an XmlReader is open, that XmlReader has exclusive use of the AdomdConnection object that was used to run the command. You will not be able to run any commands using that AdomdConnection, including creating another XmlReader or AdomdDataReader, until you close the original XmlReader.

Example of Retrieving Data from the XmlReader

The following example runs a command and retrieves the data as an XmlReader, outputting the contents of the file to the console.

        void OutputDataWithXML()
        {
            //Open a connection to the local server.
            AdomdConnection conn = new AdomdConnection("Data Source=localhost");
            conn.Open();

            //Create a command to retrieve the data.
            AdomdCommand cmd = new AdomdCommand(@"WITH MEMBER [Measures].[FreightCostPerOrder] AS 
[Measures].[Reseller Freight Cost]/[Measures].[Reseller Order Quantity],  
FORMAT_STRING = 'Currency'

SELECT [Geography].[Geography].[Country].&[United States].Children ON ROWS, 
[Date].[Calendar].[Calendar Year] ON COLUMNS
FROM [Adventure Works]
WHERE [Measures].[FreightCostPerOrder]", conn);

            //Execute the command, retrieving an XmlReader.
            System.Xml.XmlReader reader = cmd.ExecuteXmlReader();

            //Do something with the reader: Parse data, Parse metadata,
            //                              Save for later loading into CellSet, etc.
            Console.WriteLine(reader.ReadOuterXml());

            //Close the reader, then the connection
            reader.Close();
            conn.Close();

            //Await user input.
            Console.ReadLine();
        }

See Also

Reference

Retrieving Data from an Analytical Data Source
Retrieving Data Using the CellSet
Retrieving Data Using the AdomdDataReader

Help and Information

Getting SQL Server 2005 Assistance