Article Series

This article is part of a series of articles, you can find the other parts here: 

1  Introduction

Within Logic Apps there exists an Integration Account, part of the Logic Apps Enterprise Integration Pack (EIP), that can store XSLT maps which are used by Logic App workflows to perform message translation. In some cases, these XSLT maps will have been migrated from BizTalk Server and generated by the BizTalk mapper. The BizTalk mapper design tool, however, does not simply provide message translation, it also allows for the implementation of other integration patterns like message aggregator, message splitter, and one called content enricher. This latter pattern, described in detail here, is as follows:

The BizTalk Mapper implements such pattern by allowing connections to SQL Server databases (actually any OleDb Provider) using what is known as Database Functoids. These Database Functoids are used to lookup table row values, for example, looking up a phone number from a person’s ID: 

Migrating these types of BizTalk maps to Logic Apps have proved to be more complex, but it is certainly achievable with the introduction of Integration Account assemblies (see the example here). But what if, because of lack of support in Logic Apps, we want to move away from Database Functoids, or because we’re not coming from a BizTalk migration, or even because the Database Functoids are limited in their functionality (e.g. they can’t call stored procedures), can such a pattern still be implemented in an XSLT map within Logic Apps and, if so, to what degree; for Azure SQL Server it should be possible (at least using an OleDb provider), but what about for other data providers and other cloud databases?

The purpose of this article is to examine how this message enrichment pattern can be implemented with a range of cloud databases, which can be categorized as: Microsoft databases, non-Microsoft databases hosted in Azure, and databases hosted outside Azure. Each solution will make use of an msxsl:script script block, rather than some external assembly, in order to determine the minimal coding requirements to connect to and query a given database.  One thing to note is that this pattern of content enrichment is read-only, that is, the database table rows are not modified.

This series assumes that the reader has a good knowledge of XSLT, C#, and the Azure Logic Apps Integration Account, in particular, XSLT maps and custom assemblies. The example message and schemas used in all the examples in the series are provided in Appendix A.

 


2  Understanding the Logic App Transform XML Action

