XML Namespaces and the ADOMD XML Reader Result Set

I’m building a generic helper class to flatten an ADOMD data set to a data table for easier consumption by default and third party .NET controls.  I can’t rely on the Load method of the data table, nor the CellSet object as in some cases not all the information is persisted; datatypes and top level hierarchies to name two.

To persist all of the information I need to traverse the actual xml dataset returned by the ExecuteXMLReader method of the ADOMDCommand object.

I don’t use xml enough these days to remember all the nuances with namespaces and it took me a little while to work out what to set the namespaces to in order for the ‘SelectNodes’ method to work.

Below is the code snippet I used to ensure the output from the ExecuteXmlReader method is fully traversable.

// Execute the command returning the XML data set
XmlReader reader = command.ExecuteXmlReader();

// Load the object into an XML document
XmlDocument xml = new XmlDocument();
xml.Load(reader);

// Create an XML namespace manager
System.Xml.XmlNamespaceManager nsManager=new XmlNamespaceManager(xml.NameTable);

// Add the namepsaces to the manager
nsManager.AddNamespace(“defaultNS”,”urn:schemas-microsoft-com:xml-analysis:mddataset”);
nsManager.AddNamespace(“xsi”,http://www.w3.org/2001/XMLSchema-instance);
nsManager.AddNamespace(“xsd”,http://www.w3.org/2001/XMLSchema);

// Return a list of nodes that represent the row information
XmlNodeList rows = xml.SelectNodes(“defaultNS:root/defaultNS:Axes/defaultNS:Axis[@name =’Axis1′]”, nsManager);

This kb article helped me out a bit:
http://support.microsoft.com/default.aspx?scid=kb;en-gb;q318545

There are some more goodies included in my helper class that I will blog about in the future, once it’s stable !