Oracle packages can leverage VARRAY types to enable a caller to send a collection of values (an array) to a stored procedure in a single call. This VARRAY is very much like a single column Table Type in SQL Server. Generating the schema for this procedure required creating a class/assembly for the ODP.NET driver to use, which in turn is used by BizTalk. That process is fairly straightforward and documented well here. The one point missing from that blog is that the VARRAY object has to have an INDEXED BY INT in order for BizTalk to be able to use it; this is hinted at by the MSDN article on Limitations in the Oracle Adapter:
The Oracle Database adapter does not support PL/SQL tables that are not indexed by a numeric field.
It seems this limitation applies to VARRAY types (which makes sense…
I wrote previously (here) about using SQL Server Profile to capture and debug SQL calls from BizTalk. This method works well when there are no errors actually calling the procedure (but you want to tune the procedure using ‘real’ data from BizTalk). However, it’s not as much help when BizTalk can’t call the procedure at all – because of an invalid conversion, or a malformed table type, or some other errors in the procedure that prevent the procedure from actually being run. Either no event will get logged in the Profiler, or you will see only the stored procedure name but no parameters (because BizTalk realizes the procedure won’t be able to correctly execute).
Some recipients of BizTalk messages (such as the SQL Server adapter and some SOAP web services) will run into problems if there’s an empty node in the message, such as:
In this example, we’d really just want to have Keeper in there and get rid of the rest (to avoid other services from throwing exceptions on those empty/valueless nodes).
There are mapping patterns to address this, generally using a Value Mapping functoid with a Logical functoid. For example, you might have a Logical Existence or Logical String as the first parameter (see this blog for an example) to prevent an empty value from being mapped to the destinatino node. With a Table Looping functoid, you can achieve this for an entire table by having the first column be gated (see the Remarks section of the Table Looping refernece: MSDN). This technique works well,…
In the last post, I wrote about the overall structure of 837P claim messages. 837I and 837D messages have similar structures, though with some differences – for example, the 837I has many more diagnosis code options, and the 837D has more specific nodes for tooth identification, etc. One important thing to note is that 837P, 837I, and 837D files are very similar, but not the same. It’s not enough to simply change the version numbers in the GS and ST segments and then try to process one the same as the other! If it were that easy, we wouldn’t need separate schemas for them in BizTalk!
BizTalk provides out of the box functionality to support these message types, with well developed functionality to translate the X12 EDI to XML and vice versa. The schema nodes also have friendly names, translating something like this:
The BizTalk mapper is a phenomenal tool and part of what sets BizTalk apart as an enterprise middleware platform. Developers with little knowledge of XSLT can rapidly and accurately develop complicated transformations, and like all other aspects of the BizTalk engine it can be extended and heavily customized when necessary. This more than makes up for any limitations in the mapping engine. At the same time, sometimes the designer interface goes wrong and it’s difficult to figure out why: a functoid that has placeholders on a multipage map, or the desire to delete all functoids of a specific type from a specific page. On smaller maps, this usually isn’t a problem, but when dealing with larger documents (like HIPAA 837 claim or 834 enrollment files), understanding the file structure of .BTMs can be a huge time saver. It’s also possible to…
Muenchian Grouping is a powerful technique for to allow grouping by common values among looping/repeating nodes in an XML document. BizTalk does not have out of the box support for this, but it can be achieved by adding custom XSLT to a map. Chris Romp wrote a post about this years ago that serves as an excellent example of the idea in BizTalk: http://blogs.msdn.com/b/chrisromp/archive/2008/07/31/muenchian-grouping-and-sorting-in-biztalk-maps.aspx. The drawback of his method is that you lose all other Mapper functionality by using completely custom XSLT, and custom XSLT is more difficult to maintain than a BizTalk map.
Enter Sandro Periera’s phenomenal tutorial on Muenchian Grouping in BizTalk maps (https://code.msdn.microsoft.com/windowsdesktop/Muenchian-Grouping-and-790347d2). His solution is particularly powerful because it allows you to maintain the functionality and simplicity of the BizTalk Mapping engine while extending it to allow for the Muenchian Grouping technique as well. However, there is still…
I had a situation where I needed to compare a query string parameter to a value in a list inside a data view web part in SharePoint Designer. If the two values matched in regards to the text, but did not match in regards to case (for example: SomeWord vs. someword), they would not match when compared in the XSLT. It appears that the version of XSLT used in a data view web parts doesn’t support the lower-case() and upper-case() functions.
Luckily, I discovered a workaround. Originally, I had the following code:
<xsl:variable name=”Rows” select=/dsQueryResponse/MyList/Rows/Row[normalize-space(@MyField) = $myQueryStringVar]”/>
At first, I tried this:
<xsl:variable name=”Rows” select=/dsQueryResponse/MyList/Rows/Row[normalize-space(lower-case(@MyField)) = lower-case($myQueryStringVar)]”/>
But I received an error that the function was not supported. The workaround is as follows:
<xsl:variable name=”Rows” select=”/dsQueryResponse/MyList/Rows/Row[normalize-space(translate(@MyField,$upper,$lower)) = translate($myQueryStringVar,$upper,$lower)]”/>
So you’ve finished writing your new XSLT file using your favorite XML/XSLT editing tool. Great. Now for the next problem, you’ve been given the task to transform thousands of XML documents using your new XSLT file. Great… There’s no way we can transform all of these documents by hand. At this point we should be thinking about automating this process in the fastest and most efficient way possible. This scenario may sound familiar to some of us. Luckily, .Net provides us with such options.
For this example we’ll be serializing an XML file and transforming it using .Net’s XSLT processor. The process is very simple, and requires very little code.
Load the XML File into memory using the XmlReader class.
var reader = XmlReader.Create(xmlFileLocation);
Create a StreamWriter object which will be used to write the transformation to a file.
var textWriter = new StreamWriter(outputFileLocation);