17.9.3. Virtual directory Setup
To make a virtual directory work as an XMLA data provider it is enough to grant execute permissions on Discover and Execute procedures to the SQL account specified for SOAP execution in this virtual directory.
For compliance with XMLA, the virtual directory attribute elementFormDefault must have the value "qualified" in the XMLA SOAP end point.
Here are the steps for setting up a virtual directory for use with XMLA:
create user "XMLA";
user_set_qualifier ('XMLA', 'XMLA');
VHOST_REMOVE (lpath=>'/XMLA');
VHOST_DEFINE (lpath=>'/XMLA', ppath=>'/SOAP/', soap_user => 'XMLA',
soap_opts => vector ('ServiceName', 'XMLAnalysis', 'elementFormDefault', 'qualified'));
grant execute on DB.."Discover" to "XMLA";
grant execute on DB.."Execute" to "XMLA";
Example 17.36. Simple Discovery Request/Response examples
The following example shows a simple request for discovering data sources and response from server.
<?xml version="1.0"?>
<SOAP:Envelope SOAP:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP:Body>
<cli:Discover xmlns:cli="urn:schemas-microsoft-com:xml-analysis">
<RequestType xsi:type="xsd:string" dt:dt="string">DISCOVER_DATASOURCES</RequestType>
<Restrictions xsi:nil="1"/>
<Properties xsi:nil="1" />
</Properties>
</cli:Discover>
</SOAP:Body>
</SOAP:Envelope>
<SOAP:Envelope SOAP:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP:Body>
<cli:DiscoverResponse xmlns:cli="urn:schemas-microsoft-com:xml-analysis">
<Result xmlns="">
<root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
<n0:schema xmlns:n0="http://www.w3.org/2001/XMLSchema"
targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset"
elementFormDefault="qualified">
<n0:element name="root" type="n2:root" xmlns:n2="urn:schemas-microsoft-com:xml-analysis:rowset"/>
<n0:complexType name="root">
<n0:sequence minOccurs="0" maxOccurs="unbounded">
<n0:element name="row" type="n2:row" xmlns:n2="urn:schemas-microsoft-com:xml-analysis:rowset"/>
</n0:sequence>
</n0:complexType>
<n0:complexType name="row">
<n0:choice maxOccurs="unbounded" minOccurs="0">
<n0:element name="DataSourceName" type="string" xmlns:n2="urn:schemas-microsoft-com:xml-sql" n2:field="DataSourceName" nillable="0"/>
<n0:element name="DataSourceDescription" type="string" xmlns:n2="urn:schemas-microsoft-com:xml-sql" n2:field="DataSourceDescription" nillable="0"/>
<n0:element name="URL" type="string" xmlns:n2="urn:schemas-microsoft-com:xml-sql" n2:field="URL" nillable="0"/>
<n0:element name="DataSourceInfo" type="string" xmlns:n2="urn:schemas-microsoft-com:xml-sql" n2:field="DataSourceInfo" nillable="0"/>
<n0:element name="ProviderName" type="string" xmlns:n2="urn:schemas-microsoft-com:xml-sql" n2:field="ProviderName" nillable="0"/>
<n0:element name="ProviderType" xmlns:n2="urn:schemas-microsoft-com:xml-sql" n2:field="ProviderType">
<n0:complexType>
<n0:sequence minOccurs="0" maxOccurs="unbounded">
<n0:any processContents="lax" maxOccurs="unbounded"/>
</n0:sequence>
</n0:complexType>
</n0:element>
<n0:element name="AuthenticationMode" type="string" xmlns:n2="urn:schemas-microsoft-com:xml-sql" n2:field="AuthenticationMode" nillable="0"/>
</n0:choice>
</n0:complexType>
</n0:schema>
<row xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
<DataSourceName>Local Server</DataSourceName>
<DataSourceDescription>Virtuoso Server</DataSourceDescription>
<URL>http://example.com/XMLA</URL>
<DataSourceInfo>DSN=__local</DataSourceInfo>
<ProviderName>Virtuoso XML for Analysis</ProviderName>
<ProviderType>
<TDP xsi:type="xsd:string" dt:dt="string"/>
</ProviderType>
<AuthenticationMode>Unauthenticated</AuthenticationMode>
</row>
</root>
</Result>
</cli:DiscoverResponse>
</SOAP:Body>
</SOAP:Envelope>
The following example shows a query against the Northwind's database's Customers table
Request/Response
<?xml version="1.0"?>
<SOAP:Envelope SOAP:encodingType="http://schemas.xmlsoap.org/soap/encoding/" SOAP:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:ref="http://schemas.xmlsoap.org/ws/2002/04/reference/" xmlns:dt="urn:schemas-microsoft-com:datatypes">
<SOAP:Body>
<cli:Execute xmlns:cli="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Statement xsi:type="xsd:string" dt:dt="string">select CustomerID , CompanyName from Demo..Customers where CustomerID like 'A%'</Statement>
</Command>
<Restrictions xsi:nil="1"/>
<Properties>
<PropertyList>
<DataSourceInfo xsi:type="xsd:string" dt:dt="string">DSN=__local</DataSourceInfo>
<Password type="http://www.w3.org/2001/XMLSchema:string" dt="string" >demo</Password>
<UserName type="http://www.w3.org/2001/XMLSchema:string" dt="string" >demo</UserName>
</PropertyList>
</Properties>
</cli:Execute>
</SOAP:Body>
</SOAP:Envelope>
<SOAP:Envelope SOAP:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:dt="urn:schemas-microsoft-com:datatypes" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:xenc="http://www.w3.org/2001/04/xmlenc#" xmlns:wsse="http://schemas.xmlsoap.org/ws/2002/07/secext" xmlns:ref="http://schemas.xmlsoap.org/ws/2002/04/reference/" xmlns:wsdl="services.wsdl">
<SOAP:Body>
<cli:ExecuteResponse xmlns:cli="urn:schemas-microsoft-com:xml-analysis">
<Result xmlns="">
<root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
<n0:schema xmlns:n0="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" elementFormDefault="qualified">
<n0:element name="root" type="n2:root" xmlns:n2="urn:schemas-microsoft-com:xml-analysis:rowset"/>
<n0:complexType name="root">
<n0:sequence minOccurs="0" maxOccurs="unbounded">
<n0:element name="row" type="n2:row" xmlns:n2="urn:schemas-microsoft-com:xml-analysis:rowset"/>
</n0:sequence>
</n0:complexType>
<n0:complexType name="row">
<n0:choice maxOccurs="unbounded" minOccurs="0">
<n0:element name="CustomerID" type="string" xmlns:n2="urn:schemas-microsoft-com:xml-sql" n2:field="CustomerID" nillable="0"/>
<n0:element name="CompanyName" type="string" xmlns:n2="urn:schemas-microsoft-com:xml-sql" n2:field="CompanyName" nillable="1"/>
</n0:choice>
</n0:complexType>
</n0:schema>
<row xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
<CustomerID>ALFKI</CustomerID>
<CompanyName>Alfreds Futterkiste</CompanyName>
</row>
<row xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
<CustomerID>ANATR</CustomerID>
<CompanyName>Ana Trujillo Emparedados y helados</CompanyName>
</row>
<row xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
<CustomerID>ANTON</CustomerID>
<CompanyName>Antonio Moreno Taquería</CompanyName>
</row>
<row xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
<CustomerID>AROUT</CustomerID>
<CompanyName>Around the Horn</CompanyName>
</row>
</root>
</Result>
</cli:ExecuteResponse>
</SOAP:Body>
</SOAP:Envelope>
The following example shows a simple request for "Execute" operation whit Virtuoso extension. Sources and response from server.
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/" xmlns:n1="http://schemas.xmlsoap.org/soap/envelope/" xmlns:n2="http://schemas.xmlsoap.org/soap/envelope/" n1:encodingType="http://schemas.xmlsoap.org/soap/encoding/" n2:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<Body xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Statement xmlns:n1="http://www.w3.org/2001/XMLSchema-instance" xmlns:n2="urn:schemas-microsoft-com:datatypes" n1:type="http://www.w3.org/2001/XMLSchema:string" n2:dt="string">select CustomerID, CompanyName from Demo..Customers</Statement>
</Command>
<Properties>
<PropertyList>
<DataSourceInfo xmlns:n1="http://www.w3.org/2001/XMLSchema-instance" xmlns:n2="urn:schemas-microsoft-com:datatypes" n1:type="http://www.w3.org/2001/XMLSchema:string" n2:dt="string">Local_Instance</DataSourceInfo>
<UserName xmlns:n1="http://www.w3.org/2001/XMLSchema-instance" xmlns:n2="urn:schemas-microsoft-com:datatypes" n1:type="http://www.w3.org/2001/XMLSchema:string" n2:dt="string">dba</UserName>
<Password xmlns:n1="http://www.w3.org/2001/XMLSchema-instance" xmlns:n2="urn:schemas-microsoft-com:datatypes" n1:type="http://www.w3.org/2001/XMLSchema:string" n2:dt="string">dba</Password>
<direction xmlns:n1="http://www.w3.org/2001/XMLSchema-instance" xmlns:n2="urn:schemas-microsoft-com:datatypes" n1:type="http://www.w3.org/2001/XMLSchema:string" n2:dt="string">backward</direction>
<skip xmlns:n1="http://www.w3.org/2001/XMLSchema-instance" xmlns:n2="urn:schemas-microsoft-com:datatypes" n1:type="http://www.w3.org/2001/XMLSchema:int" n2:dt="int">1</skip>
<n-rows xmlns:n1="http://www.w3.org/2001/XMLSchema-instance" xmlns:n2="urn:schemas-microsoft-com:datatypes" n1:type="http://www.w3.org/2001/XMLSchema:int" n2:dt="int">3</n-rows>
<return-bookmark xmlns:n1="http://www.w3.org/2001/XMLSchema-instance" xmlns:n2="urn:schemas-microsoft-com:datatypes" n1:type="http://www.w3.org/2001/XMLSchema:int" n2:dt="int">1</return-bookmark>
</PropertyList>
</Properties>
</Execute>
</Body>
</Envelope>
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/" xmlns:n1="http://schemas.xmlsoap.org/soap/envelope/" n1:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<Body xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<ExecuteResponse xmlns="urn:schemas-microsoft-com:xml-analysis">
<return xmlns="urn:schemas-microsoft-com:xml-analysis">
<root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
<schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" elementFormDefault="qualified">
<element xmlns="http://www.w3.org/2001/XMLSchema" name="root" type="urn:schemas-microsoft-com:xml-analysis:rowset:root"/>
<complexType xmlns="http://www.w3.org/2001/XMLSchema" name="root">
<sequence xmlns="http://www.w3.org/2001/XMLSchema" minOccurs="0" maxOccurs="unbounded">
<element xmlns="http://www.w3.org/2001/XMLSchema" name="row" type="urn:schemas-microsoft-com:xml-analysis:rowset:row"/>
</sequence>
</complexType>
<complexType xmlns="http://www.w3.org/2001/XMLSchema" name="row">
<choice xmlns="http://www.w3.org/2001/XMLSchema" maxOccurs="unbounded" minOccurs="0">
<element xmlns="http://www.w3.org/2001/XMLSchema" xmlns:n3="urn:schemas-microsoft-com:xml-sql" name="BOOKMARK" type="string" n3:field="BOOKMARK" nillable="1"/>
<element xmlns="http://www.w3.org/2001/XMLSchema" xmlns:n3="urn:schemas-microsoft-com:xml-sql" name="CustomerID" type="string" n3:field="CustomerID" nillable="0"/>
<element xmlns="http://www.w3.org/2001/XMLSchema" xmlns:n3="urn:schemas-microsoft-com:xml-sql" name="CompanyName" type="string" n3:field="CompanyName" nillable="1"/>
</choice>
</complexType>
</schema>
<row xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
<BOOKMARK xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">wbwCtQExvAI=</BOOKMARK>
<CustomerID xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">AROUT</CustomerID>
<CompanyName xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">Around the Horn</CompanyName>
</row>
<row xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
<BOOKMARK xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">wbwCtQExvAE=</BOOKMARK>
<CustomerID xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">ANTON</CustomerID>
<CompanyName xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">Antonio Moreno Taquera</CompanyName>
</row>
<row xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
<BOOKMARK xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">wbwCtQExvAA=</BOOKMARK>
<CustomerID xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">ANATR</CustomerID>
<CompanyName xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">Ana Trujillo Emparedados y helados</CompanyName>
</row>
</root>
</return>
</ExecuteResponse>
</Body>
</Envelope>