Retrieving data using XmlReader

The XmlReader class, which is part of the System.Xml namespace for the .NET Framework Class Library, is similar to the AdomdDataReader class. Both classes provide fast, non-cached, forward-only access to data. If you don't need an in-memory, analytical view of the data by using the CellSet object, the XmlReader object is perfect for retrieving XML data, especially for large quantities of data. Because XmlReader streams data, it doesn't have to retrieve and cache all the data before exposing the data to the caller. In contrast, if you use a CellSet object to convert the XML for Analysis response into an analytical object model representation, you must wait for all the data to be cached.

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 retrieve data by using the XmlReader class, follow these steps:

1. Create a new instance of the object.

   Call the Execute or ExecuteXmlReader method on the AdomdCommand object to create an XmlReader instance.

2. Retrieve data.

   After the command runs the query and returns an XmlReader, parse the data and metadata. The XML data and metadata are 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.

   Call the Close method when you finish using the XmlReader object. While an XmlReader is open, it has exclusive use of the AdomdConnection object that runs the command. You can't run any commands by using that <`xref:Microsoft.AnalysisServices.AdomdClient.AdomdConnection>, including creating another XmlReader or AdomdDataReader, until you close the original XmlReader.

Retrieve data from XmlReader

The following example runs a command, retrieves data as an XmlReader, and writes the contents 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();
        }