Biztalk consulting & training

I am doing the following services

1.Biztalk consulting (soltution, architect,implementation for enterprise)
2.Biztalk training for indivual as well as corparate
3.Freelance work

Updating multiple tables from BizTalk and validating the incoming message in SP

Sometimes, we may have the requirements to insert/update more than one table in SQL server from BizTalk using SQL Adapter as well as we have to validate the passing parameter - message in SP before updating database. This will help us to check the data type as well as data.

We need to follow the following steps

Step 1:
Create xml schema collections in SQL 2005

CREATE XML SCHEMA COLLECTION [dbo].[TableSchema] AS N''

Step 2:
Create the stored procedure as below


CREATE PROCEDURE [dbo].[iu_Table_sp]
@xmlTable AS XML (TableSchema)

Pulling records from SQL data as tree structure (multi-level) xml

If you want to create multi level(root ->child record-child record)xml message from SQL for biztalk inbound, then you have to use for xml explicit keyword for t-sql and the example is given below

SELECT
Tag
,Parent
,'Table' AS [Table!1!sEntity!element]
,sTable_GUID AS [Table!1!sTable_GUID!element]

,sPhone_no AS [Tablephone!2!sPhone_no!element] ,sPhoneType_cd AS [Tablephone!2!sPhoneType_cd!element]
,sAddressType_cd AS [Tableaddress!3!sAddressType_cd!element]
,sAddress_nm AS [Tableaddress!3!szAddress_nm!element
FROM
tbTableDetails
FOR XML EXPLICIT, BINARY BASE64

Finding biztalk installation directory

SET BMPATH=
FOR /F "tokens=2* delims= " %%A IN ('REG QUERY "HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0" /v InstallPath') DO SET BMPATH=%%B
IF "%BMPATH%" EQU "" (
ECHO ERROR Locating the BizTalk Installation directory
ECHO Updates not completed
)
GOTO :EOF

Biztalk Map - Adding (Carriage Return, Line Feed) / New Line in element

We have three fields as address1, address2 and address3 in source schema and in destination side; we want to concatenate them with new line between address1, address2 and address3. if we want newline between data in element, we can achieve it using ASCII to Character functoid with passing input value as 13.

Biztalk - Sending an email with html tags and non html tags (<, >, &)

Sending an email with html tags and non html tags (<, >, &)

For example, if we want email body output as below

click here to see kannannova.blogspot.com
Thanks & Regards

We have to do the following steps for above output
1.Create custom send pipeline with MIME/SMIME encoder
2. Set the part's SetPartProperty as "text/html". If needed, do url encoding too
3.Do html encoding for html tags only
4.Use escape sequence characters for non html tags (< , > ,&)
<'s escape character is < >'s escape character is >
&'s escape character is &

Technorati

Technorati Profile