Two handy references for BizTalk XSLT:

Substring in XSLT Value-Of XPATH

The first example is fairly easy, I just needed to get the date/time without the timezone modifier, in other words to convert this: 2016-01-29T00:00:00-08:00

to this: 2016-01-29T00:00:00.

BizTalk_XSLT_XPath_Substring
The reason was that our web service was only coded to handle the date without the timezone. It used an Orcacle date/time conversion like this:

eValue = "TO_DATE('" + eValue.Replace("T", " ").Replace(".0000000", "") + "','YYYY-MM-DD HH24:MI:SS')";

XPath “Where” clause when using complex namespaces

Take a look at the data below. I needed to map the EventDate associated with DateType=PLN to D02, and the EventDate associated with DataType=FCT to D03.

Biztalk_Xpath_XSLT_Sample_Data

Most of the examples on the internet show you how to do this when you don’t have namespaces involved. But when you do have namespaces, the trick is to use nested queries in your XPATH.  In the sample below, you can see that the text in yellow is complete inside the other xpath brackets.

Using [local-name()=’xxx”] is like saying find me the element whose name matches ‘xxx’.

Once the where has been matched, the “xsl:value-of” statement begins with ./ to go up one level, then to find the EventDate (that is on the same level as DateType) to put in the output field.

BizTalk_XSLT_Nested_Xpath_Where_Clause

Sample Code

XSLT_Bad_Value-Of_element

Error

MyMap.btm: error btm1023: Exception Caught: The ‘=’ character, hexadecimal value 0x3D, cannot be included in a name. Line 4, position 23.

Solution

This was a simple typo. I typed in “value-of-select=” instead of “value-of select=” (I erroneously included an extra dash between the “of” and the “select”). The parser saw the entire element name as “value-of-select=”, so of course, you cannot have an equal sign in an element name.

Context

I was trying to merge data from two incoming messages in a BizTalk Map, using the XSLT technique described here:
https://blogs.msdn.microsoft.com/biztalkcpr/2009/06/01/how-to-join-two-schemas-in-a-map-when-they-contain-namespaces/

Two things that I would like to add to that blog:

  1. You need to use an XSLT Template instead of just inline XSLT because you need to pass a parameter.
  2. When using the XSLT Template, you need to connect the Scripting Functoid to a field on the right side of the map; otherwise the Template will never get called.

 

How do you test a multi-part map, i.e. a map with multiple input messages?

To test a normal map, you just take an sample XML file, click the map (in Visual Studio Solution Explorer), set the property of the filename into the value of the property “TestMap Input”, then right-click on the map and select “Test Map”. The output is shown in the Output window, and you can do a CNTL-Click on the output file to view the results.

Maps created in the normal mapper usually just have one input message; but maps created in an orchestration can easily have multiple input messages (just by selecting more than one incoming message in Transform shape). This creates a special structure, or wrapper around the messages. You can still do a test-map, but the file you input to the test map must be in the special format as follows:

<ns0:Root xmlns:ns0="http://schemas.microsoft.com/BizTalk/2003/aggschema">
	<InputMessagePart_0>
		<ns1:Schema1RootElement xmlns:ns1="http://MyNamespace.Schema1">
		</ns1:Schema1RootElement>
	</InputMessagePart_0>
	<InputMessagePart_1>
		<ns2:Schema2RootElement xmlns:ns2="http://MyNamespace.Schema2">
		</ns2:Schema2RootElement>
	</InputMessagePart_1>
</ns0:Root>

You would put your own elements and attribute between Schema1RootElement and it’s closing element, as well as between Schema1RootElement2 and it’s closing element.

So normally, you use some text or XML editor (like NotePad++) and paste data samples, then wrap them with the XML element wrappers shown above.

If your schemas don’t have target namespaces, you can simplify as follows:

<ns0:Root xmlns:ns0="http://schemas.microsoft.com/BizTalk/2003/aggschema">
	<InputMessagePart_0>
		<Schema1RootElement>
		</Schema1RootElement>
	</InputMessagePart_0>
	<InputMessagePart_1>
		<Schema2RootElement>
		</Schema2RootElement>
	</InputMessagePart_1>
</ns0:Root>

So basically the root element is part of a special namespace called the “aggschema” (aggregate schema). Then, as many message as you added each are found, but each is wrapped with N) where N start with 0, and increments once per each message.


USE BizTalkDTADb
--select top 10 * from dbo.dta_DebugTrace
select COUNT(*) from dbo.dta_DebugTrace as TotalRowCount

SELECT YEAR(dtBeginTimeStamp) AS Yr,
Month(dtBeginTimeStamp) AS Mo,
COUNT(*) AS [RowCount]
FROM dbo.dta_DebugTrace nolock
GROUP BY
YEAR(dtBeginTimeStamp),
DatePart(mm,dtBeginTimeStamp)
Order BY
YEAR(dtBeginTimeStamp),
Month(dtBeginTimeStamp)

