Biztalk Nibbles: November 2011

Thursday, November 3, 2011

User Defined Table Types and its use

Programmability > Types > User-Defined Table Types

================
USE [GGGG]
GO

CREATE TYPE [pppp].[mmmm] AS TABLE(
[AAA] [varchar](20) NULL,
[BBB] [int] NULL,
[CCC] [varchar](500) NULL,
[DDD] [nvarchar](2000) NULL,
[EEE] [varchar](50) NULL,
[FFF] [int] NULL,
[GGG] [bit] NULL
)
GO
=========================== use =======================

USE [pmpm]
GO

/****** Object: StoredProcedure [pppp].[mmmm] Script Date: 11/03/2011 13:54:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [pppp].[mmmm]
@cdd [pppp].[mmmm] READONLY
AS

DECLARE @AAA AS [varchar](20)
DECLARE @BBB AS [int]
DECLARE @CCC AS [varchar](500)
DECLARE @DDD AS [nvarchar](2000)
DECLARE @EEE AS [varchar](50)
DECLARE @FFF AS [int]
DECLARE @GGG AS [bit]

IF NOT EXISTS(
SELECT A1.AAA
FROM @cdd A1
INNER JOIN @cdd A2
ON (A1.AAA=A2.AAA) AND
((A1.BBB <>3 AND A2.BBB=3)OR
(A1.BBB =3 AND A2.BBB<>3)OR
((A1.BBB <>3 AND A2.BBB<>3))AND(A1.BBB=A2.BBB))
group by A1.AAA
having COUNT(*)> 2
) AND
((SELECT COUNT(*) FROM @cdd)> 0)

BEGIN
DELETE FROM [pmpm].[Reports].[TTTTT]
WHERE EEE1 = 'CCD'AND DATEDIFF(MI,created_datetime,GETDATE())>1

INSERT INTO [pmpm].[Reports].[TTTTT]
( [AAA1]
,[BBB1]
,[CCC1]
,[DDD1]
,[EEE1]
,[FFF1]
,[active_flag]
)
SELECT
A.AAA,A.BBB,A.CCC,A.DDD,A.EEE,A.FFF,A.GGG
FROM @cdd A left OUTER JOIN
[Reports].[TTTTT] w ON A.AAA = w.AAA1
WHERE
(A.BBB<>3)and w.userid IS NULL

DECLARE ccd_cursor CURSOR FOR
SELECT A.AAA,A.BBB,A.CCC,A.DDD,A.EEE,A.FFF,A.GGG
FROM @cdd A
WHERE A.BBB = 3

OPEN ccd_cursor;
FETCH NEXT FROM ccd_cursor
INTO @AAA,@BBB, @CCC,@DDD,@EEE,@FFF, @GGG;

WHILE @@FETCH_STATUS = 0
BEGIN
-- If BBB1=3 username is not present in TTTTT insert the record from @cdd

IF((SELECT COunt(*) FROM [pmpm].[Reports].[TTTTT] WHERE AAA1 = @AAA) = 0)
BEGIN
INSERT INTO [pmpm].[Reports].[TTTTT]
( [AAA1]
,[BBB1]
,[CCC1]
,[DDD1]
,[EEE1]
,[FFF1]
,[active_flag]
)
values(@AAA,@BBB, @CCC,@DDD,@EEE,@FFF, @GGG)
END
-- If BBB1=3 username is present in TTTTT insert the CCC1 i.e., country from @cdd
IF((SELECT COunt(*) FROM [pmpm].[Reports].[TTTTT] WHERE AAA1 = @AAA) > 0)
BEGIN
Update [pmpm].[Reports].[TTTTT]
SET CCC1 = CCC1 +','+ @CCC
WHERE
((AAA1 = @AAA)
AND (@BBB= 3) AND (BBB1=3)
AND (CCC1 NOT LIKE '%' + @CCC + '%'))
END

FETCH NEXT FROM ccd_cursor
INTO @AAA,@BBB,@CCC,@DDD,@EEE,@FFF,@GGG;
END
CLOSE ccd_cursor;
DEALLOCATE ccd_cursor;
END

GO

From Any Type

<xsl:template name="MyBarCodeXsltConcatTemplate">
<xsl:param name="seqno" />
<xsl:element name="PostalServiceBarCode">
<xsl:value-of select = "/*[local-name()='FieldingBatchWrapper' and namespace-uri()='MM:P']
/*[local-name()='FieldingBatch' and namespace-uri()='PP:P']/*[local-name()='Surveys' and namespace-uri()='PP:P']/*[local-name()='Survey' and namespace-uri()='PP:P'][$seqno+0]/*[local-name()='Unit' and namespace-uri()='PP:P']/*[local-name()='Contacts' and namespace-uri()='PP:P']/*[local-name()='Contact' and namespace-uri()='PP:P']/*[local-name()='Extension' and namespace-uri()='PP:P']/*[local-name()='AddressExtension']/*[local-name()='Finalist']/*[local-name()='BarCode']/text()" />
</xsl:element>
</xsl:template>

Tuesday, November 1, 2011

MAP Global Variable and USE with Cumulative Functoid

This funtoid should be connected to single occuring unique element like GUID from Input MAP so that for each new records its value will get reassigned( to empty "").

First Scirpting Functoid with two Input:

public void GetTelephone(string UPPhonenum, string Phonenum)
{
if (UPhomePhone.Equals(String.Empty))
{
UPhomePhone = UPPhonenum;
}
if (homePhone.Equals(String.Empty))
{
homePhone = Phonenum;
}
}
================
Cumulative Funtoid: Scripting, Constant 1
====================
Scirpting Functoid Connected to Top element of output schema in Pic. above:

public string retHomeNum(string IgnoreValue)
{
if (!String.IsNullOrEmpty(UPhomePhone))
return UPhomePhone;
else
return homePhone;
}

====================
Middle element in Output Schema:

public string retOfficePhone()
{
if (!String.IsNullOrEmpty(UPofficePhone))
return UPofficePhone;
else
return officePhone;
}
======================

Mapping Derivation Type - Any Sequence compare sibilings

If mapping Schema input has a Deriviation type.If you have compare the sibling element in sequence with Sibling elements in other similar record then best way is to go with XSLT Template. Here Electronic Email is Derivation Type With Sequence of Type Any and Sequence of Email Address.
Each EmailAddress is associated with ContactType (ex. Primary Email, secondary email etc).

Here we are comparing for example Primary E-mail address from Record 1 with Record 2. If Record 1 is present then assign it to output if not then assign it from Record2.

Here we used Inline XSLT call template compared Both values and the assigned it to Email-Add-1 to 3.

Inline XSLT is as follows:

xsl:template name="MyBarCodeXsltConcatTemplate1">
<xsl:param name="seqno1"/>
<xsl:choose>
<xsl:when test ="string-length(/*[local-name()='DetailedRecordWrapper' and namespace-uri()='uri1:1']/*[local-name()='DetailedRecord' and namespace-uri()='uri2:2']/*[local-name()='SingleRecords' and namespace-uri()='uri2:2']/*[local-name()='SingleRecord' and namespace-uri()='uri2:2'][$seqno1+0]/*[local-name()='UpdatedContacts' and namespace-uri()='uri2:2']/*[local-name()='Contact' and namespace-uri()='uri2:2']/*[local-name()='EmailAddress'][../*[local-name()='ContactType']/text( )='Primary E-mail']/text()) > 0" >
<xsl:element name="CUST-EMAIL-ADD-1"><xsl:value-of select = "/*[local-name()='DetailedRecordWrapper' and namespace-uri()='uri1:1']/*[local-name()='DetailedRecord' and namespace-uri()='uri2:2']/*[local-name()='SingleRecords' and namespace-uri()='uri2:2']/*[local-name()='SingleRecord' and namespace-uri()='uri2:2'][$seqno1+0]/*[local-name()='UpdatedContacts' and namespace-uri()='uri2:2']/*[local-name()='Contact' and namespace-uri()='uri2:2']/*[local-name()='EmailAddress'][../*[local-name()='ContactType']/text( )='Primary E-mail']/text()"/></xsl:element>
</xsl:when>
<xsl:otherwise>
<xsl:element name="CUST-EMAIL-ADD-1">
<xsl:value-of select = "/*[local-name()='DetailedRecordWrapper' and namespace-uri()='uri1:1']/*[local-name()='DetailedRecord' and namespace-uri()='uri2:2']/*[local-name()='SingleRecords' and namespace-uri()='uri2:2']/*[local-name()='SingleRecord' and namespace-uri()='uri2:2'][$seqno1+0]/*[local-name()='Survey' and namespace-uri()='uri2:2']/*[local-name()='Unit' and namespace-uri()='uri2:2']/*[local-name()='Contacts' and namespace-uri()='uri2:2']/*[local-name()='Contact' and namespace-uri()='uri2:2']/*[local-name()='EmailAddress' and namespace-uri()='uri2:2'][../*[local-name()='ContactType']/text( )='Primary E-mail']/text()"/>
</xsl:element>
</xsl:otherwise>
</xsl:choose>
</xsl:template>

------------------

Important thing worth to mention is seqno+0 gives the correct result, though mathematically it seems absurd.
seqno- Looping Iteration

/*[local-name()='EmailAddress' and namespace-uri()='uri2:2'][../*[local-name()='ContactType']/text( )='Primary E-mail']/text()"

This one checks EmailAddress with the sibling record ContactType-"Primary E-mail"