XMLA Examples

The following examples illustrate an XMLA response and request.

This is an example of a flattened rowset request. To flatten the result, you must use Tabular format in the PropertyList element, as shown in the example.

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" 
  xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance" 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <SOAP-ENV:Body> 
  <Execute xmlns="urn:schemas-microsoft-com:xml-analysis" 
   SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
   <Command>
    <Statement>
     WITH MEMBER [Year].[calctest] AS '4' 
     SELECT NON EMPTY { [Profit] } ON COLUMNS,
     NON EMPTY {[Year].ALLMEMBERS } ON ROWS 
     FROM Sample.Basic
    </Statement>
   </Command>
   <Properties>
    <PropertyList>
     <DataSourceInfo>Provider=Essbase;Data Source=localhost
     </DataSourceInfo>
     <Catalog>Sample</Catalog>
     <Format>Tabular</Format>
     <AxisFormat>TupleFormat</AxisFormat>
    </PropertyList>
   </Properties>
  </Execute>
 </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

An example of a flattened rowset response:

<?xml version="1.0"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
 SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
  <SOAP-ENV:Body>
   <m:ExecuteResponse xmlns:m="urn:schemas-microsoft-com:xml-analysis">
    <m:return
     SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
      <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xmlns:xsd="http://www.w3.org/2001/XMLSchema">
       <xsd:schema xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" 
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
        targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" 
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" 
        elementFormDefault="qualified">						
        <xsd:element name="root">
         <xsd:complexType>
          <xsd:sequence minOccurs="0" maxOccurs="unbounded">
           <xsd:element name="row" type="row" />
          </xsd:sequence>
         </xsd:complexType>
        </xsd:element>
        <xsd:complexType name="row">
         <xsd:sequence minOccurs="0" maxOccurs="unbounded">
          <xsd:element name="column1" type="xsd:string" 
           sql:field="[Year].Levels(1).[MEMBER_CAPTION]" minOccurs="0"/>
          <xsd:element name="column2" type="xsd:string" 
           sql:field="[Year].Levels(2).[MEMBER_CAPTION]" minOccurs="0"/>
          <xsd:element name= "column3" type="xsd:double" 
           sql:field= "[Profit]" minOccurs="0"/>
         </xsd:sequence>
        </xsd:complexType>
       </xsd:schema>

       <row>
        <column3>105522.000000</column3>
       </row>
       <row>
        <column1>Qtr1</column1>
        <column3>24703.000000</column3>
       </row>
       <row>
        <column1>Qtr1</column1>
        <column2>Jan</column2>
        <column3>8024.000000</column3>
       </row>
       <row>
        <column1>Qtr1</column1>
        <column2>Feb</column2>
        <column3>8346.000000</column3>
       </row>
       <row>
        <column1>Qtr1</column1>
        <column2>Mar</column2>
        <column3>8333.000000</column3>
       </row>
       <row>
        <column1>Qtr2</column1>
        <column3>27107.000000</column3>
       </row>
       <row>
        <column1>Qtr2</column1>
        <column2>Apr</column2>
        <column3>8644.000000</column3>
       </row>
       <row>
        <column1>Qtr2</column1>
        <column2>May</column2>
        <column3>8929.000000</column3>
       </row>
       <row>
        <column1>Qtr2</column1>
        <column2>Jun</column2>
        <column3>9534.000000</column3>
       </row>
       <row>
        <column1>Qtr3</column1>
        <column3>27912.000000</column3>
       </row>
       <row>
        <column1>Qtr3</column1>
        <column2>Jul</column2>
        <column3>9878.000000</column3>
       </row>
       <row>
        <column1>Qtr3</column1>
        <column2>Aug</column2>
        <column3>9545.000000</column3>
       </row>
       <row>
        <column1>Qtr3</column1>
        <column2>Sep</column2>
        <column3>8489.000000</column3>
       </row>
       <row>
        <column1>Qtr4</column1>
        <column3>25800.000000</column3>
       </row>
       <row>
        <column1>Qtr4</column1>
        <column2>Oct</column2>
        <column3>8653.000000</column3>
       </row>
       <row>
        <column1>Qtr4</column1>
        <column2>Nov</column2>
        <column3>8367.000000</column3>
       </row>
       <row>
        <column1>Qtr4</column1>
        <column2>Dec</column2>
        <column3>8780.000000</column3>
       </row>
       <row>
        <column1>calctest</column1>
        <column3>4.000000</column3>
       </row>
      </root>
     </m:return>
    </m:ExecuteResponse>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>