The results show you by year/month how many rows. In most cases, you will find you need to purge data prior the current month. At least on a test system, I can think of any possible use of month old orchestration trace data (or even on Production for that matter).  But if you have long-running dehydrated orhestrations you might need it).

dta_DebugTrace_Counts

Orchestrations write data to these tables each time they run (when Trace is enabled).

Go to SQL Agent on the SQL Server that supports your BizTalk server, check the job entitled: DTA Purge and Archive (BizTalkDTADb)
SQLAgent_Jobs_BizTalk_2010
I take the original code that is there, and copy it to the line below, then comment out the code that is there with the T-SQL comment (two dashes).
Then change the second line to the parameters you want. Follow-that by running the job manually, or set it up to run on a scheduled basis.


--exec dtasp_BackupAndPurgeTrackingDatabase
0, --@nLiveHours tinyint, --Any completed instance older than the live hours +live days
1, --@nLiveDays tinyint = 0, --will be deleted along with all associated data
30, --@nHardDeleteDays tinyint = 0, --all data older than this will be deleted.
null, --@nvcFolder nvarchar(1024) = null, --folder for backup files
null, --@nvcValidatingServer sysname = null,
0 --@fForceBackup int = 0 --


exec dtasp_BackupAndPurgeTrackingDatabase 24, 30, 32, 'e:\Backup\BizTalkDatabases\', null, 0

MSDN Reference: https://msdn.microsoft.com/library/aa558715.aspx

NOTE: Running the job creates a backup, so if your DTA database is large, and you are short on disk space, you will have to make sure you find disk space for the backup to be written.

If you get odd errors, try running the command directly in a SQL query window, then the a more useful error may be displayed than what you would see in the “View History” of the SQL Agent Job.

dtasp_BackupAndPurgeTrackingDatabase_Errors


The hard purge window cannot be less than the live data window [SQLSTATE 42000] (Error 50000).
The step failed.

After changing the second 30 to 32, I re-ran it, and saw this result:

Successful_Run_of_dtasp_BackupAndPurgeTrackingDatabase

 

The Parms are as follows:
dtasp_BackupAndPurgeTrackingDatabase_storedProc_Parms

 

However, after running it, I still saw many of the same rows there (this is on BT2010 by the way). I did some more research, found Sandro Pereira’s blog and ran the following:


declare @dtLastBackup datetime
set @dtLastBackup = GetUTCDate() exec dtasp_PurgeTrackingDatabase 1, 0, 7, @dtLastBackup

I brought a Rosetta .xsd schema into a new BizTalk Project, gave it a compile and got 1524 occurences of the following error:

The Build Errors


------ Rebuild All started: Project: BTS2C7Outbound, Configuration: Debug Any CPU ------
e:\MyDir\BTS2C7Outbound\Schemas\2C7\Domain\Logistics\CodeList\RN_ResponseCode_01_00.xsd.cs(13,17): error CS0234: The type or namespace name 'NonSerializedAttribute' does not exist in the namespace 'BTS2C7Outbound.Schemas._2C7.System' (are you missing an assembly reference?)
e:\MyDir\BTS2C7Outbound\Schemas\2C7\Domain\Logistics\CodeList\RN_ResponseCode_01_00.xsd.cs(13,17): error CS0234: The type or namespace name 'NonSerializedAttributeAttribute' does not exist in the namespace 'BTS2C7Outbound.Schemas._2C7.System' (are you missing an assembly reference?)
...
e:\MyDir\BTS2C7Outbound\Schemas\2C7\Domain\Logistics\CodeList\RN_ContainerType_01_02.xsd.cs(201,17): error CS0234: The type or namespace name 'SerializableAttribute' does not exist in the namespace 'BTS2C7Outbound.Schemas._2C7.System' (are you missing an assembly reference?)
e:\MyDir\BTS2C7Outbound\Schemas\2C7\Domain\Logistics\CodeList\RN_ContainerType_01_02.xsd.cs(201,17): error CS0234: The type or namespace name 'SerializableAttributeAttribute' does not exist in the namespace 'BTS2C7Outbound.Schemas._2C7.System' (are you missing an assembly reference?)

Compile complete — 1524 errors, 0 warnings
========== Rebuild All: 0 succeeded, 1 failed, 0 skipped ==========

 

RosettaNet_type_Namespace_Serializable.AttributeAttribute_does_not_exit

Solution

You must change the “Namespace” property on the following two schemas in the “System” folder; one directly in that folder, and one the “CodeList” subfolder.
Two_Rosetta_Schemas

Here is an example of the original values:
Rosetta_Schema_Namespace_Fix

Change the .System in yellow above to ._System. or some other value.

Rebuild, and your error should go away.

For additional information about bringing RosettaNet PIPs into BizTalk projects, see this link:

https://msdn.microsoft.com/en-us/library/ff720376.aspx