There are two things we are going to discuss in this post:

  1. Dealing with namespaces in tsql without using the WITH XMLNAMESPACES statement
  2. Setting setting data from a sql variable in the modify statement

I have always struggled with the correct placement of the WITH XMLNAMESPACES statement: I can’t get it in the correct place or ‘escaped’ correctly, so I can never save the stored procedure. So we will show how you can use the methods to deal with XML without having to declare the WITH XMLNAMESPACES statement and in this case we are going to modify the existing value, passing in the variable from the sql statement.

To remove the use of WITH XMLNAMEPACES statement, you simply have to put in your xml method the following statement: ’declare namespace ns0=”http://whateveryournamespaceis.com”; {your work here}’

When using the modify statement, I wanted to pass a variable in from the stored procedure; you have to use the with sql:variable statement

Here is a simple example:

declare @xml XML = 
'<XML xmlns="http=stottcreations.com/data">
   <Record>
      <Element>Value1</Element>
   </Record>
 </XML>'
 declare @replaceValue char(5)='ABCDE'
 set @xml.modify('declare namespace ns0="http=stottcreations.com/data";
                  replace value of(ns0:XML/ns0:Record/ns0:Element/text())[1]
                  with sql:variable("@replaceValue")')
 select @xml

Which results in this xml

<XML xmlns="http=stottcreations.com/data">
  <Record>
    <Element>ABCDE</Element>
  </Record>
</XML>

Notice that the original xml did not have a namespace prefix (ns0), however I added it to the queries.

Also notice in the sql the locations of the single and double quotes.

XSL Tricks

 Uncategorized  Comments Off
Feb 112015
 

I have mentioned this a couple of times before:

I HATE FUNCTIODS

I have reverted to using XSL for nearly all mapping requirements (except straight across mapping)

I have created a simple map that maps pretty much straight across using XSL:

Here is one of the functiods:

This has always worked, the issue is that if I change the context, ALL of my Inline XSLT scripts are broken:

What I have done before is go into each scripting functiod and change the XSLT. – What a pain.

Here I am going to show you a ‘trick’ to save yourself the headache.

We are going to use Inline XSLT Call Template

I have always had an issue with a Call Template, “I am creating a template to use, but nothing is ‘reusable’ in my mind” For instance: my output to FName isn’t reusable in any other scenario.

We are going to show how it can be reusable!

I am going to create a mapping for the FName, but to make it reusable, I am going to ‘pass’ into the call template the source node and output name:

Now for the fun, I want to create this template so I can reuse it, so here is what I have written:

The code:

 <xsl:element name="($OutputNode)">

is essentially saying: I want the real value of this variable as an output

All I have to do is copy the contents of this script and create new functiods and just put the output node name as an argument in the scripting functiod

Here is my map

Here is the underlying XSL

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var" version="1.0" xmlns:ns0="http://StottCreations.com/XSL.DataDefinition">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:template match="/">
    <xsl:apply-templates select="/ns0:Input" />
  </xsl:template>
  <xsl:template match="/ns0:Input">
    <ns0:Output>
      <Line>
        <xsl:call-template name="MakeOutput">
          <xsl:with-param name="InputValue" select="string(Record/@FirstName)" />
          <xsl:with-param name="OutputNode" select="&quot;FName&quot;" />
        </xsl:call-template>
        <xsl:call-template name="MakeOutput">
          <xsl:with-param name="InputValue" select="string(Record/@LastName)" />
          <xsl:with-param name="OutputNode" select="&quot;LName&quot;" />
        </xsl:call-template>
        <xsl:call-template name="MakeOutput">
          <xsl:with-param name="InputValue" select="string(Record/@Address)" />
          <xsl:with-param name="OutputNode" select="&quot;AddressLine&quot;" />
        </xsl:call-template>
        <xsl:call-template name="MakeOutput">
          <xsl:with-param name="InputValue" select="string(Record/@City)" />
          <xsl:with-param name="OutputNode" select="&quot;City&quot;" />
        </xsl:call-template>
        <xsl:call-template name="MakeOutput">
          <xsl:with-param name="InputValue" select="string(Record/@State)" />
          <xsl:with-param name="OutputNode" select="&quot;State&quot;" />
        </xsl:call-template>
        <xsl:call-template name="MakeOutput">
          <xsl:with-param name="InputValue" select="string(Record/@Zip)" />
          <xsl:with-param name="OutputNode" select="&quot;ZipCode&quot;" />
        </xsl:call-template>
      </Line>
    </ns0:Output>
  </xsl:template>
  <xsl:template name="MakeOutput">
 <xsl:param name="InputValue" />
 <xsl:param name="OutputNode" />
 <xsl:element name="($OutputNode)">
   <xsl:value-of select="$InputValue" />
 </xsl:element>
</xsl:template>
</xsl:stylesheet>

Now if I change the context, I don’t have to remap anything!

And here is the underlying XSL (without me having to go in an change anything in the scripting functiods)

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var" version="1.0" xmlns:ns0="http://StottCreations.com/XSL.DataDefinition">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:template match="/">
    <xsl:apply-templates select="/ns0:Input" />
  </xsl:template>
  <xsl:template match="/ns0:Input">
    <ns0:Output>
      <xsl:for-each select="Record">
        <Line>
          <xsl:call-template name="MakeOutput">
            <xsl:with-param name="InputValue" select="string(@FirstName)" />
            <xsl:with-param name="OutputNode" select="&quot;FName&quot;" />
          </xsl:call-template>
          <xsl:call-template name="MakeOutput">
            <xsl:with-param name="InputValue" select="string(@LastName)" />
            <xsl:with-param name="OutputNode" select="&quot;LName&quot;" />
          </xsl:call-template>
          <xsl:call-template name="MakeOutput">
            <xsl:with-param name="InputValue" select="string(@Address)" />
            <xsl:with-param name="OutputNode" select="&quot;AddressLine&quot;" />
          </xsl:call-template>
          <xsl:call-template name="MakeOutput">
            <xsl:with-param name="InputValue" select="string(@City)" />
            <xsl:with-param name="OutputNode" select="&quot;City&quot;" />
          </xsl:call-template>
          <xsl:call-template name="MakeOutput">
            <xsl:with-param name="InputValue" select="string(@State)" />
            <xsl:with-param name="OutputNode" select="&quot;State&quot;" />
          </xsl:call-template>
          <xsl:call-template name="MakeOutput">
            <xsl:with-param name="InputValue" select="string(@Zip)" />
            <xsl:with-param name="OutputNode" select="&quot;ZipCode&quot;" />
          </xsl:call-template>
        </Line>
      </xsl:for-each>
    </ns0:Output>
  </xsl:template>
  <xsl:template name="MakeOutput">
 <xsl:param name="InputValue" />
 <xsl:param name="OutputNode" />
 <xsl:element name="($OutputNode)">
   <xsl:value-of select="$InputValue" />
 </xsl:element>
</xsl:template>
</xsl:stylesheet>
Feb 042015
 

Building off the previous entry, I want to map data from the OBR3.1 into each corresponding OBX15.1

Originally, I was lazy, and just mass copied the input to the output

<OBX_ObservationResult>
  <xsl:copy-of select="./@*" />
  <xsl:copy-of select="./*" />
</OBX_ObservationResult>

So I need to create a mapping for each element so I can map the specific element. As I am too lazy to do start typing, I use the BizTalk mapper and drag the OBX_ObservationResult and link it by structure. I get this:

I then validate the map so I can see the underlying xsl:

<xsl:for-each select="OBX_ObservationResult[2]">
  <OBX_ObservationResult>
  <xsl:if test="OBX_1_SetIdObx">
    <OBX_1_SetIdObx>
      <xsl:value-of select="OBX_1_SetIdObx/text()" />
    </OBX_1_SetIdObx>
  </xsl:if>
  <xsl:if test="OBX_2_ValueType">
    <OBX_2_ValueType>

Since I already have the for-each properly defined all I really need to copy is the children of the OBX_ObservationResult logic to my real map.

Once I have got it in the map, I simply need to map the OBR3.1 data into the OBX15.1

So I look at the code that maps to the OBX15

<xsl:for-each select="OBX_15_ProducerSId">
  <OBX_15_ProducerSId>
    <xsl:if test="CE_0_Identifier">
      <CE_0_Identifier>
        <xsl:value-of select="CE_0_Identifier/text()" />
      </CE_0_Identifier>
    </xsl:if>
    <xsl:if test="CE_1_Text">
      <CE_1_Text>
        <xsl:value-of select="CE_1_Text/text()" />
      </CE_1_Text>
    </xsl:if>
    <xsl:if test="CE_2_NameOfCodingSystem">
      <CE_2_NameOfCodingSystem>
        <xsl:value-of select="CE_2_NameOfCodingSystem/text()" />
      </CE_2_NameOfCodingSystem>
    </xsl:if>
    <xsl:if test="CE_3_AlternateIdentifier">
      <CE_3_AlternateIdentifier>
        <xsl:value-of select="CE_3_AlternateIdentifier/text()" />
      </CE_3_AlternateIdentifier>
    </xsl:if>
    <xsl:if test="CE_4_AlternateText">
      <CE_4_AlternateText>
        <xsl:value-of select="CE_4_AlternateText/text()" />
      </CE_4_AlternateText>
    </xsl:if>
    <xsl:if test="CE_5_NameOfAlternateCodingSystem">
      <CE_5_NameOfAlternateCodingSystem>
        <xsl:value-of select="CE_5_NameOfAlternateCodingSystem/text()" />
      </CE_5_NameOfAlternateCodingSystem>
    </xsl:if>
    <xsl:value-of select="./text()" />
  </OBX_15_ProducerSId>
</xsl:for-each>

I want to remove the ‘dependency’ on the input of of the OBX15, I want to create it regardless. So I remove the for-each wrapping around the OBX15 node

I also need access to to OBR data, so i create a variable that I can access in my for-each loop

  <xsl:template match="OBR_ObservationRequest">
    <xsl:variable name="Anchor" select="OBR_1_SetIdObr/text()"/>
    <xsl:variable name="OBR31" select="OBR_3_FillerOrderNumber/EI_0_EntityIdentifier/text()"/>

So now I want to map from my variable into the proper location:

<CE_1_Text>
  <xsl:value-of select="$OBR31"/>
</CE_1_Text>

 

Which now creates the following output:

    <OBX_15_ProducerSId>
      <CE_1_Text>B13573501L</CE_1_Text>
    </OBX_15_ProducerSId>
  </OBX_ObservationResult>
</ns1:ORU_R01_25_GLO_DEF>

Mapping HL7 Data

 Uncategorized  Comments Off
Feb 032015
 

So I wanted to show how to map an HL7 message.
Here is the schema view:

Here is a collapsed view of the data:

Now you can’t simply use the mapping functiods to map it straight across, the sequence groups ‘appear’ to indicate what you think is a structure, however, the resulting XML from the BTAHL7 pipeline component creates a ‘flat’ structure.

So we are going to create custom XSL and going to map straight across, input to output. So I created the map and drew lines across to get the initial xsl structure defined.

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                xmlns:msxsl="urn:schemas-microsoft-com:xslt" 
                xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" 
                exclude-result-prefixes="msxsl var" version="1.0" 
                xmlns:ns0="http://stottcreations.com/HealthCare/HL7/2X/2.5/Segments" 
                xmlns:ns2="http://stottcreations.com/HealthCare/HL7/2X/2.5/Tables" 
                xmlns:ns3="http://stottcreations.com/HealthCare/HL7/2X/2.5/DataTypes" 
                xmlns:ns1="http://stottcreations.com/HealthCare/HL7/2X">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:template match="/">
    <xsl:apply-templates select="/ns1:ORU_R01_25_GLO_DEF" />
  </xsl:template>
  <xsl:template match="/ns1:ORU_R01_25_GLO_DEF">
    <ns1:ORU_R01_25_GLO_DEF>
      <xsl:value-of select="./text()" />
    </ns1:ORU_R01_25_GLO_DEF>
  </xsl:template>
</xsl:stylesheet>

So we need to start mapping the OBR segment: so let’s create a template for it.

    <ns1:ORU_R01_25_GLO_DEF>
      <xsl:apply-templates select="OBR_ObservationRequest"/>
    </ns1:ORU_R01_25_GLO_DEF>

Now in the OBR template we need to set an achor (since it is a flat structure) of the OBR1SetIdObr/text())

  <xsl:template match="OBR_ObservationRequest">
    <xsl:variable name="Anchor" select="OBR_1_SetIdObr/text()"/>

