XML variables substitution in a collection

[msimkin]

I am trying to figure out how I can use the .NET XML configuration variable substitution feature to update a collection of elements. The collection is below.

<dataSources>
	<dataSource alias="Env1" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Host1.contoso.com)(PORT=Port1))(CONNECT_DATA=(FAILOVER_MODE=(TYPE=select)(METHOD=basic))(SERVER=dedicated)(SERVICE_NAME=Service1)(SID = prmp)))" />
	<dataSource alias="Env2" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Host2.contoso.com)(PORT=Port2))(CONNECT_DATA=(FAILOVER_MODE=(TYPE=select)(METHOD=basic))(SERVER=dedicated)(SERVICE_NAME=Service2)(SID = PRMQ)))" />
	<dataSource alias="Env3" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Host3.contoso.com)(PORT=Port3))(CONNECT_DATA=(FAILOVER_MODE=(TYPE=select)(METHOD=basic))(SERVER=dedicated)(SERVICE_NAME=Service3)(SID = prmp)))" />
	<dataSource alias="Env4" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Host4.contoso.com)(PORT=Port4))(CONNECT_DATA=(SERVICE_NAME=ORCL))) " />
</dataSources>

In this collection I would need to replace the Hostx.contoso.com, Portx, and Servicex variables with the correct values for the environment.

My first thought was to create the template string as:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=#{Host})(PORT=#{Port))(CONNECT_DATA=(FAILOVER_MODE=(TYPE=select)(METHOD=basic))(SERVER=dedicated)(SERVICE_NAME=#{Service})(SID = prmp)))

but I would need to have 4 instances of this template, with each using a different set of variable names: #{Host1}, #{Port1}, etc.

Is there anyway to set this up to use collections? For example I would associated the #{Host} variable in the QA environment to be

db1.contoso.com; db2.contoso.com; db3.contoso.com; db4.contoso.com

Relying on the substitution process to keep track of the current collection item being processed.

If possible, I do not want to rely on the value of the alias attribute, as that needs to be replaced also.

I am trying to minimize the number of variables that need to be used, the number of times the same text is typed in order to minimize the chance of typos.

Thanks

-marc

[ryanrousseau]

Hey Marc,

Thanks for reaching out!

I’ve got a few ideas on how you could accomplish this.

One would be to use Configuration Transforms. These are traditionally used in .NET deployments, but they will work with regular XML files as well.

So you could have a transform file for each environment that would replace the dataSources element with the appropriate values.

Another option would be to use variable collections and iteration.

You could create entries for your different aliases that are scoped to the appropriate environments, iterate over the aliases, and use the values to construct the dataSource nodes.

I hope those suggestions help! Let me know if you have any follow-up questions.

Best,
Ryan