Before proceeding we really need to know more about the environment that hosts our maps. Where an XSLT map calls an external assembly, the assembly must be uploaded to the Integration Account before the XSLT map itself can be uploaded. The reason for this is because when the XSLT file is uploaded it is compiled (the XSL into one assembly, the inline C# code into another), and hence all the references must be resolved before the compilation can succeed. This compilation process is the reason why, if you attempt to run a Logic App that uses the XSLT map immediately after uploading, you may receive the error: “MapNotReady. The map 'xxxxxxxxxxx' is still being processed. Please try again later.” Behind the scenes, the transformation actions are hosted in a separate Azure Web Apps environment (https://xslt-*****.azurewebsites.net where ***** is your 32 character Integration Account ID, found in the Callback URL property e.g. https://prod-22.northcentralus.logic.azure.com:443/integrationAccounts/*****? ...), and this is where our XSLT maps run.

At this point it would be useful to know what exactly is installed in this ‘map’ environment. Since we know we can connect to Azure SQL Server using an OleDB provider, one thing we can do is write an XSLT map with a script that enumerates the OleDb providers. This can be extended to include the ODBC Drivers too. We can also iterate through the assemblies in the current app domain (i.e. AppDomain.CurrentDomain.GetAssemblies()). Taking this idea further, if we download, sign, and build the GACManager application found on GitHub, and then upload just the GACManagerAPI DLL to the Logic App Integration Account Assemblies blade, we can also determine the contents of the GAC within this environment. The final XSLT map would have the following script:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl userCSharp" version="1.0" xmlns:userCSharp="http://schemas.microsoft.com/BizTalk/2003/userCSharp">
<xsl:output omit-xml-declaration="yes" media-type="application/text" method="text" version="1.0" />
<xsl:template match="/">
<xsl:value-of select ="userCSharp:Invoke()" />
</xsl:template>
<msxsl:script language="C#" implements-prefix="userCSharp"
<msxsl:assembly name="GACManagerApi, Version=1.0.0.0, Culture=neutral, PublicKeyToken=xxxxxxxxxxxxxxxx" />
<msxsl:assembly name ="System.Data"/>
<msxsl:using namespace="System.Data.OleDb" />
<msxsl:using namespace="GACManagerApi" />
<msxsl:using namespace="Microsoft.Win32" />
<![CDATA[ 
public string Invoke()
{
string sAssemblies = “GAC Assemblies:” + System.Environment.NewLine;
 
// Create an assembly enumerator.
var assemblyEnumerator = new AssemblyCacheEnumerator();
  
// Get the first assembly.
var assemblyName = assemblyEnumerator.GetNextAssembly();
  
// Start to loop through the assemblies. 
while (assemblyName != null)
{
// The 'assemblyName' object is a COM interface, if we create an
// AssemblyDescription from it, we will have access to more information.
var assemblyDescription = new AssemblyDescription(assemblyName);
sAssemblies += assemblyDescription.DisplayName + System.Environment.NewLine;
  
// Move to the next assembly.
assemblyName = assemblyEnumerator.GetNextAssembly();
}
 
sAssemblies += “AppDomain Assemblies:” + System.Environment.NewLine;
foreach (Assembly a in AppDomain.CurrentDomain.GetAssemblies())
{
sAssemblies += a.GetName().FullName + System.Environment.NewLine;
}
  
string sProviders = “OleDb Providers:” + System.Environment.NewLine;
using (IDataReader dr = OleDbEnumerator.GetRootEnumerator())
{
 while (dr.Read())
{
sProviders += dr.GetString(0) + System.Environment.NewLine;
}
}
 
string[] odbcDriverNames = null;
using (RegistryKey localMachineHive = Registry.LocalMachine)
using (RegistryKey odbcDriversKey = localMachineHive.OpenSubKey(@"SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers"))
{
if (odbcDriversKey != null)
{
odbcDriverNames = odbcDriversKey.GetValueNames();
}
}
String odbcDrivers = “ODBC Drivers:” + System.Environment.NewLine + string.Join(System.Environment.NewLine, odbcDriverNames);
  
return sAssemblies + sProviders + odbcDrivers;
  
}
]]>
</msxsl:script
</xsl:stylesheet>

If the above XSLT map is executed on a Logic App we get a details list of components installed. Within the list returned there are some interesting database assemblies:

System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

MySql.Data, Version=6.5.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d

Microsoft.Azure.Documents.Client, Version=1.13.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35

Microsoft.WindowsAzure.Storage, Version=7.2.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 (also versions 2.1.0.0, 4.3.0.0 & 6.1.0.0)

 

So, in theory, it should at least be possible to connect to SQL Server, Oracle, MySQL, and the CosmosDB Table & Document databases. There are also some familiar OleDb providers and ODBC drivers available:

OleDb Providers ODBC Drivers
SQLOLEDB Microsoft Access Driver (*.mdb)
MSDataShape Microsoft dBase Driver (*.dbf)
SQLNCLI11 Microsoft Excel Driver (*.xls)
ADsDSOObject Microsoft ODBC for Oracle
SQLNCLI10 Microsoft Paradox Driver (*.db )
Windows Search Data Source Microsoft Text Driver (*.txt; *.csv)
MSDASQL SQL Server
Microsoft.Jet.OLEDB.4.0 SQL Server Native Client 10.0
MSDAOSP SQL Server Native Client 11.0
MSDAORA ODBC Driver 11 for SQL Server [also 13 & 17 versions]

 

The next sections will look at how to connect to and query different cloud databases. The example used will be simple just to prove that we can connect to the database and run a query. In most cases, it will be a SQL query to retrieve a phone number for a given person ID. The code will be within the msxsl:script block (we could just as well put it in an assembly), but the three key things to note will be: the assemblies (msxsl:assembly) that must be referenced, the namespaces (msxsl:using) that are required, and any assemblies uploaded to the Integration Account Assemblies’ blade.

 


3  How to Connect to Azure SQL Server

As already shown in another article, we can migrate the BizTalk Database Functoids to Logic Apps and connect to an Azure SQL Server database. By disassembling the Microsoft.BizTalk.BaseFunctoids.dll assembly we can see that the code uses an OleDb provider. So, we can be confident of connecting using this mechanism. But we also have SQLClient and ODBC as other ways of connecting.

Steps

  1. Create an XSLT map for each of the three data providers below (OleDb, SqlClient & ODBC) and upload them to an Integration Account;
  2. Create a new Azure SQL Server resource;
  3. Create a new database, use the Sample (AdventureWorksLT) database. See here for instructions;
  4. Create a new Logic App:
    • a.  Add a Compose Action and copy the Person XML message from Appendix A;
    • b.  Add a Transform XML action. Set the Content value to the previous Compose action output. Set the Map to the value to one of the XSLT maps previously uploaded;
  5. Run the Logic App. The output should appear like the following:

  6. The phone element should be populated with the phone number of customer ID=1 from the [SalesLT].[Customer] table in the database created in step 3;
  7. Repeat the process for the other two providers.

OleDB Provider

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"  exclude-result-prefixes="msxsl s0 ScriptNS0" version="1.0" xmlns:s0="http://AzureBizTalkMapsDemo/Person" xmlns:ns0="http://AzureBizTalkMapsDemo/Contact" xmlns:ScriptNS0="http://schemas.microsoft.com/BizTalk/2003/ScriptNS0">
<xsl:output omit-xml-declaration="yes" method="xml" indent="yes" version="1.0" />
<xsl:template match="/">
<xsl:apply-templates select="/s0:Person" />
</xsl:template>
<xsl:template match="/s0:Person">
<ns0:Contact>
<Title><xsl:value-of select="Title/text()" /></Title>
<Forename><xsl:value-of select="Forename/text()" /></Forename>
<Surname><xsl:value-of select="Surname/text()" /></Surname>
<Phone><xsl:value-of select="ScriptNS0:GetPhoneNo(ID/text())" /></Phone>
</ns0:Contact>
</xsl:template>
<msxsl:script language="C#" implements-prefix="ScriptNS0">
<msxsl:assembly name ="System.Data"/>
<msxsl:using namespace="System.Data.OleDb" />
<![CDATA[
public string GetPhoneNo(int id)
{
string connectionString = "Provider=SQLOLEDB; Data Source=tcp:xxxxxxxxxxxxxxx.database.windows.net,1433;Initial Catalog=xxxxxxx; Encrypt=yes; User Id=admin@xxxxxxxxxxxxxxx;Password=xxxxxxxxxx";
  
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open(); 
StringBuilder sb = new StringBuilder();
sb.Append("SELECT Phone FROM [SalesLT].[Customer] WHERE CustomerID=" + id.ToString());
 String sql = sb.ToString();
  
using (OleDbCommand command = new OleDbCommand(sql, connection))
{
using (OleDbDataReader reader = command.ExecuteReader())
{
reader.Read();
return reader.GetString(0);
}
}
}
]]>
</msxsl:script
</xsl:stylesheet>


SQLClient

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"  exclude-result-prefixes="msxsl s0 ScriptNS0" version="1.0" xmlns:s0="http://AzureBizTalkMapsDemo/Person" xmlns:ns0="http://AzureBizTalkMapsDemo/Contact" xmlns:ScriptNS0="http://schemas.microsoft.com/BizTalk/2003/ScriptNS0">
<xsl:output omit-xml-declaration="yes" method="xml" indent="yes" version="1.0" />
<xsl:template match="/">
<xsl:apply-templates select="/s0:Person" />
</xsl:template>
<xsl:template match="/s0:Person">
<ns0:Contact>
<Title><xsl:value-of select="Title/text()" /></Title>
<Forename><xsl:value-of select="Forename/text()" /></Forename>
<Surname><xsl:value-of select="Surname/text()" /></Surname>
<Phone><xsl:value-of select="ScriptNS0:GetPhoneNo(ID/text())" /></Phone>
</ns0:Contact>
</xsl:template>
<msxsl:script language="C#" implements-prefix="ScriptNS0">
<msxsl:assembly name ="System.Data"/>
<msxsl:using namespace="System.Data.SqlClient" />
<![CDATA[
public string GetPhoneNo(int id)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "xxxxxxxxxx.database.windows.net";
builder.UserID = "xxxxxxxxxxx"; 
 builder.Password = "xxxxxxxxxxxx"; 
builder.InitialCatalog = "xxxxxxxx";
  
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
connection.Open(); 
StringBuilder sb = new StringBuilder();
sb.Append("SELECT Phone FROM [SalesLT].[Customer] WHERE CustomerID=" + id.ToString());
String sql = sb.ToString();
  
using (SqlCommand command = new SqlCommand(sql, connection))
 {
using (SqlDataReader reader = command.ExecuteReader())
{
reader.Read();
return reader.GetString(0);
}
}
}
]]>
</msxsl:script
</xsl:stylesheet>


