Get Value from XMLA result file in NAnt with xmlpeek

If I got paid for every hour spent wrangling with XML…

Oh wait, I do

Problem:

• Read a scalar-result out of an XMLA response file from an MDX query run against a cube.
• Store it in a NAnt property.

Jiest of my XMLA file:

<return xmlns="urn:schemas-microsoft-com:xml-analysis">
  <root xmlns="urn:schemas-microsoft-com:xml-analysis:mddataset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<CellData xmlns="urn:schemas-microsoft-com:xml-analysis:mddataset">
      <Cell CellOrdinal="0">
        <Value xsi:type="xsd:double" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">1.09</Value>
        <FmtValue>1.09</FmtValue>
      </Cell>
    </CellData>
  </root>

Surprisingly I’ve had good luck parsing and reading XMLA files from Java, so I figured this would be a quick solution using NAnt’s xmlpeek.  Taking a look I can just create an XPath statement of “/return/root/CellData/Cell/FmtValue”. Guess again!  After much banging my head I found a cheating solution using the XPath: “//*[local-name()=’FmtValue’]/text()” which just says “get me the local node named FmtValue anywhere in the xml doc”.  Not pretty, plus it worked on every online tester I found, but still returned nothing in NAnt – even though it says “1 node found matching…”.  I had popped a couple namespaces in to the xmlpeek task, but I still don’t really understand them…

Somehow this xml/xpath evaluator makes it all very clear (toggle the “XPath” option), it gives a handy Namespace Reference:

a,b,c??  I don’t know either, but if it doesn’t have an obvious prefix I guess you just make it up?  At any rate, I added the above namespaces verbaetum to my xmlpeek namespaces, then I was able to construct a “proper” XPath to my liking: “/a:return/b:root/b:CellData/b:Cell/b:Value/text()”.  With NAnt it does the text() evaluation on it’s own so you only need “/a:return/b:root/b:CellData/b:Cell/b:Value”.  With that unlocked, you should be able to peek and poke any item within an XMLA statement spit out from Analysis Services.

	<xmlpeek
	file = "result.xmla"
	xpath = "/a:return/b:root/b:CellData/b:Cell/b:Value"
	property="your.property"
	verbose="true" >
	<namespaces>
		<namespace prefix="a" uri="urn:schemas-microsoft-com:xml-analysis" />
		<namespace  prefix="b" uri="urn:schemas-microsoft-com:xml-analysis:mddataset" />
		<namespace prefix="xsi" uri="http://www.w3.org/2001/XMLSchema-instance"  />
		<namespace prefix="xsd" uri="http://www.w3.org/2001/XMLSchema" />
		<namespace prefix="c" uri="http://schemas.microsoft.com/analysisservices/2003/engine" />
	</namespaces>
</xmlpeek>