BizTalk Adapter for Cosmos DB

Build 21.0.7930

Query Mapping (Sql API)

The adapter maps SQL queries into the corresponding Cosmos DB SQL API queries. A detailed description of all the transformations is out of scope, but we will describe some of the common elements that are used. The adapter takes advantage of SQL API features such as the aggregation framework to compute the desired results.

SELECT Queries

Since all requests can be submitted to a specific collection, we can send any constant string as table name to the API. Following the Azure Portal standard we are using the "C" character as table name.

SQL QuerySql API Query

SELECT id, name FROM Users

SELECT C.id, C.name FROM C

SELECT * FROM Users WHERE name = 'A'

SELECT * FROM C WHERE C.name = 'A'

SELECT * FROM Users WHERE name = 'A' OR email = 'zoe55@gmail.com'

SELECT * FROM C WHERE C.name = 'A' OR C.email = 'zoe55@gmail.com'

SELECT id, grantamt FROM WorldBank WHERE grantamt IN (4500000, 85400000) OR grantamt = 16200000

SELECT C.id, C.grantamt FROM C WHERE C.grantamt IN (4500000, 85400000) OR C.grantamt = 16200000

SELECT * FROM WorldBank WHERE CountryCode = 'A' ORDER BY TotalCommAmt ASC

SELECT * FROM C WHERE C.countrycode = 'AL' ORDER BY C.totalcommamt ASC

SELECT * FROM WorldBank WHERE CountryCode = 'A' ORDER BY TotalCommAmt DESC

SELECT * FROM C WHERE C.countrycode = 'AL' ORDER BY C.totalcommamt DESC

Aggregate Queries

The adapter makes extensive use of this for various aggregate queries. See some examples below:

SQL QuerySql API Query

SELECT COUNT(grantamt) AS COUNT_GRAMT FROM WorldBank

SELECT COUNT(C.grantamt) AS COUNT_GRAMT FROM C

SELECT SUM(grantamt) AS SUM_GRAMT FROM WorldBank

SELECT SUM(C.grantamt) AS SUM_GRAMT FROM C

Built-In functions

SQL QuerySql API Query

SELECT IS_NUMBER(grantamt) AS ISN_ATTR, IS_NUMBER(id) AS ISN_ID FROM WorldBank

SELECT IS_NUMBER(C.grantamt) AS ISN_ATTR, IS_NUMBER(C.id) AS ISN_ID FROM C

SELECT POWER(totalamt, 2) AS POWERS_A, LENGTH(id) AS LENGTH_ID, PI() AS ThePI FROM WorldBank

SELECT POWER(C.totalamt, 2) AS POWERS_A, LENGTH(C.id) AS LENGTH_ID, PI() AS ThePI FROM C

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7930