Using XQuery to Retrieve Data from a Web Service

The Freeform SQL Editor can be used to connect to third-party web services to retrieve and report on data. However, you cannot use SQL to report on web services. Instead, you must use XQuery statements.

XQuery is a language that is used to retrieve data from documents or data sources that can be viewed as XML. Since you create your own XQuery statements to create reports with the Freeform SQL Editor, an understanding of how to create and use XQuery statements is essential. Some basic techniques of how to create XQuery statements are provided below. You can also use the XQuery Editor and Generator (XEG) to generate an XQuery statement to query a given data source. For steps to use XEG, see Generating an XQuery Statement with XQuery Editor and Generator (XEG).

An XQuery statement, that is used to create a report in MicroStrategy to report on data retrieved from a web service, can be separated into two logical parts. These two logical parts separate the tasks of connecting to the data and returning the data so it can be integrated into MicroStrategy. This separation of tasks in the structure of the XQuery statement is described below and shown in the following image:

Connecting to a web service and requesting data: The first part of an XQuery statement includes all the necessary definitions and information to connect to a web service. This includes defining any functions required for the XQuery statement, determining whether to use REST architecture or SOAP protocol, creating a request to retrieve data from the web service, and so on. These definitions are highlighted with a red box with a solid border in the example XQuery statement above.
The definitions required in this part of the XQuery depend on the web service that you access and the data that you plan to retrieve. However, the following parts are required for any XQuery statement:
- Defining the REST architecture or SOAP protocol: In the example XQuery statement shown above, the line declare namespace mstr-xquery = "http://www.microstrategy.com/xquery/ rest-functions"; defines whether the XQuery statement uses the REST architecture or SOAP protocol. MicroStrategy provides the following sets of functions to support querying third-party web services:
Returning data from a web service so that it can be integrated into MicroStrategy: The second part of an XQuery statement determines how to return the data retrieved from a web service so that it can be integrated into MicroStrategy. These definitions are highlighted with a blue box with a dashed border in the example XQuery statement above.
You must return the data in a table format, which is why the XQuery statement shown above starts with return <Table>. This ensures that the data is in a format that can be understood in MicroStrategy.
The <ColumnHeader> section of the statement defines the columns of data that are included in the table. The XQuery statement shown above creates columns for city, state, zip code, area code, and time zone data. These columns become the attributes and metrics of the MicroStrategy report.
The data for these columns is then retrieved in the <Data> section of the statement. How you create an XQuery statement to return data depends on the third-party web service's structure, as well as whether you plan to manipulate the data using XQuery prior to integrating it into MicroStrategy.

Implementing Security Within an XQuery Statement

Web services can have various security requirements to access the data that the web service provides. MicroStrategy's support of data retrieval from web services using XQuery statements supports most of the web services security mechanisms available. A web service can require HTTP authentication, Web Services Security, or standard user name and password security requirements, which are all described below:

HTTP authentication provides authentication using one of several authentication modes. MicroStrategy supports the use of basic and digest HTTP authentication for REST functions, and only basic HTTP authentication for SOAP functions. The user name and password that you provide as part of a database login is automatically used by MicroStrategy to complete any HTTP authentication required by the web service. Creating a database login is part of the steps required to create a database instance to access a web service, which is described in Allowing Connections to Web Services in a Project. A default database login is associated with the web service that you are connecting to. You can also use connection mapping to determine the database login used for each MicroStrategy user. For information on configuring connection mapping, see the System Administration Help. If the web service uses HTTP authentication, you cannot use Web Services Security authentication, which is described below.
The example provided in Using XQuery to Retrieve Data from a Web Service is an example of using REST functions with the parameters URI. In that example, the parameter URI provides the URL to access the web service as well as define the data to return. This parameter supports both basic and digest HTTP authentication.
An additional example of using REST functions that can support both basic and digest HTTP authentication is shown below:
This type of REST function requires the following parameters:
- URI is the Internet address required to access the web service.
- Content-Type is the Internet media type to use for the web form accessed by the request for data.
- Payload is the request for data from the web service. For the example above, this requests data on the state of Virginia.
Web Services Security (WS-Security) provides web services authentication, as defined in the WS-Security standard. MicroStrategy utilizes the mstr-soap:post() external function to make SOAP web services calls. This function uses Axis2/C to invoke web services and uses Rampart/C to support WS-Security authentication. Using these XML-based assertions, you can enforce authentication and ensure the confidentiality and integrity of the message. If the web service uses WS-Security authentication, HTTP authentication must be defined as anonymous for the web service.
With WS-Security you can support security features such as:
- Authentication with Username tokens.
- Authentication with X.509 certificates.
- Inclusion of Timestamp tokens.
- SOAP message encryption.
- SOAP message signature.
- Message replay detection.
The MicroStrategy function required to utilize WS-Security has the following syntax and requirements:
mstr-soap:post($URI, $SoapInfo, $WSSPolicy, $SoapCustomHeader, $SoapPayload)
Where:
- URI is the Internet address required to access the web service.
- SoapInfo is the version and action information of the SOAP request required to access the web service. For example, the following is a definition of SoapInfo for a web service SOAP request:
  let $soap-version := element SOAPInfo { element SOAPVersion{ 'SOAP11' }, element SOAPAction{ ' ' } }
  The SoapInfo element shown above is defined as two elements:
  - SOAPVersion, which specifies the SOAP Version of the request. The two possible values for this element are 'SOAP11' or 'SOAP12'.
  - SOAPAction, which specifies the required action for the web service.
- WSSPolicy is the location of the web service's security policy file, which conforms to the WS-Security and WS-Security policy assertion language along with Rampart/C extensions.
  A policy file can be extracted from the Web Services Description Language (WSDL) supplied by a SOAP file. Most web services provide a way to view the WSDL in a web browser. The policy file can also be created based on the security requirements of a SOAP web service. Regardless of how you create the policy file, the file may require manual changes to fully configure the required Rampart/C behavior. This MicroStrategy documentation assumes you have a working knowledge of WS-Security, Axis2/C, and Rampart/C technologies. While a comprehensive explanation of these technologies is not provided, the following links are provided for further details and background information on these subjects:
  - The specification for WS-Security can be found at http://docs.oasis-open.org/wss/2004/01/ oasis-200401-wss-soap-message-security-1.0.pdf.
  - The specification for WS-Security policy language can be found at http://specs.xmlsoap.org/ws/2005/07/securitypolicy/ ws-securitypolicy.pdf.
  - Information on how Rampart/C can be configured using WS-Security policy language can be found at http://ws.apache.org/rampart/c/docs/configurations.html.
- A web service can require additional authentication requirements to access the data. If a web service requires additional authentication through the use of a user name and password within the body of the request, you can provide a valid user name and password in one of the following ways:

Using Security Filters to Restrict Results from an XQuery Source

By integrating web services data into MicroStrategy using XQuery reports, you can also take advantage of standard MicroStrategy security features such as security filters. You can include a security filter as part of an XQuery report to restrict the web services data that a MicroStrategy user can access.

To use security filters to restrict access to web services data returned on XQuery reports, you must do the following:

The XQuery statement must use the security filter as part of the definition in the where clause. As an example, part of an XQuery statement is provided below, including the security filter as part of the XQuery statement.
```
let $example := XML definition of the third-party web service
```
```
return
```
```
<Table>
```
```
 <ColumnHeaders>
```
```
<ColumnHeader name='FIRST_NAME'type='xs:string'/>
```
```
<ColumnHeader name='LAST_NAME'type='xs:string'/>
```
```
<ColumnHeader name='ADDRESS'type='xs:string'/>
```
```
 </ColumnHeaders>
```
```
<Data>{for $example1 in $example/Employees/Employee
```
```
 where $example1/Security Filter
```
```
return
```
```
<Row>
```
<FIRST_NAME>{fn:data($example1/FIRST_NAME)}</FIRST_NAME>
<LAST_NAME>{fn:data($example1/LAST_NAME)}</LAST_NAME>
<ADDRESS>{fn:data($example1/ADDRESS)}</ADDRESS>
```
</Row>
```
```
</Data></Table>
```
In the example XQuery statement above, the text Security Filter acts as a placeholder for the security filter, which is applied to the Employee data.
You must create the security filter placeholder within the XQuery report. These must be created using the same techniques used to create a security filter placeholder in Freeform SQL reports, which is described in Security for Data Access and Security for Data Access. Additional details related to creating these security filters in XQuery reports are provided below:

The attribute mappings for the security filter placeholder should match the attribute form that is mapped to the web services data, and thus can be used to restrict data in the XQuery statement. For the example above, you can include the FIRST_NAME attribute form of the Employee attribute as the attribute mapping for the security filter.
The replacement string for the security filter placeholder must match the syntax for your XQuery statement. This replacement string defines the text in your XQuery statement that is replaced when resolving the security filter. In the example XQuery statement provided above, only the text Security Filter is replaced. To support the possibility that a user without a security filter runs the XQuery report, you can define the replacement string as:
```
where $example1/Security Filter
```
By using this replacement string, when a user without a security filter runs the XQuery report, the entire where clause of the XQuery statement is removed, allowing the report to use correct XQuery syntax.

Using Kerberos to Authenticate XQuery Data Sources

You can use Kerberos to authenticate XQuery data sources. There are two ways to configure Kerberos integrated authentication for your XQuery data sources:

With pass-through: Allows Kerberos credentials for each MicroStrategy Web user to be passed through to the data source for authentication.

If you are already using integrated authentication in your MicroStrategy project for web users, you only need to enable the XQuery data source to use integrated authentication. For steps, see Enabling integrated authentication to data sources in System Administration Help.
For more information and steps on how to use integrated authentication, see Enabling integrated authentication in System Administration Help.

Without pass-through: Use a single Kerberos certificate stored on Intelligence Server to authenticate all users to the data source.

To use this method, you must have completed the required configuration for the Kerberos 5 client o Intelligence Server. For more information and steps, see Configuring Intelligence Server Universal on UNIX/Linux for integrated authentication in System Administration Help.
If you have already setup your XQuery data source with Kerberos pass-through, you will need to clear the Use network login ID (Windows authentication) check box in the "Database Logins" dialog box. The Login ID is still required to have a value however, it will not be used when Kerberos without pass-through is configured.

To Use Kerberos Without Pass-Through to Authenticate an XQuery Data Source

In MicroStrategy Developer, log in to a project source, using a user account with administrative privileges.
In the Folder List, expand the project source, expand Administration, expand Configuration Managers, and select Database Instances.
Double-click to open the settings for your existing XQuery data source.
In the Database connection area, click Modify… The "Database Connections" dialog box opens.
Click the Advanced tab and add the following text to the Additional Connection String Parameters textbox:
```
MECHANISM=KerberosWithoutPassthrough
```
Click OK.

Using an XQuery Source to Authenticate Users for a MicroStrategy Project

Using database authentication, you can authenticate against an XQuery source when users log in to a project, and save any tokens returned by the authentication process. These tokens can then be used in XQuery reports for the duration of a given user's MicroStrategy session. This authentication technique has the added benefit of keeping your MicroStrategy project secure by authenticating upon login, while allowing the use of tokens to avoid the need to include authentication details in every XQuery report.

The steps below show you how to configure your environment to support authenticating all XQuery reports for a given project.

An XQuery statement that returns various tokens from a web service, which can then be used in XQuery reports as authentication tokens, in place of authenticating with every request. A common scenario is to return two tokens, including a session URL (a URL for subsequent requests) and a session Token (which identifies the caller as an already authenticated user). You can return up to four distinct pieces of information from a web service.

To Use an XQuery Source to Authenticate Users for a MicroStrategy Project

In MicroStrategy Developer, log in to a project source, using a user account with administrative privileges.
In the Folder List, expand the project source, expand Administration, expand Configuration Managers, and select Database Instances.
Choose File > New > Database Instance. The Database Instances Editor opens.
On the General tab, in the Database instance name field, enter a descriptive name for the database instance.
From the Database connection type drop-down list, select XQuery.
In the Database connection area, click New. The Database Connections dialog box opens.
In the Database connection name field, enter a descriptive name for the database connection.
In the Default database login name area, click New. The Database Logins dialog box opens.
In the Database login field, enter a descriptive name for the database login.
Choose the login option corresponding to the authentication method accepted by the web service:

