Incremenent counter in nested for loop and maintain parent-child relationship(XSLT)

My requirement was to increment counter for each order and orderitems table. It is very simple in C# because
you can set the value of variable in each loop but in XSLT once you declare the variable you can not
change it. I have tried with couple of hours and at last found solution with scripting function in XSLT.

Below is my input XML

<orders>
<orderItems>
<Name>Test1</Name>
</orderitems>
<orderItems>
<Name>Test2</Name>
</orderitems>
<orderItems>
<Name>Test3</Name>
</orderitems>
</Orders>
<orders>
<orderItems>
<Name>Test4</Name>
</orderitems>
<orderItems>
<Name>Test5</Name></orderitems>
</Orders>

And here is my output XML.

I want to loop through all orders and orderitems and create seperate node and maintain parent- child
relationship.


<orderItems>
<Id>1 </Id>
<Name></Name>
<ParentID>0</ParentID>
</orderitems>
<orderItems>
<Id>2</Id>
<Name>Test1</Name>
<ParentID>1</ParentID>
</orderitems>
<orderItems>
<Id>3</Id>
<Name>Test2</Name>
<ParentID>1</ParentID>
</orderitems>
<orderItems>
<Id>4</Id>
<Name>Test3</Name>
<ParentID>1</ParentID>
</orderitems>

<orderItems>
<Id>5</Id>
<Name></Name>
<ParentID>0</ParentID>
</orderitems>
<orderItems>
<Id>6</Id>
<Name>Test4</Name>
<ParentID>5</ParentID>
</orderitems>
<orderItems>
<Id>7</Id>
<Name>Test5</Name>
<ParentID>5</ParentID>
</orderitems>

Here is my XSLT file

<xsl:for-each select="Orders">
     <xsl:variable name="var:v3" select="userCSharp:AddToCounter()" />
        <xsl:variable name="var:v5" select="userCSharp:GetCounter()" />


          <OrderItems>
      <Id><xsl:value-of select="userCSharp:GetCounter()"/></Id>
<Name>Test5</Name>
<ParentID>0</ParentID>
</orderitems>

    <xsl:for-each select="OrderItems">
            <xsl:variable name="var:v4" select="userCSharp:AddToCounter()" />
     <OrderItems>
      <Id><xsl:value-of select="userCSharp:GetCounter()"/></Id>
<Name>   <xsl:value-of select="$var:v5"/> </Name>
<ParentID>0</ParentID>
</orderitems>


    </xsl:for-each>
</xsl:for-each>

   
Add below script

 <msxsl:script language="C#" implements-prefix="userCSharp">
    <![CDATA[
    int mCounter= 1;
    public bool  AddToCounter()
    {
      mCounter = mCounter + 1;
      return true;
    }
 
    public int GetCounter()
    {
      return mCounter;
    }
   ]]>
  </msxsl:script>

Generate Footer Content using ITextSharp

ITextSharp provides the Pdfpageeventhandler class to handle the event at the time to generate pdf file. If you want to handle the event on start of page

or when close the pdf then inherit your class with Pdfpageeventhandler.

If you want to display footer on every page then can do this in OnEndPage event.

Below are the sample code to display content in footer

   public override void OnEndPage(PdfWriter writer, Document document)

        {

            base.OnEndPage(writer, document);

            PdfTemplate tmpFooter = pdfContent.CreateTemplate(600, 300);

             tmpFooter.MoveTo(1, 1);

            // Place the footer content

            tmpFooter.Stroke();

            // Begin writing the footer

            tmpFooter.BeginText();

          // Bold text for ther headers

            tmpFooter.SetFontAndSize(boldFont, 9);

            tmpFooter.ShowTextAligned(PdfContentByte.ALIGN_LEFT, "Total: ", 400, 80, 0);

            // Regular text for infomation fields

            tmpFooter.SetFontAndSize(baseFont, 9);

            tmpFooter.ShowTextAligned(PdfContentByte.ALIGN_LEFT, "$" + Total, 500, 80, 0);

            int pageN = writer.PageNumber;

            String text =  pageN + " of " + TotalPages;

            float len = baseFont.GetWidthPoint(text, 8);

            Rectangle pageSize = document.PageSize;

            pdfContent = writer.DirectContent;

            tmpFooter.SetFontAndSize(boldFont, 8);

            tmpFooter.SetTextMatrix(pageSize.GetLeft(440), pageSize.GetBottom(10));

            tmpFooter.ShowText(text);

            // End text

            tmpFooter.EndText();

            // Stamp a line above the page footer

            pdfContent.SetLineWidth(0f);

            pdfContent.MoveTo(20, 100);

            pdfContent.LineTo(580, 100);

            pdfContent.Stroke();

            pdfContent.AddTemplate(tmpFooter, 30, 1);

        }

SQL variable with comma separated value with IN clauses

Declare @CommaVal  varchar(max)

set @CommaVal   = ' test1, test2 ,test3, test4'

If you want compare comma separated values in sql statement as below

Select * From Orders where description in (@CommaVal   )

The above stored procedure retrurns zero records even if description matches with one of comma separated values.

There are two ways to solve this problem.

1) Split comma separated values and stored in temporary table and make In query on temporary table.

2) Second option to do this thing without creating temporary table and It is as below.

Select * From Orders where

(ISNULL(@CommaVal   , SPACE(0)) = SPACE(0) OR ',' + (@CommaVal + ',' LIKE '%,' + CONVERT(nvarchar, description) + ',%')

Insertion Using Table Type in Biztalk

1) Create Table type in sql server.
Create TYPE [eOrders] as TABLE(

[eCartId] [int] NOT NULL,
[ShippingMethodName] [Varchar](50) NULL,
[CustomerPO] [varchar](30) NULL,
[ShipToName] [varchar](50) NULL,
[ShipToAddress1] [varchar](50) NULL,
[ShipToAddress2] [varchar](50) NULL,
[ShipToCity] [varchar](50) NULL,
[ShipToStateZip] [varchar](50) NULL,
[Optional1] [varchar](255) NULL

 )



Create TYPE [eOrderItems] as TABLE(

CustomerPO [varchar](50) NULL ,
LineNumber int null,
Quantity int null,
Vendor char(3) null,
PartNumber varchar(50) null,
UnitPrice varchar(50) null,
Optional1 varchar(500) null,
Optional2 varchar(500) null,
Optional3 varchar(500) null,
Optional4 varchar(500) null

 )

2)  Create Insert Stored procedure.
create Procedure [InsertOrder]
(
@Orders as [Orders]  READONLY,
@OrderItems [OrderItems]] READONLY
)
as
Begin
Declare @OrderID int

Insert into Orders (eCartID , ShippingMethodID, CustomerPO, ShipToName, ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToZip)
Select PurchaseOrderNo  ShipToName , ShipToAddress1 , ShipToAddress2 , ShipToCity, ShipToStateZip
from @Order

set @OrderID = scope_identity()

Insert into OrderItems (CustomerPO,  LineNumber , Quantity , Vendor , PartNumber, UnitPrice, Optional1, Optional2 ,Optional3 , Optional4)
Select CustomerPO,  LineNumber , Quantity , Vendor , PartNumber, UnitPrice, Optional1, Optional2 ,Optional3 , Optional4
Price from @OrderItems
End


3) let's move to biztalk environment.

Create Insert schema using Add Generated Schema diglogue. Click on Add -> Add Generated schema -> Consume Adapter Service -> Consume WCF service
AFter then select your stored procedure. The schema will be generated as below.

4) After then Create Map to map your data with Insert schema as below. 

5) Construct Insert message in orchestration and able to insert records using table type in single trip.