Now we actually start creating the OBR segment

    <OBR_ObservationRequest>
      <xsl:copy-of select="./@*" />
      <xsl:copy-of select="./*" />
    </OBR_ObservationRequest>

Now, we need to map any NTE segment, following the OBR Segment

    <xsl:for-each select="following-sibling::NTE_NotesAndComments[preceding-sibling::OBR_ObservationRequest[1]/OBR_1_SetIdObr/text()=$Anchor]">
      <NTE_NotesAndComments>
        <xsl:copy-of select="./@*" />
        <xsl:copy-of select="./*" />
      </NTE_NotesAndComments>
    </xsl:for-each>

And we need to map any OBX segment following the OBR Segment also

    <xsl:for-each select="following-sibling::OBX_ObservationResult[preceding-sibling::OBR_ObservationRequest[1]/OBR_1_SetIdObr/text()=$Anchor]">
      <OBX_ObservationResult>
        <xsl:copy-of select="./@*" />
        <xsl:copy-of select="./*" />
      </OBX_ObservationResult>
    </xsl:for-each>

The entire xsl is here:

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                xmlns:msxsl="urn:schemas-microsoft-com:xslt" 
                xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" 
                exclude-result-prefixes="msxsl var" 
                version="1.0" xmlns:ns1="http://stottcreations.com/HealthCare/HL7/2X" 
                xmlns:ns0="http://stottcreations.com/HealthCare/HL7/2X/2.5/Segments">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" indent="yes" />
  <xsl:template match="/">
    <xsl:apply-templates select="/ns1:ORU_R01_25_GLO_DEF" />
  </xsl:template>
  <xsl:template match="/ns1:ORU_R01_25_GLO_DEF">
    <ns1:ORU_R01_25_GLO_DEF>
      <xsl:apply-templates select="OBR_ObservationRequest"/>
    </ns1:ORU_R01_25_GLO_DEF>
  </xsl:template>
  <xsl:template match="OBR_ObservationRequest">
    <xsl:variable name="Anchor" select="OBR_1_SetIdObr/text()"/>
    <OBR_ObservationRequest>
      <xsl:copy-of select="./@*" />
      <xsl:copy-of select="./*" />
    </OBR_ObservationRequest>
    <xsl:for-each select="following-sibling::NTE_NotesAndComments[preceding-sibling::OBR_ObservationRequest[1]/OBR_1_SetIdObr/text()=$Anchor]">
      <NTE_NotesAndComments>
        <xsl:copy-of select="./@*" />
        <xsl:copy-of select="./*" />
      </NTE_NotesAndComments>
    </xsl:for-each>
    <xsl:for-each select="following-sibling::OBX_ObservationResult[preceding-sibling::OBR_ObservationRequest[1]/OBR_1_SetIdObr/text()=$Anchor]">
      <OBX_ObservationResult>
        <xsl:copy-of select="./@*" />
        <xsl:copy-of select="./*" />
      </OBX_ObservationResult>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