ODBC Driver

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"  exclude-result-prefixes="msxsl s0 ScriptNS0" version="1.0" xmlns:s0="http://AzureLogicAppsMapsDemo/Person" xmlns:ns0="http://AzureLogicAppsMapsDemo/Contact" xmlns:ScriptNS0="https://portal.azure.com/myschemas/ScriptNS0">
<xsl:output omit-xml-declaration="yes" method="xml" indent="yes" version="1.0" />
<xsl:template match="/">
<xsl:apply-templates select="/s0:Person" />
</xsl:template>
<xsl:template match="/s0:Person">
<ns0:Contact>
<Title><xsl:value-of select="Title/text()" /></Title>
<Forename><xsl:value-of select="Forename/text()" /></Forename>
<Surname><xsl:value-of select="Surname/text()" /></Surname>
<Phone><xsl:value-of select="ScriptNS0:GetPhoneNo(ID/text())" /></Phone>
</ns0:Contact>
</xsl:template>
<msxsl:script language="C#" implements-prefix="ScriptNS0">
<msxsl:assembly name ="System.Data"/>
<msxsl:using namespace="System.Data.Odbc" />
<![CDATA[
public string GetPhoneNo(int id)
{
string connectionString = "Driver={ODBC Driver 17 for SQL Server};Server=tcp:xxxxxxxxxx.database.windows.net,1433;Database=xxxxxxxx;Uid=admin@xxxxxxxxxxxx;Pwd=xxxxxxxxxx;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;";
 
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
connection.Open(); 
StringBuilder sb = new StringBuilder();
sb.Append("SELECT Phone FROM [SalesLT].[Customer] WHERE CustomerID=" + id.ToString());
String sql = sb.ToString();
  
using (OdbcCommand command = new OdbcCommand(sql, connection))
{
using (OdbcDataReader reader = command.ExecuteReader())
{
reader.Read();
return reader.GetString(0);
}
}
}
]]>
</msxsl:script
</xsl:stylesheet>


