From BizTalk Maps to Azure Functions: Dynamic Code-Table Mapping

When migrating integration solutions from BizTalk Server to Azure, one of the recurring challenges is replacing the small but essential pieces of middleware functionality that many legacy integrations depend on. One of those features is code-table mapping, also known as transcoding or value mapping.

Typical examples include translating country codes, status values, transport identifiers, or customer classifications between systems. An incoming message may contain a value such as BE, while the target system expects BEL. Another system might send a transport type of EXP, which needs to be mapped to ExpressDelivery before it can be processed downstream.

These kinds of transformations are usually small, but they are critical for keeping integrations reliable and maintainable. They are commonly used to avoid large and hard-to-maintain IF/ELSE or SWITCH logic inside integration flows. In many cases, the mappings change frequently over time, making it preferable to manage them as configuration or reference data rather than embedding them directly into code.

In BizTalk Server, this functionality was often implemented in several different ways. Many solutions used the visual mapper together with the Database Lookup Functoid to translate incoming values into another system’s representation. Others implemented the logic using custom pipeline components, helper assemblies, or directly inside orchestrations.

So how do we solve this in Azure, and more specifically in Azure Logic Apps? In one of my migration projects, I built a generic Azure Function for dynamic XML value mapping. The function is triggered by a JSON configuration that references an XML document stored in Azure Blob Storage. The configuration defines a set of mapping rules, each consisting of XPath expressions, namespace declarations and SQL-based lookup queries. The function retrieves the XML, evaluates each rule within its defined scope, executes the corresponding SQL query and applies the resulting values back into the document.

In this post, I’ll walk through some of the implementation details and give you some inspiration on how you could solve this in your own migration project.

Why an Azure Function?

At first glance, you might argue that this scenario could also be implemented in an Azure Logic App by combining the Azure SQL Connector with inline XML manipulation, for example using a JavaScript action or expressions.

While that sounds reasonable in theory, it quickly becomes less straightforward in practice.

BizTalk Server was built around XML as its native data model, and its tooling reflects that. Logic Apps, on the other hand, are primarily JSON-centric. As a result, some of the XML-first capabilities that were natural in BizTalk, such as rich XPath navigation and transformation, are either missing or more limited in Logic Apps.

This makes complex XML manipulation and dynamic value replacement harder to implement and maintain, especially when combined with external lookups.

For this reason, I chose to implement this logic in an Azure Function instead. This provides a reusable, testable, and centrally managed component that can be called from any integration flow, while keeping the Logic App focused on orchestration rather than transformation logic.

Example

The easiest way to start is with an example. Let's have a look at what the input for my Azure Function looks like. Afterwards, we can use the sample to dig into a few important parts of the code.

{

"blob": {

"blobContainer": "orders-inbound",

"fileName": "order_12345.xml"

},

"valueMappings": [

{

"description": "Translate ISO country code to full country name",

"namespaces": {

"ord": "http://schemas.example.com/orders"

},

"scopeXPath": "//ord:Order/ord:ShipTo",

"sql": "SELECT CountryName FROM dbo.CountryTranslations WHERE IsoCode = @CountryCode",

"isStoredProcedure": false,

"isMandatory": true,

"sourceXPaths": {

"CountryCode": "ord:CountryCode"

},

"destinationXPaths": {

"CountryName": "ord:CountryName"

}

}

]

}

Blob

ValueMapping

How it works

The code samples in this post are intentionally simplified to highlight the main concepts and data flow. Production aspects such as detailed error handling, logging, and edge-case XPath behavior are omitted for clarity.

1. Scoping: Iterating over repeating XML elements

The function doesn't just process the XML as a whole, it works on repeating elements defined by scopeXPath. This is what makes it powerful for documents with multiple line items.

var scopeElements = xmlDoc.XPathSelectElements(xmlValueMapping.ScopeXPath, nsManager);