Here is the result

There is a follow up post on doing a bit more complicated mapping logic in my Mapping HL7 Data Part 2

 

I am going to write a ‘visual’ example of the article documented here: Performing Composite Operations on Oracle Database by Using BizTalk Server

I have a schema already created from Oracle that defines all of the stored procedures we are planning on calling from within BizTalk

The issue is that I need to send multiple of the same calls to the stored procedure and multiple stored procedure calls.

Now we are going to create the composite Schema Definition.

Let’s import the original schema, and for whatever reason, I needed to add the ns0 to the prefix.

 

So let’s change the root node name to Request (like it asks us to in the instruction)

And start adding the things we want to the Request node (making them repeatable), however, unlike the instructions, I am not naming it, because once I choose the correct node, it is going to rename it anyway:

Make it min 0 and max * and we are done with the first one.

And do it for the rest of the stored proc calls.

Let’s create the Response and do the same thing, except choosing all of the Response nodes. Here is the final schema

 

Now you can start mapping…

Jan 232015
 

One of the biggest beef’s I have with the BizTalk mapper (other than functiods in general) is the ‘feature’ where, when you click on a source node, it will ‘highlight all of the links and center the destination to show you the links.

For small maps, this is ‘hardly’ noticeable.

However, for maps that are large, and the same input node is used in multiple destination nodes, it becomes hair pulling-ly frustrating.

