Jes%u00fas Rodriguez mentions that TwoConnect has
released trial versions of their Salesforce.com and SQL
Service Service Broker adapters for BizTalk on AdapterWorx (which
seems to have gotten a cool new logo, btw). There seems to be a Sonic
MQ adapter in the pipeline, as well. Cool stuff.
Recently I have been working a lot with BizUnit 2.0 (Available on GotDotNet). This framework provides an easy and customizable way to unit test your end to end BizTalk solutions. I’ll not get into details about BizUnit right now since this post is focused on Visual Studios 2005 Code Snippets.
As fate would have it, I’m going to be delving back into MOM 2005. I figure I may as well blog on it, so I’ve added it as another category. I’ll be talking about MOM 2005 within the context of MOM monitoring BizTalk Server 2006. There are of course, many other applications but I’ll start focused and try to be alot of use to a small group of people.
I’m going to be covering the management packs, initial setup, using the agents, configuring and responding to alerts, and also writing scripts for Mom to execute when an alert condition is detected.
I’m also taking requests, so if you have questions about MOM 2005 let me know and I’ll do my best to answer them.
Recently I have been working a lot with BizUnit 2.0 (Available on GotDotNet). This framework provides an easy and customizable way to unit test your end to end BizTalk solutions. I’ll not get into details about BizUnit right now since this post is focused on Visual Studios 2005 Code Snippets.
Young Jun has developed a set of code snippets for BizUnit 2.0 for use with Visual Studios 2005. Although not all the BizUnit tasks are included, it is a huge time saver when building unit test files.
The only problem I had was how to install the snippets. I found it a little tricky so here are the steps I went through.
Installation:
1. Download the code snippets.
2. Unzip them to a folder, in my case C:\BizUnit
3. Open Visual Studios and locate the Code Snippets Manager under Tools.
4. If you do not see it listed, you will need to go to Customize under Tools, look under Commands, Tools, and move the command to the Tools menu.
5. Change the Language to XML
6. Select the Add button and select the folder with your snippet (in my case C:\BizUnit)
7. Click OK.
8. You are all set. Now when you open an Xml document inside Visual Studios you will see the BizTalk Snippets available
More to come on BizUnit in later posts.
AdapterWorx community keeps growing!!!
Today Two Connect decided to make available to the AdapterWorx community the trial versions of the Salesforce.com adapter and Service Broker 1.2 adapters for BizTalk Server 2006. Stay tune because we will have new surprises coming up on the next weeks.
So go ahead download the software, start playing with it and provide feedback.
A staple in BizTalk 2004/2006 is to design and implement mapping/transformation operations using the BizTalk mapper. Mapping in BizTalk can be extended and improved by:
a) Using a rich set of Functoids that ship with BizTalk.
b) Using custom XSLT in a map.
c) Writing Custom Functoids that can be used in a map (link)
d) Writing JScript,C# or VB.Net code in a Scripting Functoid.
As mapping operations become more complex, Custom XSLT must often be used to perform the complete transformation or a portion of the transformation.
Unfortunately many developers do not know XSLT and in some cases do not have time to learn it. But, most developers do have Sql skills and are comfortable writing Sql Statements that join tables to produce a final result set.
This post will discuss a transformation of a source XML message to a destination XML message using a Sql Server 2005 stored procedure. The code for this example can be downloaded at the end of this entry.
A portion of the source XML message for the transformation is as below.
One or many Applicant nodes are submitted with the message.
A brief description of the <Applicant> node for the above message is as below:
1) <Applicant> nodes contain information such as <ApplicantId>,<FirstName>,<LastName>,<Sex>,<BirthCountryCode> etc.
2) <Applicant> nodes contain 0 to many <NickName> Nodes.
3) <Applicant> nodes contain <TestScore> nodes, that include the results of a test taken.
4) <Applicant> nodes contain <RelationInfoToScores> Nodes that contain IDRefs to relate the ApplicantId to the correct TestScores in the message. The red arrows in the above graphic illustrate the relation.
The Destination XML message for the completed transformation is as below. A separate <ConcatenatedApplicantInfo> node is created for each <Applicant> node in the source message. Information for each Applicant is concatenated with a padding of 25 spaces between items.
The below Sql Server 2005 stored procedure will complete the transformation of the source message to the destination message.
USE [TransformationHelper]
GO
/****** Object: StoredProcedure [dbo].[Map_ApplicantHierarchical_To_ApplicantFlat] Script Date: 07/13/2006 17:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Proc [dbo].[Map_ApplicantHierarchical_To_ApplicantFlat]
— Parameter to Accept AllApplicantInfo XML message
— from BizTalk.
@ApplicantXML xml
As
/*
@Applicant Temp table for Applicant Header Info
from AllApplicantInfo info XML message.
*/
DECLARE @Applicant Table(
ApplicantId int null,
FirstName varchar(20) null,
LastName varchar(20) null,
MiddleName varchar(20) null,
Sex char(1) null,
BirthCountryCode int null,
BirthProvinceCode int null,
BirthCountry varchar(20) null,
BirthProvince varchar(20) null);
/*
Statement to shred Applicant Header Info from XML message
into @Applicant Temp table
*/
WITH XMLNAMESPACES (‘http://Applicants’ as ns0)
Insert Into @Applicant
(ApplicantId,
FirstName,
LastName,
MiddleName,
Sex,
BirthCountryCode,
BirthProvinceCode)
Select ShreddedApplicantInfoXML.nCol.value(‘@ApplicantId’,‘int’),
ShreddedApplicantInfoXML.nCol.value((‘(FirstName)[1]’),‘varchar(20)’),
ShreddedApplicantInfoXML.nCol.value((‘(LastName)[1]’),‘varchar(20)’),
ShreddedApplicantInfoXML.nCol.value((‘(MiddleName)[1]’),‘varchar(20)’),
ShreddedApplicantInfoXML.nCol.value((‘(Sex)[1]’),‘char(1)’),
ShreddedApplicantInfoXML.nCol.value((‘(BirthCountryCode)[1]’),‘int’),
ShreddedApplicantInfoXML.nCol.value((‘(BirthProvinceCode)[1]’),‘int’)
From @ApplicantXML.nodes (‘/ns0:AllApplicants/Applicant/Information’) as ShreddedApplicantInfoXML(nCol)
Where ShreddedApplicantInfoXML.nCol.value((‘(FirstName)[1]’),‘varchar(20)’) is not null
/* In below update statement Join to another table to get Country Name:
Join CountryCodes
On Applicant.BirthCountryCode = CountryCodes.Id
Replaces Database functoids.
This increases performance as it cuts out the
Database functoids that would make multiple round trips to
the database
*/
/* Also in below update statement Call Managed Code to get Province name
BirthProvince = dbo.GetProvince(Applicant.BirthCountryCode,Applicant.BirthProvinceCode)
This is like a scripting functoid or custom functoid in a BizTalk map.
*/
Update @Applicant
Set BirthCountry = CountryCodes.Name,
BirthProvince = dbo.GetProvince(Applicant.BirthCountryCode,Applicant.BirthProvinceCode)
From @Applicant as Applicant
Join CountryCodes
On Applicant.BirthCountryCode = CountryCodes.Id
–Below is good for debugging purposes.
–Select * from @Applicant
/*
@NickNames Temp table for Info
from AllApplicantInfo info XML message.
*/
DECLARE @NickNames Table
(ApplicantId int null,
[Name] varchar(20) null) ;
/*
Statement to shred Info from XML message
into @NickNames Temp table
*/
WITH XMLNAMESPACES (‘http://Applicants’ as ns0)
Insert Into @NickNames
Select NickNameXML.nCol.value(‘(../@ApplicantId)[1]’,‘int’),
NickNameXML.nCol.value((‘(Name)[1]’),‘varchar(20)’)
FROM @ApplicantXML.nodes (‘/ns0:AllApplicants/Applicant/Information/NickName’) as NickNameXML(nCol)
–Below is good for debugging purposes.
–Select * from @NickNames
/*
@TestScores Temp table for Info
from AllApplicantInfo info XML message.
*/
Declare @TestScores Table
(TestScoreId char(2) null,
Test varchar(20) null,
Score int null);
/*
Statement to shred Info from XML message
into @TestScores Temp table
*/
WITH XMLNAMESPACES (‘http://Applicants’ as ns0)
Insert Into @TestScores
Select
TestScoresXML.nCol.value(‘(@TestScoreId)’,‘char(2)’),
TestScoresXML.nCol.value((‘(Test)[1]’),‘varchar(20)’),
TestScoresXML.nCol.value((‘(Score)[1]’),‘int’)
FROM @ApplicantXML.nodes (‘/ns0:AllApplicants/Applicant/TestScores’) as TestScoresXML(nCol)
–Below is good for debugging purposes.
–Select * from @TestScores
/*
@Relation Temp table for Info
from AllApplicantInfo info XML message.
*/
Declare @Relation Table
(ApplicantId int null,
TestScoreId char(2) null);
/*
Statement to shred Info from XML message
into @TestScores Temp table
*/
WITH XMLNAMESPACES (‘http://Applicants’ as ns0)
Insert Into @Relation
Select
RelationXML.nCol.value(‘(@ApplicantId)’,‘int’),
RelationXML.nCol.value((‘@TestScoreId’),‘char(2)’)
FROM @ApplicantXML.nodes (‘/ns0:AllApplicants/Applicant/RelateInfoToScores’) as RelationXML(nCol)
–Below is good for debugging purposes.
–Select * from @Relation
Declare @FinalResults Table
(ApplicantID int not null,
ConcatenatedInfo varchar(8000))
Declare @ApplicantId int
Declare @ApplicantInfoText varchar(8000)
Declare @NickNamesText varchar(8000)
Declare @ScoresText varchar(8000)
— Below cursor will do the work of concatenating each Applicants
— info together into one node.
Declare cur_ApplicantRows Cursor Local Fast_Forward For
SELECT ApplicantId
FROM @Applicant
OPEN cur_ApplicantRows
FETCH NEXT FROM cur_ApplicantRows INTO @ApplicantId
While @@Fetch_status = 0
Begin
Set @ApplicantInfoText = ”
Set @NickNamesText = ”
Set @ScoresText = ”
Select @ApplicantInfoText = FirstName + space(25 – len(FirstName)) +
LastName + space(25 – len(LastName)) +
MiddleName + space(25 – len(MiddleName)) +
Sex + space(5 – len(Sex)) +
BirthCountry + space(25 – len(BirthCountry)) +
BirthProvince + space(25 – len(BirthProvince))
From @Applicant
Where ApplicantId = @ApplicantId
Select @NickNamesText = @NickNamesText + [Name] + space(25 – len([Name]))
From @NickNames
Where ApplicantId = @ApplicantId
Select @ScoresText = @ScoresText +
TestScores.Test + space(25 – len(TestScores.Test)) +
rtrim(str(TestScores.Score)) + space(25 – len(rtrim(str(TestScores.Score))))
From @TestScores as TestScores
Join @Relation as Relation
On TestScores.TestScoreId = Relation.TestScoreId
and Relation.ApplicantId = @ApplicantId
–Select @NickNamesText
–Select @ApplicantInfoText
–Select @ScoresText
Insert Into @FinalResults
values (@ApplicantId, @ApplicantInfoText + ‘ ‘ + @NickNamesText + ‘ ‘ + @ScoresText)
FETCH NEXT FROM cur_ApplicantRows INTO @ApplicantId
End
— Below statement will generate the Final Flat Applicant XML Result.
— Return back the final result set
Select 1 as Tag,
0 as Parent,
ApplicantID as [ConcatenatedApplicantInfo!1!ApplicantID],
ConcatenatedInfo as [ConcatenatedApplicantInfo!1!!cdata]
From @FinalResults as ConcatenatedApplicantInfo
FOR XML EXPLICIT
Discussion of Stored Procedure:
1) The AllApplicantInfo source XML Message is passed to the stored procedure via a parameter of type XML.
2) The AllApplicantInfo source XML Message is then shredded into an number of temporary relational tables:
@Applicant
@NickNames
@TestScores
@Relation
3) Sql Statements in the stored procedure are used to join the temp tables to produce the final <ConcatenatedApplicantInfo> node for each <Applicant> node in the source message.
4) The final destination message is returned using a Select statement via an XML Explicit Clause.
5) The Country Code table is directly available and can be joined to in a set operation in the stored procedure. This eliminates using Database Functoids in a BizTalk Map that would invoke multiple round trips to Sql Server.
6) A new feature of Sql Server 2005 is calling managed code. Managed code in Sql Server 2005 allows complex data manipulation that would be difficult to write using straight Transact SQL. In this example, calling managed code from stored procedure replaces the use of functoids in a BizTalk map. The below managed code was called from the stored procedure to determine the Province from a Province code.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetProvince (SqlInt32 country, SqlInt32 province)
{
string result = “”;
switch (country.Value)
{
case 1:
switch (province.Value)
{
case 1:
result = “Newfoundland”;
break;
case 2:
result = “PEI”;
break;
case 3:
result = “New Brunswick”;
break;
case 4:
result = “Nova Scotia”;
break;
case 5:
result = “Quebec”;
break;
case 6:
result = “Ontario”;
break;
case 7:
result = “Manitoba”;
break;
case 8:
result = “Saskatchewan”;
break;
case 9:
result = “Alberta”;
break;
case 10:
result = “British Columbia”;
break;
case 11:
result = “Yukon”;
break;
case 12:
result = “NWT”;
break;
default:
result = “Unknown”;
break;
}
break;
default:
result = “Unknown”;
break;
}
return result;
}
};
As below, BizTalk can invoke the stored procedure via the Sql Adapter passing the source message as a parameter and then receiving the destination message as the response.
Sql 2005 has added many enhancements to aid in XML processing.
a) XQuery expressions
An example is below:
for $i in /InvoiceList/Invoice/Items/Item
return
{$i/@Quantity}
{string($i/@Product)}
b) Xml Auto -> Nesting XML auto clauses
c) XML datatype
d) Indexing xml data
e) A subset of the FLWOR syntax
f) Validating XML against a Schema.
g) more..
If you are interested in some free online virtual labs that explore the new Sql 2005 XML capabilities, please click on the below links:
SQL Server%u2122 2005: XML Capabilities (TechNet) Virtual Lab
And:
Using Xquery with SQL Server 2005 XML Data (TechNet) Virtual Lab
Finally:
The not so good things about this method:
1) You have to call Sql server from BizTalk. When using BizTalk maps, the entire
transformation is localized to the BizTalk host process.
The good things about this method:
1) Simplified debugging of the transformation. For example, in the T-SQL you can place in print statements, select statements that return intermediate result sets, debug a stored procedure in Visual Studio, use the Sql Profiler etc.
2) In this example only a stored procedure needs to be deployed to a Sql Server database.
3) In this example, no data is persisted to permanent Sql Server tables. Only temp tables are utilized in the stored procedure.
Use this method:
1) As an alternative to writing custom XSLT.
2) If you are comfortable writing Sql Server Stored Procedures.
3) To replace database functoids in a map that incur multiple round trips to a database.
4) To perform transformations on larger messages
Some notes:
1) More than one XML message can be passed to a stored procedure.
2) Transformation operations can be split between the BizTalk maps and stored procedures. For example BizTalk maps can carry out a portion of the transformation and then pass the remainder of the transformation to the stored procedures.
3) For Sql 2000, OpenXML statements can be used to shred XML nodes into relational tables.
Conclusion:
Use the new XML enhancements along with the managed code in Sql 2005 to simplify and to improve the performance of complex BizTalk mapping operations.
Download the sample discussed above HERE. Read the ReadMe.Txt before installing and running.
A staple in BizTalk 2004/2006 is to design and implement mapping/transformation operations using the BizTalk mapper. Mapping in BizTalk can be extended and improved by:
a) Using a rich set of Functoids that ship with BizTalk.
b) Using custom XSLT in a map.
c) Writing Custom Functoids that can be used in a map (link)
d) Writing JScript,C# or VB.Net code in a Scripting Functoid.
As mapping operations become more complex, Custom XSLT must often be used to perform the complete transformation or a portion of the transformation.
Unfortunately many developers do not know XSLT and in some cases do not have time to learn it. But, most developers do have Sql skills and are comfortable writing Sql Statements that join tables to produce a final result set.
This post will discuss a transformation of a source XML message to a destination XML message using a Sql Server 2005 stored procedure. The code for this example can be downloaded at the end of this entry.
A portion of the source XML message for the transformation is as below.
One or many Applicant nodes are submitted with the message.
A brief description of the <Applicant> node for the above message is as below:
1) <Applicant> nodes contain information such as <ApplicantId>,<FirstName>,<LastName>,<Sex>,<BirthCountryCode> etc.
2) <Applicant> nodes contain 0 to many <NickName> Nodes.
3) <Applicant> nodes contain <TestScore> nodes, that include the results of a test taken.
4) <Applicant> nodes contain <RelationInfoToScores> Nodes that contain IDRefs to relate the ApplicantId to the correct TestScores in the message. The red arrows in the above graphic illustrate the relation.
The Destination XML message for the completed transformation is as below. A separate <ConcatenatedApplicantInfo> node is created for each <Applicant> node in the source message. Information for each Applicant is concatenated with a padding of 25 spaces between items.
The below Sql Server 2005 stored procedure will complete the transformation of the source message to the destination message.
USE [TransformationHelper]
GO
/****** Object: StoredProcedure [dbo].[Map_ApplicantHierarchical_To_ApplicantFlat] Script Date: 07/13/2006 17:25:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Proc [dbo].[Map_ApplicantHierarchical_To_ApplicantFlat]
— Parameter to Accept AllApplicantInfo XML message
— from BizTalk.
@ApplicantXML xml
As
/*
@Applicant Temp table for Applicant Header Info
from AllApplicantInfo info XML message.
*/
DECLARE @Applicant Table(
ApplicantId int null,
FirstName varchar(20) null,
LastName varchar(20) null,
MiddleName varchar(20) null,
Sex char(1) null,
BirthCountryCode int null,
BirthProvinceCode int null,
BirthCountry varchar(20) null,
BirthProvince varchar(20) null);
/*
Statement to shred Applicant Header Info from XML message
into @Applicant Temp table
*/
WITH XMLNAMESPACES (‘http://Applicants’ as ns0)
Insert Into @Applicant
(ApplicantId,
FirstName,
LastName,
MiddleName,
Sex,
BirthCountryCode,
BirthProvinceCode)
Select ShreddedApplicantInfoXML.nCol.value(‘@ApplicantId’,‘int’),
ShreddedApplicantInfoXML.nCol.value((‘(FirstName)[1]’),‘varchar(20)’),
ShreddedApplicantInfoXML.nCol.value((‘(LastName)[1]’),‘varchar(20)’),
ShreddedApplicantInfoXML.nCol.value((‘(MiddleName)[1]’),‘varchar(20)’),
ShreddedApplicantInfoXML.nCol.value((‘(Sex)[1]’),‘char(1)’),
ShreddedApplicantInfoXML.nCol.value((‘(BirthCountryCode)[1]’),‘int’),
ShreddedApplicantInfoXML.nCol.value((‘(BirthProvinceCode)[1]’),‘int’)
From @ApplicantXML.nodes (‘/ns0:AllApplicants/Applicant/Information’) as ShreddedApplicantInfoXML(nCol)
Where ShreddedApplicantInfoXML.nCol.value((‘(FirstName)[1]’),‘varchar(20)’) is not null
/* In below update statement Join to another table to get Country Name:
Join CountryCodes
On Applicant.BirthCountryCode = CountryCodes.Id
Replaces Database functoids.
This increases performance as it cuts out the
Database functoids that would make multiple round trips to
the database
*/
/* Also in below update statement Call Managed Code to get Province name
BirthProvince = dbo.GetProvince(Applicant.BirthCountryCode,Applicant.BirthProvinceCode)
This is like a scripting functoid or custom functoid in a BizTalk map.
*/
Update @Applicant
Set BirthCountry = CountryCodes.Name,
BirthProvince = dbo.GetProvince(Applicant.BirthCountryCode,Applicant.BirthProvinceCode)
From @Applicant as Applicant
Join CountryCodes
On Applicant.BirthCountryCode = CountryCodes.Id
–Below is good for debugging purposes.
–Select * from @Applicant
/*
@NickNames Temp table for Info
from AllApplicantInfo info XML message.
*/
DECLARE @NickNames Table
(ApplicantId int null,
[Name] varchar(20) null) ;
/*
Statement to shred Info from XML message
into @NickNames Temp table
*/
WITH XMLNAMESPACES (‘http://Applicants’ as ns0)
Insert Into @NickNames
Select NickNameXML.nCol.value(‘(../@ApplicantId)[1]’,‘int’),
NickNameXML.nCol.value((‘(Name)[1]’),‘varchar(20)’)
FROM @ApplicantXML.nodes (‘/ns0:AllApplicants/Applicant/Information/NickName’) as NickNameXML(nCol)
–Below is good for debugging purposes.
–Select * from @NickNames
/*
@TestScores Temp table for Info
from AllApplicantInfo info XML message.
*/
Declare @TestScores Table
(TestScoreId char(2) null,
Test varchar(20) null,
Score int null);
/*
Statement to shred Info from XML message
into @TestScores Temp table
*/
WITH XMLNAMESPACES (‘http://Applicants’ as ns0)
Insert Into @TestScores
Select
TestScoresXML.nCol.value(‘(@TestScoreId)’,‘char(2)’),
TestScoresXML.nCol.value((‘(Test)[1]’),‘varchar(20)’),
TestScoresXML.nCol.value((‘(Score)[1]’),‘int’)
FROM @ApplicantXML.nodes (‘/ns0:AllApplicants/Applicant/TestScores’) as TestScoresXML(nCol)
–Below is good for debugging purposes.
–Select * from @TestScores
/*
@Relation Temp table for Info
from AllApplicantInfo info XML message.
*/
Declare @Relation Table
(ApplicantId int null,
TestScoreId char(2) null);
/*
Statement to shred Info from XML message
into @TestScores Temp table
*/
WITH XMLNAMESPACES (‘http://Applicants’ as ns0)
Insert Into @Relation
Select
RelationXML.nCol.value(‘(@ApplicantId)’,‘int’),
RelationXML.nCol.value((‘@TestScoreId’),‘char(2)’)
FROM @ApplicantXML.nodes (‘/ns0:AllApplicants/Applicant/RelateInfoToScores’) as RelationXML(nCol)
–Below is good for debugging purposes.
–Select * from @Relation
Declare @FinalResults Table
(ApplicantID int not null,
ConcatenatedInfo varchar(8000))
Declare @ApplicantId int
Declare @ApplicantInfoText varchar(8000)
Declare @NickNamesText varchar(8000)
Declare @ScoresText varchar(8000)
— Below cursor will do the work of concatenating each Applicants
— info together into one node.
Declare cur_ApplicantRows Cursor Local Fast_Forward For
SELECT ApplicantId
FROM @Applicant
OPEN cur_ApplicantRows
FETCH NEXT FROM cur_ApplicantRows INTO @ApplicantId
While @@Fetch_status = 0
Begin
Set @ApplicantInfoText = ”
Set @NickNamesText = ”
Set @ScoresText = ”
Select @ApplicantInfoText = FirstName + space(25 – len(FirstName)) +
LastName + space(25 – len(LastName)) +
MiddleName + space(25 – len(MiddleName)) +
Sex + space(5 – len(Sex)) +
BirthCountry + space(25 – len(BirthCountry)) +
BirthProvince + space(25 – len(BirthProvince))
From @Applicant
Where ApplicantId = @ApplicantId
Select @NickNamesText = @NickNamesText + [Name] + space(25 – len([Name]))
From @NickNames
Where ApplicantId = @ApplicantId
Select @ScoresText = @ScoresText +
TestScores.Test + space(25 – len(TestScores.Test)) +
rtrim(str(TestScores.Score)) + space(25 – len(rtrim(str(TestScores.Score))))
From @TestScores as TestScores
Join @Relation as Relation
On TestScores.TestScoreId = Relation.TestScoreId
and Relation.ApplicantId = @ApplicantId
–Select @NickNamesText
–Select @ApplicantInfoText
–Select @ScoresText
Insert Into @FinalResults
values (@ApplicantId, @ApplicantInfoText + ‘ ‘ + @NickNamesText + ‘ ‘ + @ScoresText)
FETCH NEXT FROM cur_ApplicantRows INTO @ApplicantId
End
— Below statement will generate the Final Flat Applicant XML Result.
— Return back the final result set
Select 1 as Tag,
0 as Parent,
ApplicantID as [ConcatenatedApplicantInfo!1!ApplicantID],
ConcatenatedInfo as [ConcatenatedApplicantInfo!1!!cdata]
From @FinalResults as ConcatenatedApplicantInfo
FOR XML EXPLICIT
Discussion of Stored Procedure:
1) The AllApplicantInfo source XML Message is passed to the stored procedure via a parameter of type XML.
2) The AllApplicantInfo source XML Message is then shredded into an number of temporary relational tables:
@Applicant
@NickNames
@TestScores
@Relation
3) Sql Statements in the stored procedure are used to join the temp tables to produce the final <ConcatenatedApplicantInfo> node for each <Applicant> node in the source message.
4) The final destination message is returned using a Select statement via an XML Explicit Clause.
5) The Country Code table is directly available and can be joined to in a set operation in the stored procedure. This eliminates using Database Functoids in a BizTalk Map that would invoke multiple round trips to Sql Server.
6) A new feature of Sql Server 2005 is calling managed code. Managed code in Sql Server 2005 allows complex data manipulation that would be difficult to write using straight Transact SQL. In this example, calling managed code from stored procedure replaces the use of functoids in a BizTalk map. The below managed code was called from the stored procedure to determine the Province from a Province code.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetProvince (SqlInt32 country, SqlInt32 province)
{
string result = “”;
switch (country.Value)
{
case 1:
switch (province.Value)
{
case 1:
result = “Newfoundland”;
break;
case 2:
result = “PEI”;
break;
case 3:
result = “New Brunswick”;
break;
case 4:
result = “Nova Scotia”;
break;
case 5:
result = “Quebec”;
break;
case 6:
result = “Ontario”;
break;
case 7:
result = “Manitoba”;
break;
case 8:
result = “Saskatchewan”;
break;
case 9:
result = “Alberta”;
break;
case 10:
result = “British Columbia”;
break;
case 11:
result = “Yukon”;
break;
case 12:
result = “NWT”;
break;
default:
result = “Unknown”;
break;
}
break;
default:
result = “Unknown”;
break;
}
return result;
}
};
As below, BizTalk can invoke the stored procedure via the Sql Adapter passing the source message as a parameter and then receiving the destination message as the response.
Sql 2005 has added many enhancements to aid in XML processing.
a) XQuery expressions
An example is below:
for $i in /InvoiceList/Invoice/Items/Item
return
{$i/@Quantity}
{string($i/@Product)}
b) Xml Auto -> Nesting XML auto clauses
c) XML datatype
d) Indexing xml data
e) A subset of the FLWOR syntax
f) Validating XML against a Schema.
g) more..
If you are interested in some free online virtual labs that explore the new Sql 2005 XML capabilities, please click on the below links:
SQL Server™ 2005: XML Capabilities (TechNet) Virtual Lab
And:
Using Xquery with SQL Server 2005 XML Data (TechNet) Virtual Lab
Finally:
The not so good things about this method:
1) You have to call Sql server from BizTalk. When using BizTalk maps, the entire
transformation is localized to the BizTalk host process.
The good things about this method:
1) Simplified debugging of the transformation. For example, in the T-SQL you can place in print statements, select statements that return intermediate result sets, debug a stored procedure in Visual Studio, use the Sql Profiler etc.
2) In this example only a stored procedure needs to be deployed to a Sql Server database.
3) In this example, no data is persisted to permanent Sql Server tables. Only temp tables are utilized in the stored procedure.
Use this method:
1) As an alternative to writing custom XSLT.
2) If you are comfortable writing Sql Server Stored Procedures.
3) To replace database functoids in a map that incur multiple round trips to a database.
4) To perform transformations on larger messages
Some notes:
1) More than one XML message can be passed to a stored procedure.
2) Transformation operations can be split between the BizTalk maps and stored procedures. For example BizTalk maps can carry out a portion of the transformation and then pass the remainder of the transformation to the stored procedures.
3) For Sql 2000, OpenXML statements can be used to shred XML nodes into relational tables.
Conclusion:
Use the new XML enhancements along with the managed code in Sql 2005 to simplify and to improve the performance of complex BizTalk mapping operations.
Download the sample discussed above HERE. Read the ReadMe.Txt before installing and running.
$args is a special variable, an array of all arguments passed to a function on the command line. But, $args is always treated as an array in PowerShell. And It may cause an interesting side effect when passing $args to a downstream function.
Suppose we define a function as follows:
function t1
{
“args : $args, $($args.count)”
}
If you call the function in the command line as follows:
PS : Yjhong>C:\t1 r t g
The result will be
args: r t, 2
But, if you call the function in a function as follows:
function t2
{
t1 $args # my intention is passing the command line arguments that t2 received to the nested function, t1.
}
And, if you run the t2 as follows, you will get a different result as follows:
PS : Yjhong>C:\t2 r t
args: System.Object[], 1
It means that when the t1 function is called in a function, t1 will receive one System.Object[] object consisting of two elements, ’r’, and ’t’
I passed new Microsoft Certified Technology Specialist (MCTS) exam 70-235: Developing Business Process and Integration Solutions using Microsoft® BizTalk® Server 2006. Fresh experience with BizTalk 2006 helped a lot as well as reading documentation, blogs. Interestingly, this exam stands aside of new Microsoft Certified Professional Developer (MCPD) path. It would make sense to include it as an elective option earning credit towards MCPD: Enterprise Application Developer. This exam with the combination of:
would give well rounded Enterprise Application Developer skill set with emphasis on integration and business automation given that you’ve got to know all that stuff anyways to use BizTalk efficiently. I don’t know what Microsoft planned this certification for but without skills listed above one can’t develop a solid “Business Process and Integration Solutions”. So it does look like an orphan certification right now.
Microsoft has bought Winternals and Sysinternals. That means that Mark Russinovich (guardian against the dark forces of rootkits) is now a Microsoft employee! That has to be good news. It also means that my favourite weapon in my BizTalk debug toolkit, DebugView, is now owned by MS. Hope it stays freeware.
Previously posted at http://blog.solidsoft.com/blogs/charles_young_mvp_biztalk/default.aspx