Dealing with namespaces and sql:variables in TSQL

 Uncategorized  Comments Off on Dealing with namespaces and sql:variables in TSQL
Feb 202015
 

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 on XSL Tricks
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>

Mapping HL7 Data Part 2

 Uncategorized  Comments Off on Mapping HL7 Data Part 2
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 on Mapping HL7 Data
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