To disable this, in Visual Studio go to Tools –> Options –> BizTalk Mapper and uncheck the two default checks.

You then need to restart Visual Studio (perhaps just close the currently opened Maps) for this to finally not scroll.

Options

 

I am sure I am the last to figure this out, so pardon me for my late entry to the game of XSL goodness.

A few years ago, I discovered the power of XSL, and now I never use any of the out of the box functiods, in fact, I am hard pressed to try to remember the last time I even used inline C# in my maps.

So here is how I have ALWAYS coded inline XSLT:

I need to create an output node

Destination

So, what I have always done is created the XSL like this:

<ns2:P_BATCH_ITEM_CNT>
<xsl:value-of select="count(/s1:Batch/s0:Form)" />
</ns2:P_BATCH_ITEM_CNT>

Which should work. However, when I either Validate the map or compile it, I get the following error:

MakeMessages.btm: error btm1023: Exception Caught: ‘ns2′ is an undeclared prefix. Line 1, position 2

So then I open up the map and go find the ns2 definition up at the top of the generated xsl

namespaces

Copy the namespace down into the script like this:

<ns2:P_BATCH_ITEM_CNT xmlns:ns2="http://Microsoft.LobServices.OracleDB/2007/03/BIZTALK/Procedure">
<xsl:value-of select="count(/s1:Batch/s0:Form)" />
</ns2:P_BATCH_ITEM_CNT>

