XSLT Distinct another way to determine distinct in XSLT 1.0

February 17, 2011 / Comments Off on XSLT Distinct another way to determine distinct in XSLT 1.0

I had a requirement to map a buyer only if it was the same buyer throughout the entire document.
The reason for this was that in the source document the buyer was defined in a sub sub sub node of a document and in the destination it occurred only once.
So I ended up with several choices.

  • Only map the first buyer
  • Don’t map
  • Only map if they were the same throughout the entire document

For sure the first option would be a bad thing.
The second option would work for all parties involved (it’s an optional element in the output of the map) but the parties really want their buyer information if it’s there.
The third option seemed the best solution. I quickly googled on XSLT and distinct and there were some results. So I told the customer implementing a distinct wouldn’t be too hard. (it already existed in XSLT).
(I wish I looked a bit harder, cause then I would have seen that the distinct function of XSLT comes with XSLT 2.0 and sadly BizTalk is still using XSLT1.0)

After some thinking I got the following solution for this problem.

  1. Perform a count of the number of buyers in a document
  2. Get the first buyer (buyer is mandatory in the input document)
  3. Perform a count of the numbers of buyers where buyer != buyer found in step2

If the number in step 3 is 0 then we know all the buyers are the same. Below is the XSLT I used to perform this different distinct approach.

<xsl:template name=”Buyerparty_DocTemplate”>
  <xsl:param name=”var1″ />
  <xsl:param name=”var2″ />
  <xsl:param name=”dbg” />
  <xsl:variable name=”buyers” select=”count(/s0:Request/GeleverdePartij/LeveringsBericht/Levering[*]/Ladingdrager[*]/Goederen[*]/Koper/kop_gln)” />
  <xsl:variable name=”firstBuyer” select=”/s0:Request/GeleverdePartij/LeveringsBericht/Levering[1]/Ladingdrager[1]/Goederen[1]/Koper/kop_gln” />
  <xsl:variable name=”otherBuyers” select=”count(/s0:Request/GeleverdePartij/LeveringsBericht/Levering[*]/Ladingdrager[*]/Goederen[*]/Koper[not(kop_gln=$firstBuyer)])” />
  <xsl:if test=”$dbg=1″>
    <xsl:element name=”BuyerInfo”>
      <xsl:element name=”TotalBuyers”>
        <xsl:value-of select=”$buyers” />
      <xsl:element name=”FirstBuyer”>
        <xsl:value-of select=”$firstBuyer” />
      <xsl:element name=”OtherBuyers”>
        <xsl:value-of select=”$otherBuyers” />
  <xsl:if test=”$otherBuyers=0″>
    <xsl:if test=”string-length($firstBuyer) > 0″>
      <xsl:element name=”BuyerParty”>
        <xsl:element name=”PrimaryID”>
          <xsl:value-of select=”$firstBuyer” />
        <xsl:element name=”schemeID”>
          <xsl:value-of select=”$var1″ />
        <xsl:element name=”schemeAgencyName”>
          <xsl:value-of select=”$var2″ />

xs:int and xs:integer, what’s the difference….

February 8, 2011 / Comments Off on xs:int and xs:integer, what’s the difference….

I am busy creating schema’s and exposing them as a web service.
I always generate a client and try to post some messages and this time I was again surprised by BizTalk. (or should I say XML).
When creating a schema you can chose several types for an element. Some of these are xs:int and xs:integer.
I noticed these two before but didn’t bother too much.
But now for the first time I see there is a clear difference in the way stuff is treated by .Net. Below is a screenshot of a node with the type xs:integer.

I also have some regular elements of type xs:int. Below is a screenshot of that.

Now after I generated the WCF service for this schema, I imported the WSDL into VS 2005 and I was quite surprised to see what intellisense did to these elements in visual studio :

 So intellisense showed me it was actually a string !… And the other node of type xs:int was the .Net type I expected to see.

So what did I learn today, to stay away from xs:integer and use xs:int instead.
Hope this will help someone in the future, if it does, leave a comment


Millions of records in the BAMAlertsApplication and how to get rid of them (NSVacuum to the rescue)

February 3, 2011 / Comments Off on Millions of records in the BAMAlertsApplication and how to get rid of them (NSVacuum to the rescue)

As a BizTalk consultant I always implement BAM to do basic auditing. This is besides the BAM a business analist would want to see. The basic functionality of this audit trial is:

  • When was the message received
  • Where did it came from
  • Where did it go
  • What happend to the message
  • Any important business decision made in an orchestration

I write this audit data into a BAM view, and it has proven to be valuable information. From time to time you will get questions regarding messages and it’s always nice to have this information. Besides that you can set nice alerts ( if you use them) on specific events and have people mailed in case things go wrong. So this is nice and i wouldn’t want to do an implementation without it.

But there is a downside to it. All these audit records also write some data in a not very well documented database BAMAlertsApplication. And over time there could be millions of rows in them. Below is a sample of the BAMAlertsApplication

The records are just piling up and are consuming more and more resources from your SQL server. I had noticed this behaviour before and posted a question about it on the MDSN forums see “How to clean up the BAMAlertsApplication database.” And the answer of a MS Employee was to open a case by Microsoft. Yesterday the BizTalk Administrator of the customer that I work for, asked me if there was anything I could do about the size of this database that just kept on growing and growing. The administrator even pointing me to my own discussion on MSDN and stated that he wanted to indeed start a case with MS.

This triggered me to have a look at the database and by looking at the stored procedures ( I was looking for remove/archive/delete stored procedures) I noticed the NSVacuum stored procedure. This triggered me to see what it did. So I turned to the almighty google to see what it knew  about NSVacuum. I got only 2 results !.The first post wasn’t really encouraging, since it increased the databases



But looking at the code of the stored procedure I was convinced that it did some cleaning. So I dropped the “BizTalk” keyword from the search to have another look…..

This was not too encouraging either, only three real results this time. But fortunately the first one pointed me to a Microsoft document with usefull information. It turned out that NSVaccum was exactly what we needed. And after some runs the databas has now shrunk to a more reasonable size. See the picture below.

 So, the takaway is….., do NOT forget to schedule NSVacuum if you are using BAM !….

I really hope this is usefull for other people in the future, if it is, please leave reaction on my blog, it will keep me motivated to share my BizTalk experiences with the community !

  • Recent Posts
  • Recent Comments
  • Archives
  • Categories
  • Meta