foreach (var scopeElement in scopeElements){ // Each scope element is processed independently var parameters = ExtractSqlParameters(..., scopeElement, ...); var results = await ExecuteSqlAsync(...); // Write results back into this specific scope element}

Why this matters: If your XML has 50 order lines, and the scopeXPath points to each line item, the function will execute the lookup 50 times, once per line, each time extracting different input values and writing back different results.

This has performance implications, especially when scope sizes grow. We’ll look at caching strategies later in the post to mitigate repeated lookups.

Example: Given this XML and scopeXPath = "//Order/Line":

<Order> <Line><CountryCode>BE</CountryCode><CountryName/></Line> <Line><CountryCode>FR</CountryCode><CountryName/></Line></Order>

The function processes each <Line> independently, looking up BE and FR separately.

2. Extracting SQL parameters from the XML

The sourceXPaths dictionary maps SQL parameter names to XPath expressions that are evaluated relative to the current scope element:

private Dictionary<string, string> ExtractSqlParameters( Dictionary<string, string> sourceXPaths, XElement scopeElement, XmlNamespaceManager nsManager, ...){ var parameters = new Dictionary<string, string>();

foreach (var param in sourceXPaths) { var evalResult = scopeElement.XPathEvaluate(param.Value, nsManager); var value = ExtractValueFromXPathResult(evalResult); parameters.Add(param.Key, value); }

return parameters;}

Example: With this request configuration:

"sourceXPaths": { "@CountryCode": "CountryCode", "@Language": "string('EN')"}

For the first <Line> scope element, this produces:

• @CountryCode = "BE" (extracted from the XML)
• @Language = "EN" (fixed literal value using XPath's string() function)

3. SQL execution with in-memory caching

Every unique query + parameter combination is cached for 5 minutes. This avoids redundant database calls when multiple scope elements produce the same lookup:

private static readonly MemoryCache _sqlCache = new(new MemoryCacheOptions());

private async Task<Dictionary<string, object>> ExecuteSqlAsync( string sql, bool isStoredProcedure, Dictionary<string, string> parameters, bool isMandatory, ...){ var cacheKey = BuildCacheKey(sql, isStoredProcedure, parameters);

if (_sqlCache.TryGetValue(cacheKey, out Dictionary<string, object>? cachedResult)) return cachedResult!;

// ... execute query ...

var results = new Dictionary<string, object>(); while (await reader.ReadAsync(cancellationToken)) { for (int i = 0; i < reader.FieldCount; i++) results[reader.GetName(i)] = reader.GetValue(i); }

_sqlCache.Set(cacheKey, results, TimeSpan.FromMinutes(5)); return results;}

The cache key is built from the SQL text + a SHA256 hash of the sorted parameters, so CountryCode=BE and CountryCode=FR are correctly treated as different lookups.

This is an in-memory cache scoped to a single request execution, so it does not provide cross-request or cross-instance caching. A natural next step would be introducing a distributed cache (e.g., Redis). But this was not in scope yet at the time of writing.

4. Writing results back into the XML

The destinationXPaths dictionary maps SQL column names to XPath locations where the result should be written. The function handles both elements and attributes:

foreach (var xpath in xmlValueMapping.DestinationXPaths){ var queryResultValue = results[xpath.Key]?.ToString();

if (string.IsNullOrEmpty(xpath.Value)) { // Empty XPath value → set as attribute on the scope element itself scopeElement.SetAttributeValue(xpath.Key, queryResultValue); } else { // Evaluate XPath relative to scope element var destinationNode = scopeElement.XPathEvaluate(xpath.Value, nsManager);

if (destinationNode is XElement element) element.Value = queryResultValue; else if (destinationNode is XAttribute attribute) attribute.Value = queryResultValue; }}

Example configurations:

// Write SQL column "CountryName" into the <CountryName> child element"destinationXPaths": { "CountryName": "CountryName" }

// Write SQL column "RegionId" as an attribute on the scope element itself"destinationXPaths": { "RegionId": "" }

Given the SQL returns CountryName = "Belgium", the XML transforms from:

<Line><CountryCode>BE</CountryCode><CountryName/></Line>

to:

<Line><CountryCode>BE</CountryCode><CountryName>Belgium</CountryName></Line>

5. The isMandatory safety net

This flag controls what happens when a lookup executes successfully but returns no rows:

if (!reader.HasRows){ if (isMandatory) throw new InvalidOperationException("No rows returned from SQL execution.");

// Non-mandatory: skip silently return new Dictionary<string, object>();}

Use isMandatory: true when a missing translation should halt processing (e.g., country code must exist). Use false for optional enrichments where a missing value is acceptable (e.g., a preferred carrier that might not be configured yet).

Response

The function saves the enriched XML as a new timestamped file and returns the new filename:

{ "filename": "order_12345_20260609143022.xml"}

The original file remains untouched, giving you a full audit trail.

Conclusion

Code-table mapping may not be the most glamorous part of an integration platform, but it is one of those small features that quietly keeps enterprise systems working together without endless transformation logic scattered everywhere.

The current implementation is mainly focused on XML, since that was the primary requirement, but the same approach can easily be extended to support JSON structures as well.

The pattern is also not limited to Azure SQL as a lookup source. It could just as easily be expanded to call external APIs or other reference data services during transformation, making it a flexible building block for modern integration scenarios.

Most importantly, it keeps Logic Apps readable and maintainable. Because nobody wants to explain a complex nested IF/ELSE expression to a colleague on a Friday afternoon.

If you made it this far, this post gives you some practical ideas for handling dynamic value mapping in Azure integrations. And if it saves you from creating one more gigantic switch statement, then this Azure Function has already done its job.