However, there is an ‘easier’ way to do this:

Use the xsl:element command, this way you don’t have to ‘care’ about declaring the namespace in all of your custom XSL scripts

<xsl:element name="ns2:P_BATCH_ITEM_CNT">
<xsl:value-of select="count(/s1:Batch/s0:Form)" />
</xsl:element>

Jan 222015
 

So I just was notified that the StackExchange Redis NuGet package was upgraded, so here is my experiences:

There isn’t anything on the site’s home page that says anything about upgrading, so it must be easy!

I opened up my solution and in the NuGet console, I pointed to my project that uses Redis and put the command to get the latest Strong Name in:

image

And here are the results

image

Just as I thought: EASY

Nov 142014
 

So I learned a bit more of the importance of the places you need to name what when setting up database facts in the BRE.

The table name is especially important. Within the BRC, you don’t have much flexibility in what you can edit, but when setting up either a long or short term fact, the names are extremely important, well, they have to match, or it simply states that the TypedDataRow is un resolvable.

Here is the link between what is setup in the BRC and how it correlates to setting up a table fact in code:

image

Oct 292014
 

So we at Stott Creations often get requests to ensure that data is valid flowing through the BizTalk.

The out of the box functionality is pretty straight forward, simply turn on XSD validation in the XML pipelines:

image

The sucky part about that is that it will only surface the first error. Now if your data is really bad, I have no desire to send the data in multiple times to get all of the errors.

What we have done is create a product that creates a comprehensive list of errors so you can send the entire list of errors.

So the first thing we have is a list of validation functions that will serve us to create the list of rules to validate the data against.

image

The second set of vocabulary items is the validation patterns:

image

To setup the actual policy, we need to first setup the ability to Assert the list to the Rule engine. I create the rule as 1 Assert (so it shows up at the top of the list). I drag the run into the conditions pane. I right click the actions and choose assert and then drag List Enumerator into the fact. I also want this to run first, so I set the priority to 2.

image

Now I need to advance the Enumeration. I create 2 Advance (to sit below the 1 Assert). I drag the IEnumerator MoveNext into the Conditions pane. I then right click the actions and choose Assert and drag in the Current IEnumerator into the fact. I then right click the Actions and add update and drag IEnumerator into the fact. Because I want this to run second, I set the priority to 1.

image

Okay, so now we are ready to start creating validation rules. Each rule will run independent of each other, and I really don’t care which order it runs in, I just need them all to run. Each rule that I create in this policy is going to have a priority of 0 (which is the default). Let’s create a rule that checks a format.

I am going to check for two things:

  1. Check the XPath to see if the rule is going to be valid
  2. Check the format to see if it is going to be valid

image

Right click and choose Equal and drag in XPath Statement from the vocabulary

image

I then go to Visual Studio and open up the schema and choose the element or attribute I am attempting to check and copy the xpath statement.

image

And paste it in the right side of the rule

image

Then I drag the Check Format into the AND

image

I then drag Text Value into the first <empty string> slot

image

Now I go to the other Vocabulary and choose the date format I care to validate against, in this case Date with optional century indicator MMDDYYYY and I drag it into the second <empty string> slot. I then have two items, the first one is the regular expression value and the second one is the friendly version. I want to choose the regular expression.

image

The next part is if this is true, meaning, the xpath value is true, and it doesn’t match the regular expression check, I want to create an error. I drag the Create Format Error (Manual) into the actions

image

Now I need to fill in the error information: I need to supply the node type, which is either an attribute or element, I drag Node Type into the <enter a value> section and Node Name into the <empty string>

image

I then drag Text Item into the next <empty string> element, Line Number, and Position into the next two 0 places

image

I then drag from the Patterns vocabulary the Date with optional century indicator MMDDYYYY into the last <empty string> place holder, this time I choose the human readable description (because really, who understands Regular Expressions?)

image

I can continue creating rules this way.

However, there is an easier way:

I create the next rule, creating the If pane the same way, this time I am going to check the SSN. In the THEN, I drag in Create Format Error (Automatic)

image 

I then drag in Validation Information into the <null> (which has all of the details I need (Attribute/Element, node name, text, position, line number, etc)

image

And then drag the Pattern in the <empty string> and choose the friendly explanation.

image

There is tests for data lengths, min, max, and length ranges.

Now to execute it.

First we will show how to do it in .NET

Adding a reference to StottCreations.Validation in the GAC. I then create the following code

  1. I load an XML document
  2. I instantiate a new Record and pass in the xml document
  3. I set the facts (only one) to the Record.
  4. Because I might want to see the trace I new up the DebugTrackingInterceptor
  5. I setup the policy calling the Validation policy I just created.
  6. I then execute the policy.
  7. I also need to clear and dispose the policy (because we have found that the automated garbage collection within .NET is not fast enough).

image

 

When I run the test, I can look at the record, and this is what I see:

image

If I run a valid xml document through, this is the results

image

I can do the same for an orchestration:

image

In the Initialize Variables shape is the following code, again we don’t have the Call Rules shape because we need to dispose immediately.

image

and then in the decision shape (Good)

image

and in the Terminate shape:

image

© 2015 BizTalk Blog Suffusion theme by Sayontan Sinha