Points to Note

As expected, we can connect to Azure SQL Server using the SQL Server OleDb Provider without issue. We only need add a reference to the System.Data assembly and a reference to the System.Data.OleDb namespace. Similarly, we can do the same for SQLClient and ODBC Driver. The OleDb connection also works for the other SQL Server OleDB providers given in the table in section 2, and so does the ODBC connection.

Generally the SqlClient data provider gives the best performance of the three data providers, but also see .NET Framework Data Providers for details on the advantages and disadvantages of each data provider.

 


4  How to Connect to Cosmos DB Table

As mentioned in section 2, the Microsoft.WindowsAzure.Storage assembly exists in the GAC. Therefore, we can simply add a reference to the assembly and namespaces that we need to use.

Steps

  1. Create a new CosmosDB Table database in Azure. See here for instructions;
  2. Follow the C# example here to insert data (CustomerEntity objects) into the database. Ensure you create one customer with the same customer details (firstName, lastName) as the Person XML message from Appendix A;
  3. Allow access from all networks (Home > yourCosmosDBTable – Firewall and virtual networks);
  4. Create the XSLT TableClient map given below. Update the connectionString variable from the PRIMARY CONNECTION STRING (Home > yourCosmosDBTable – Connection String) provided in the portal. Upload the XSLT map to the Integration Account;
  5. Create a new, or edit an existing, Logic App:
    • a.  Add a Compose Action and copy the Person XML message from Appendix A;
    • b.  Add a Transform XML action. Set the Content value to the previous Compose action output. Set the Map to the value to one of the XSLT maps previously uploaded;
  6. Run the Logic App. The output should appear like the following:

  7. The phone element should be populated with the phone number of “Ben Smith” from the example provided in step 2;

