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>

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"

string split

string s = "tubo1|tubo2|tubo3";
string[] splited = s.split ('|') // it yields you array of three elements
"tubo1", ""tubo2", "tubo3"
// count is used if you want to reduce the number of elements returned

BizTalk Send Port Save File Micro

1. %SourceFileName%
2. %datetime.tz%
Local date time plus time zone from GMT in the format YYYY-MM-DDThhmmssTZD, (for example, 1997-07-12T103508+800).

Scripting Functoid Padding Integer

public string MyConcatsCount(int count){
             
              string  param1 = "0000000000"+ count.ToString();
               param1 = param1.Substring(param1.Length-10);
return param1;
}
=========
count=4
output: 0000000004

MAP- Records Appearing Multiple TImes

If Records are appearing Multiple Times don't forget to Joining Multiple Occurring element from Input and Output Schema

Get String after and before special character

namevaluestrg = "HHmm-ss";
 int di = namevaluestrg.IndexOf("-");
 string output = namevaluestrg.Substring(di+1);
 string output1 = namevaluestrg.Substring(0, di);


output: s
Output1: HHmm