MicroStrategy ONE

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 do not 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, a strong knowledge 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 detailed instructions on using XEG, see the Advanced Reporting Help.

For the purposes of creating a report in MicroStrategy to report on data retrieved from a web service, an XQuery statement can be separated into two logical parts, as described below and shown in the following image:

  1. Connecting to a web service and retrieving 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 are dependent on the web service you access and what data 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-xq = "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:

    • MicroStrategy REST functions: Use the URL http://www.microstrategy.com/xquery/rest-functions.

    • MicroStrategy SOAP functions: Use the URL http://www.microstrategy.com/xquery/soap-functions.

    • You can also use REST or SOAP functions provided by other third-party vendors such as Zorba. However, MicroStrategy only actively tests and supports the MicroStrategy functions listed above.

    • Defining the web service: In the example XQuery statement shown above, the line let $uri := ('http:// www.webserviceX.NET/uszip.asmx/GetInfoByState?USState=VA') defines the URL required to access the web service. Refer to your third-party web service's documentation to determine an accurate URL.

    • Requesting data from the web service: In the example XQuery statement shown above, the line let $uri := ('http:// www.webserviceX.NET/uszip.asmx/GetInfoByState?USState=VA') also defines the request for data from the web service. The request for data is included as the GetInfoByState?USState=VA part of the URL. For this web service, this requests data on the state of Virginia. However, not all web service requests can include the request for data as part of the URL. Refer to your third-party web service's documentation to determine the required syntax for the request of data.

    • Implementing security: In the example XQuery statement shown above, no login information is required to access the web service. However, a web service can require various types of login information and security protocols to access the data. For information on implementing security in an XQuery statement and how this can change the syntax for the XQuery request, see Implementing security in an XQuery statement.

  2. Returning data from a web service so 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 is dependent 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.