TableClient

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"  exclude-result-prefixes="msxsl s0 ScriptNS0" version="1.0" xmlns:s0="http://AzureLogicAppsMapsDemo/Person" xmlns:ns0="http://AzureLogicAppsMapsDemo/Contact" xmlns:ScriptNS0="https://portal.azure.com/myschemas/ScriptNS0">
<xsl:output omit-xml-declaration="yes" method="xml" indent="yes" version="1.0" />
<xsl:template match="/">
<xsl:apply-templates select="/s0:Person" />
</xsl:template>
<xsl:template match="/s0:Person">
<ns0:Contact>
<Title><xsl:value-of select="Title/text()" /></Title>
<Forename><xsl:value-of select="Forename/text()" /></Forename>
<Surname><xsl:value-of select="Surname/text()" /></Surname>
<Phone><xsl:value-of select="ScriptNS0:GetPhoneNumber(Forename/text(), Surname/text())" /></Phone>
</ns0:Contact>
</xsl:template>
<msxsl:script language="C#" implements-prefix="ScriptNS0">
<msxsl:assembly name ="Microsoft.WindowsAzure.Storage, Version=7.2.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
<msxsl:using namespace="Microsoft.WindowsAzure.Storage" />
<msxsl:using namespace="Microsoft.WindowsAzure.Storage.Table" />
<![CDATA[
public string GetPhoneNumber(string firstName, string lastName)
{
var connectionString = "DefaultEndpointsProtocol=https;AccountName=xxxxxxxx;AccountKey=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx==;TableEndpoint=https://xxxxxxxxxxxxx.table.cosmosdb.azure.com:443/;";
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionString);
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
  
// Retrieve a reference to the table.
CloudTable table = tableClient.GetTableReference("people");
  
// Create a retrieve operation that takes a customer entity.
TableOperation retrieveOperation = TableOperation.Retrieve<CustomerEntity>(lastName, firstName);
  
// Execute the retrieve operation.
TableResult retrievedResult = table.Execute(retrieveOperation);
  
// Print the phone number of the result.
if (retrievedResult.Result != null)
{
return ((CustomerEntity)retrievedResult.Result).PhoneNumber;
}
else
{
return string.Empty;
}
}
public class CustomerEntity : TableEntity
{
public CustomerEntity(string lastName, string firstName)
 {
this.PartitionKey = lastName;
this.RowKey = firstName;
}
public CustomerEntity() { }
public string Email { get; set; }
public string PhoneNumber { get; set; }
}
]]>
</msxsl:script
</xsl:stylesheet>

Points to Note

As largely expected we can connect and query the Cosmos DB Table API without issue.  The client assembly version available is 7.2.1.0.

For more information see Azure Storage APIs for .NET.

 


5  How to Connect to Cosmos DB Core (SQL Document)

As mentioned in section 2, the Microsoft.Azure.Documents.Client assembly exists in the GAC. We can add a reference to this assembly and namespaces, but there are a few other assemblies and namespaces that need to be referenced.

Steps

  1. Create a new CosmosDB Core (SQL) Table database in Azure. See here for instructions;
  2. Follow the C# example here and create a couple of family documents in the database;
  3. Allow access from all networks (Home > yourCosmosDBCoreSQL – Firewall and virtual networks);
  4. Create the XSLT TableClient map given below. Update the connectionString variable from the PRIMARY CONNECTION STRING (Home > yourCosmosDBTable – Keys) provided in the portal. Upload the XSLT map to the Integration Account;
  5. Create a new, or edit an existing, Logic App:
    • a.  Add a Compose Action and copy the Person XML message from Appendix A and amend the name to ‘Thomas Andersen’;
    • b.  Add a Transform XML action. Set the Content value to the previous Compose action output. Set the Map to the value to one of the XSLT maps previously uploaded;
  6. Run the Logic App. The output should appear like the following:

  7. The children element should be populated with the children of “Thomas Andersen” from the example provided in step 2;

