The following screenshot
describes the table I’m using for this explanation.
By
including the application name as a column, we can reuse the same table, stored
procedures and the generated schemas by the WCF-SQL adapter. This also
eliminates the possibility of identical parameter names by different
applications. Depending
on the expected records, you can index the table. We put an index on
the ApplicationName together with ParameterName.
For
retrieving the data, we're using the WCF-SQL adapter that calls one of two stored
procedures.
There are 2 stored procedures:
1. usp_GetParameterValueByName:
it takes the application and parameter name as an input value and returns the
value of that parameter
USE [EbtsServices]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetParameterValueByName]
@applicationName
nvarchar(10),
@parameterName
nvarchar(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT ParameterValue
FROM ApplicationParameters
WHERE ApplicationName =
@applicationName
AND ParameterName = @parameterName;
END
2. usp_GetParametersByApplicationName: it takes the application name as an input value and returns all the
parameters associated with the application
USE [EbtsServices]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetParametersByApplicationName]
@applicationName
nvarchar(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT ParameterName , ParameterValue
FROM
ApplicationParameters
WHERE
ApplicationName = @applicationName;
END
Don’t forget to grant the correct BizTalk
groups execute (with grant) permissions on the stored procedures. Using
WCF-SQL to generate the schemas results in the following three schemas:
The first
thing to do is to promote the ApplicationName en ParameterName element in the dbo.xsd
so that we can alter them inside an orchestration.
This is a response
message when using usp_GetParametersByApplication :
As you can
see, the response consists out of - depending on the number of parameters in the
table - multiple StoredProcedureResultSet0
records, which have a
ParameterName and ParameterValue element.
Because
BizTalk doesn’t let you promote elements that can occur multiple times, we had
to use XPath expression to extract the value.
On the
internet there are a lot of blogs that suggest that you count the number of
repeated records and then loop x time to extract the parameter value by using
the index in the XPath. This
approach has some disadvantages:
-
you
need to add three extra shapes to you orchestration (one expression to
determine the count, one loop and one expression insight the loop the extract
the values)
-
It
has an impact on performance because it needs to loop over all the StoredProcedureResultSet0, check if the parameter name is the one you
expect and then extract the value, even if you don’t use them all.
So I searched
for another solution. Just like with an collection it is possible to replace
the index by a key value, in this case the parameter name. First I tried to
figure out the correct xpath by trail and error. Eventually I went looking for
an interesting tool. The lucky winner is Altova’s xml spy.
I opened
the response message and started to experiment until I found the correct xpath
expression:
string(/*[local-name()='usp_GetAllParametersByApplicationNameResponse'
and
namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0'
and
namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0'
and
namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName']
/*[local-name()='ParameterName' and
namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName'
and .=’{ParameterName}’]
/../*[local-name()='ParameterValue' and
namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName'])"))
Now I just replace {ParameterName} with the parameter name (as written in the
database) where I want to get the corresponding parameter value form. And then
I had to place the expression inside string(), to get the value as a
System.String object.
Still this wasn’t good enough for BizTalk, I
got an object of type Microsoft.XLANGs.Core.Part+ArrayBasedXmlNodeList, not
exactly what I needed. The solution is to use System.Convert.ToString.
The full command to retrieve the parameter
value in an orchestration,
parameterValue is of type System.String.
parameterValue =
System.Convert.ToString(xpath(parametersResponse.Body ,
"string(/*[local-name()='usp_GetAllParametersByApplicationNameResponse' and
namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0'
and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0'
and
namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName']
/*[local-name()='ParameterName' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName'
and .= ’{ParameterName}’]
/../*[local-name()='ParameterValue' and
namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName'])"));
When
using usp_GetParameterValueByName the xpath expression is slightly different:
/*[local-name()='usp_GetParameterValueByNameResponse'
and
namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0'
and
namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0'
and
namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/sp_GetParameterValueByName']/*[local-name()='ParameterValue'])"))
It is still
necessary to put the expression inside of string() and do
a conversion to System.String.
author: Martijn