If the web service that you plan to connect to requires a user name and password, you must type a valid user name and password in the Login ID and Password fields. For information on how you can include this security in your report so that you can access the web services data, see Implementing Security Within an XQuery Statement.
If the web service that you plan to connect to does not require a user name and password, you can type any user name in the Login ID field, and leave the Password field blank.

Click OK. You are returned to the Database Connections dialog box.
In the Default database login name area, select the new database login that you created.

On the Advanced tab, in the XQuery authentication parameters field, type the XQuery statement to return the required authentication credentials from the web service. A complete XQuery statement can be included in this field, but the return statement must have the following structure:

<Table>

  <ColumnHeaders>

   <ColumnHeader name="Token1" type="xsd:string" />

   <ColumnHeader name="Token2" type="xsd:string" />

   <ColumnHeader name="Token3" type="xsd:string" />

   <ColumnHeader name="Token4" type="xsd:string" />

  <ColumnHeaders>

<Data>{

<Row>

<Token1>Example1</Token1>

<Token2>Example2</Token2>

<Token3>Example3</Token3>

<Token4>Example4</Token4>

</Row>

}</Data>

</Table>

The syntax above returns four separate credentials, which can be supplied in the Example1, Example2, Example3, and Example4 locations respectively. If fewer than four credentials are required, you can delete the associated <ColumnHeader> tags and <TokenN> tags. For example, the following XQuery statement makes a call to a SOAP web service and returns a session ID and a URI to be used for subsequent connections to a web service:

	declare namespace mstr-soap =

	http://www.microstrategy.com/xquery/soap-functions;

	declare function mstr-soap:post($args) external;

	let $username := '#?DBLOGIN_UID?#'

	let $password := '#?DBLOGIN_PWD?#'

	let $uri := 'http://ExampleURI'

	let $soap-version := element SOAPInfo {element

	SOAPVersion {'SOAP11'}, element SOAPAction{''}}

	let $ws-sec-policy := ''

	let $soap-custom-header := ''

	let $soap-payload := concat('<login

	xmlns="ExampleNamespace">

	<username>', $username,'</username>

	<password>', $password,'</password></login>')

	let $args := ($uri, $soap-version, $ws-sec-policy,

	$soap-custom-header, $soap-payload)

	let $response := mstr-soap:post($args)//loginResponse

	let $sessionToken := $response//sessionId

	let $sessionURI := $response//serverUrl

	return

	<Table>

	<ColumnHeaders>

  	<ColumnHeader name="Token1" type="xsd:string" />

  	<ColumnHeader name="Token2" type="xsd:string" />

	</ColumnHeaders>

	<Data>{

  	<Row>

   		<Token1>{$sessionToken}</Token1>

   		<Token2>{$sessionURI}</Token2>

  	</Row>

	}</Data>

	</Table>

Click OK. You are returned to the Database Instances Editor.
In the Database connection area, select the new database connection that you created, and click OK. The database instance is created.
To make the database instance available for Freeform SQL reports, right-click a project and select Project Configuration. The Project Configuration Editor opens.
Expand Database instances, and select SQL Data warehouses.
From the list of available database instances, select the check box for the database instance that you created in the previous steps. If you are prompted to configure data mart optimization, click No.
Within the Database Instances category, expand Authentication, and select Metadata.
From the drop-down list, select the database instance you created earlier in this procedure.
Click OK.
Since you must use database authentication, you must perform some additional configurations:

Modify the MicroStrategy user group Public/Guest so that it has the necessary privileges to view the projects that use database authentication.
Modify any required MicroStrategy user accounts to link them to the web service account. In the User Editor, in the Authentication: Metadata category, type the login ID in the Database Login field.
Create a new project source or modify an existing project source to use database authentication.
For additional details on supporting database authentication in MicroStrategy, see the System Administration Help.

You must also ensure that each XQuery report references the tokens that store the authentication credentials. The tokens can be accessed by using system prompts in MicroStrategy XQuery reports. These system prompts are stored in the project folder Public Objects/Prompts/System prompts and are named Token 1, Token 2, Token 3, and Token 4. Each token corresponds to the respective column returned by the XQuery authentication parameters.
Within an XQuery report, you can define variables in an XQuery statement to retrieve the authentication credentials stored in the tokens. For example, the sessionid and uri variables shown below retrieve the authentication credentials from the Token 1 and Token 2 system prompts.
To insert a prompt in an XQuery statement, within the Freeform SQL Editor, from the Edit menu, select Insert Prompt. You can then select the relevant Token prompt from the list of system prompts. You must include these tokens in any XQuery report that requires this authentication to connect to a web service.

This completes the steps to configure authentication for all XQuery reports for a given project. For additional details on how to create XQuery reports to return data from a web service, see Creating a Report to Analyze Web Service Data.

Generating an XQuery Statement with XQuery Editor and Generator (XEG)

MicroStrategy Developer includes the XQuery Editor and Generator (XEG) to automatically generate XQuery statements. You can provide XEG with appropriate input parameters, such as the input structure of the web service and any search parameters and data transformations, and XEG generates XQuery statements to query the web service.

To use XEG, you must be on a machine that has MicroStrategy Developer installed.

You must be logged in as an administrator for the machine you are using.

To Start XQuery Editor and Generator

On a machine that has MicroStrategy Developer installed, choose Start > Programs > MicroStrategy Tools > XQuery Editor and Generator. XEG opens.

XEG must be Run As Administrator on Windows. In the Start menu, right-click XQuery Editor and Generator, and select Run as administrator. XEG opens.

To Load an Existing XEG Scenario

In XEG, choose File > Load.
Browse to the saved scenario file, select it, and click OK.
The scenario is loaded and each source file is opened in a Tree View window.

Creating a New XQuery Scenario with XEG

XEG can generate XQuery statements to query REST and SOAP web services, as well as XML data sources. Refer to the following sections for steps to create a new XQuery statement for each type of data source:

To Create a New XQuery XML Scenario
To Create a New XQuery REST Scenario
To Create a New XQuery SOAP Scenario

To Create a New XQuery XML Scenario

In XEG, choose File > New. The New Simple XQuery dialog box opens.
From the Source Type drop-down list select XML. The XML Definition page opens.
Click ... (the Browse button) next to the File Name / UNC field. Browse to the XML file that you want to use as a data source and click OK. The XML file is listed in the File Name / UNC field.
To use an XML schema file, click ... (the Browse button) next to the XML Schema field. Browse to the XSD file that you want to use as an XML schema and click OK. The XSD file is listed in the XML Schema field.
Click Finish. The New Simple XQuery dialog box closes. An empty grid is displayed in the main pane, and a Tree View window opens containing the specified XML or XSD file.
For steps to use XEG to generate an XQuery statement using this data source, see Generating an XQuery Statement Using XEG.

To Create a New XQuery REST Scenario

In XEG, choose File > New. The New Simple XQuery dialog box opens.
From the Source Type drop-down list select REST. The HTTP URL And Method page opens.
In the URL field, enter or paste the URL of the REST web service that you are using as a data source.
To add additional query parameters to the URL:

From the list, specify whether to use a GET or POST request. (PUT and DELETE requests are not supported.)
In the field, type the parameters.

Click Next. The HTTP Authentication page opens.
From the Method drop-down list, select the type of authentication you are using to access the REST web service.
If you selected an authentication method other than Anonymous, in the Domain, User Name, and Password fields, enter the login information you are using to access the REST web service.
Click Next. The XML Definition Files page opens.
Select one:

To use an XML file as the definition file, click ... (the Browse button) next to the Sample XML field. Browse to the XML file that you want to use as a sample and click OK. The XML file is listed in the Sample XML field.
OR, to use an XML schema file as the definition file, click ... (the Browse button) next to the XML Schema field. Browse to the XSD file that you want to use as an XML schema and click OK. The XSD file is listed in the XML Schema field.

Click Finish. The New Simple XQuery dialog box closes. An empty grid is displayed in the main pane, and a Tree View window opens containing the specified XML or XSD file.
For steps to use XEG to generate an XQuery statement using this data source, see Generating an XQuery Statement Using XEG.

To Create a New XQuery SOAP Scenario

In XEG, choose File > New. The New Simple XQuery dialog box opens.
From the Source Type drop-down list select SOAP. The WSDL And Service Options page opens.
Click ... (the Browse button) next to the WSDL File field. Browse to the WSDL file that you want to use as a sample data source and click OK. The WSDL file is listed in the WSDL File field.
From the WSDL Operations list, select the WSDL operation to perform on the data source.
Click Next. The Request Definition page opens.
If the Sample Request field does not contain a sample SOAP request based on the WSDL operation you selected, click Generate Request to generate a sample request. Alternately, click ... (the Browse button) to load a saved sample request from a file.
Review the sample request and edit it as necessary.
The fields in the Service Detail area are pre populated based on the specified WSDL file and operation. Review these fields and edit them if necessary.
Click Next. The Request Security page opens.
From the Method drop-down list, select the type of authentication you are using to access the SOAP web service.
If you selected an authentication method other than Anonymous, in the Domain, User Name, Password, and WS Security fields, enter the login information you are using to access the SOAP web service.
If you are using data from a Salesforce or Macro SOAP web service, select the appropriate check box.
Click Next. The Response Definition page opens.
From the SOAP Version list, specify which version of the SOAP protocol the specified SOAP web service uses.
If the Sample Response Text field does not contain a sample SOAP response based on the sample request you specified, click Generate Response to generate a sample request. Alternately, click ... (the Browse button) to load a saved sample request from a file, or click Capture Response to capture a response from the specified SOAP web service.
Click Save. A Save dialog box opens.
Browse to the location where you want to save the file, enter the file name, and click OK. The file is saved.
Click Next. The Other Information page opens.
To use explicit naming in the SOAP XQuery statement, select the Use Explicit Naming check box.
MicroStrategy does not recommend using explicit naming in XQuery statements that are designed for use in MicroStrategy's Freeform SQL Editor.
Click Finish. The New Simple XQuery dialog box closes. An empty grid is displayed in the main pane. A Tree View window opens for each of the source files.
For steps to use XEG to generate an XQuery statement using this data source, see Generating an XQuery Statement Using XEG.

Generating an XQuery Statement Using XEG

Once you have loaded the source files into XEG, you can begin creating the structure to generate an XQuery statement.

The Tree View windows contain a tree view of the source files for the XQuery statement. To expand the elements of a source file out to a given level, in the Tree View window, from the Expand Tree Levels drop-down list (next to the file name), select that level.

For example, to expand all elements of the source file out to the second level, select 2. To completely collapse the source file, select 0. To expand or collapse individual elements, click the triangle next to each element's name.

To add an element to the XQuery statement, click and drag the element from the Tree View window to an empty row in the grid. When you add an element to the grid, all its child elements are automatically added as well.
To edit an element, double-click the value you want to edit. Depending on the value, either type the new value in the field, or select the new value from the drop-down list that opens.
To delete an element, select the element and press Delete. The row containing the element is deleted.

You can save the structure you have created as an XEG scenario. To save an XEG scenario, choose File > Save. Specify the file name, browse to the location you want to save the scenario, and click OK. The scenario is saved.

To change any of the options you specified in the New Simple XQuery dialog box, choose File > Edit. The Edit Simple XQuery dialog box opens. This dialog box has the same pages as the New Simple XQuery dialog box. Step through the Edit Simple XQuery dialog box and make any changes necessary. Click Finish on the last page of the dialog box. Your changes are applied to the XQuery scenario in progress.

Once you have added and edited all the elements you need to the XQuery grid, you can generate the XQuery statement. Choose File > Generate. The XQuery Generation window opens, with the generated XQuery statement.

To test the generated XQuery statement, choose File > Execute. The XQuery statement is executed and the results are displayed in the XQuery Execution window.

To use the generated XQuery statement in a Freeform SQL report, copy it to the clipboard and paste it into the Freeform SQL editor. For steps to create a Freeform SQL report, see Creating a Report to Analyze Web Service Data.