DocumentClient

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"  exclude-result-prefixes="msxsl s0 ScriptNS0" version="1.0" xmlns:s0="http://AzureLogicAppsMapsDemo/Person" xmlns:ns0="http://AzureLogicAppsMapsDemo/Contact" xmlns:ScriptNS0="https://portal.azure.com/myschemas/ScriptNS0">
<xsl:output omit-xml-declaration="yes" method="xml" indent="yes" version="1.0" />
<xsl:template match="/">
<xsl:apply-templates select="/s0:Person" />
</xsl:template>
<xsl:template match="/s0:Person">
<ns0:Contact>
<Title><xsl:value-of select="Title/text()" /></Title>
<Forename><xsl:value-of select="Forename/text()" /></Forename>
<Surname><xsl:value-of select="Surname/text()" /></Surname>
<Children><xsl:value-of select="ScriptNS0:GetChildren(Surname/text())" /></Children>
</ns0:Contact>
</xsl:template>
<msxsl:script language="C#" implements-prefix="ScriptNS0">
<msxsl:assembly name ="System.Core"/>
<msxsl:assembly name ="System.Data"/>
<msxsl:assembly name ="System.Linq"/>
<msxsl:assembly name ="Microsoft.Azure.Documents.Client, Version=1.13.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
<msxsl:assembly name ="Newtonsoft.Json"/>
<msxsl:using namespace="System.Linq" />
<msxsl:using namespace="Microsoft.Azure.Documents" />
<msxsl:using namespace="Microsoft.Azure.Documents.Client" />
<msxsl:using namespace="Newtonsoft.Json" />
<msxsl:using namespace="Newtonsoft.Json.Linq" />
<![CDATA[
public string GetChildren(string surname)
{
string EndpointUrl = @"https://xxxxxxxxxxxx.documents.azure.com:443/";
string PrimaryKey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx==";
  
DocumentClient client = new DocumentClient(new Uri(EndpointUrl), PrimaryKey); 
FeedOptions queryOptions = new FeedOptions { MaxItemCount = -1 };
IQueryable<object> familyQueryInSql = client.CreateDocumentQuery<object>(UriFactory.CreateDocumentCollectionUri("FamilyDB", "FamilyCollection"),
"SELECT * FROM Family WHERE Family.LastName = '" + surname + "'", queryOptions);
  
string children = string.Empty;
foreach (object family in familyQueryInSql)
{
JObject jo = JObject.Parse(JsonConvert.SerializeObject(family));
children += jo.SelectToken(".Children.[0].FirstName").Value<string>();
}
  
return children;
}
]]>
</msxsl:script
</xsl:stylesheet>

Points to Note

Again, as expected, we can connect any query with issue. One thing to note is that a reference to System.Core must be added. The client assembly version available is 1.13.0.0.


6  Summary

In this article, we’ve seen how to explore the ‘map’ environment and determine what components are installed. By understanding both the environment and the client requirements to connect to the cloud databases, we’ve been able to connect to, and query, three databases. In the next part, we’ll look at connecting to Azure databases where there is no database client installed and how we can manipulate the Integration Account Assemblies’ blade to provide the functionality required.

 


>>Part 2


7  Appendix A

Sample XML message and schemas for use in the examples throughout the series.  

Person.xml

<ns0:Person xmlns:ns0="http://AzureLogicAppsMapsDemo/Person">
<Title>Mr.</Title>
<Forename>thomas</Forename>
<Surname>Smith</Surname>
<ID>1</ID>
</ns0:Person>

 


Person.xsd

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://http://AzureLogicAppsMapsDemo/Person" targetNamespace="http://http://AzureLogicAppsMapsDemo/Person" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Person">
<xs:complexType>
<xs:sequence>
<xs:element name="Title" type="xs:string" />
<xs:element name="Forename" type="xs:string" />
<xs:element name="Surname" type="xs:string" />
 <xs:element name="ID" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

 


Contact.xsd

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://http://AzureLogicAppsMapsDemo/Contact" targetNamespace="http://http://AzureLogicAppsMapsDemo/Contact" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Contact">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="1" name="Title" type="xs:string" />
<xs:element minOccurs="0" maxOccurs="1" name="Forename" type="xs:string" />
<xs:element minOccurs="0" maxOccurs="1" name="Surname" type="xs:string" />
<xs:element minOccurs="0" maxOccurs="1" name="Address" type="xs:string" />
<xs:element minOccurs="0" maxOccurs="1" name="Phone" type="xs:string" />
<xs:element minOccurs="0" maxOccurs="1" name="Notes" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>