Eric's BizTalk 2004/2006/2009 blog

Getting time from datetime in TSQL

Eric Stott — Fri, 19 Mar 2010 18:14:03 GMT

As everyone already knows, to extract the time from a datetime (pre SQL 2008), you have to use the convert function

HOWEVER: beware of using the smalldatetime type, as it trucates the seconds from the value convert returns

Run the following code:

declare @thissmalldatetime smalldatetime,@thisdatetime datetime
select @thissmalldatetime=getdate(),@thisdatetime=getdate()
select convert(nvarchar(8),@thissmalldatetime,14) as [Small Date Time],convert(nvarchar(8),@thisdatetime,14) as [Date Time]
waitfor delay '00:00:01'
select @thissmalldatetime=getdate(),@thisdatetime=getdate()
select convert(nvarchar(8),@thissmalldatetime,14) as [Small Date Time],convert(nvarchar(8),@thisdatetime,14) as [Date Time]

Small Date Time Date Time
--------------- ---------
13:13:00        13:12:40

(1 row(s) affected)

Small Date Time Date Time
--------------- ---------
13:13:00        13:12:41

(1 row(s) affected)

Fast way to count rows in a table

Eric Stott — Mon, 22 Feb 2010 23:27:51 GMT

I have had to look this query up a few times, so I thought I would write about it here:

USE [DBInstanceName]
GO
SELECT  st.row_count as [Row Count]
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
and OBJECT_NAME(OBJECT_ID)='TableName'

Bulk XML Load Tutorial

Eric Stott — Wed, 10 Feb 2010 07:12:30 GMT

Since I have had to explain how to use the BulkXML adapter a few times, I decided to create a quick tutorial on how to use it. Also, this is a tutorial for me to follow, since every time I do this, I re-learn the same things over and over.

The first thing is we are going to create the tables in the database with the relationships. You can get a jump start by downloading this script:

You will have a table structure like this:

and an xml file that looks like this

<ns0:File xmlns:ns0="http://BulkXMLSample.Input">
  <FamilyRecord>
    <Name>Stott</Name>
    <Address>100 N 100 W</Address>
    <City>Logan</City>
    <State>UT</State>
    <Zip>84321</Zip>
    <Child><Name>Bob</Name><Sex>M</Sex></Child>
    <Child><Name>Susan</Name><Sex>F</Sex></Child>
    <Child><Name>Mary</Name><Sex>F</Sex></Child>
    <Child><Name>Jane</Name><Sex>F</Sex></Child>
    <Child><Name>Jeb</Name><Sex>M</Sex></Child>
  </FamilyRecord>
  <FamilyRecord>
    <Name>Dahl</Name>
    <Address>45 Polk Ave</Address>
    <City>Blaine</City>
    <State>MN</State>
    <Zip>54321</Zip>
    <Child><Name>Jason</Name><Sex>M</Sex></Child>
  </FamilyRecord>
  <FamilyRecord>
    <Name>Matthew</Name>
    <Address>232 Acadia Ave</Address>
    <City>St. Louis</City>
    <State>MO</State>
    <Zip>78223</Zip>
    <Child><Name>William</Name><Sex>M</Sex></Child>
    <Child><Name>Jennifer</Name><Sex>F</Sex>
    </Child>
  </FamilyRecord>
</ns0:File>

The next thing is to create the input and output schema

and the map

Now we need to create the ‘mapping’ schema that maps the xml data that BizTalk creates and maps it into the database table.

To speed up the creation of the mapping download the following file and place it in the schema directory of Visual Studio (%InstallRoot%\Xml\Schemas):

Let’s open up the output schema using the XML Editor:

Make the following changes to the output schema:

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns="http://BulkXMLSample.Output" targetNamespace="http://BulkXMLSample.Output" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <!--Add xmlns:sql="urn:schemas-microsoft-com:mapping-schema" to xs:schema element-->
  <xs:annotation>
    <xs:appinfo>
      <b:schemaInfo root_reference="File" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" />
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="File" sql:relation="Header" sql:key-fields="fileid">
    <!--We want to 'map' the element File to the table Header, since they are different we have to use the sql:relation-->
    <!--We also need to tell which is the primary key column-->
    <xs:complexType>
      <xs:sequence>
        <xs:element name="FileName" type="xs:string" />
        <xs:element name="Date" type="xs:string" />
        <xs:element maxOccurs="unbounded" name="FamilyRecord" sql:relation="Family" sql:key-fields="FamilyId">
          <!--Now we need to start defining how this and its parent are related-->
          <xs:annotation>
            <xs:appinfo>
              <sql:relationship parent="Header" parent-key="fileid" child-key="HeaderId" child="Family" />
              <!--The parent and parent-key are defined in the parent table and child and child-key are the columns in the current table-->
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Name" type="xs:string" />
              <xs:element name="Address" type="xs:string" />
              <xs:element name="City" type="xs:string" />
              <xs:element name="State" type="xs:string" />
              <xs:element name="Zip" type="xs:string" />
              <xs:element maxOccurs="unbounded" name="Child" sql:relation="Child" sql:key-fields="ChildId">
                <!--Added the sql:relation and sql:key-fields annotations-->
                <xs:annotation>
                  <xs:appinfo>
                    <sql:relationship parent="Family" parent-key="FamilyId" child="Child" child-key="FamilyId" />
                  </xs:appinfo>
                </xs:annotation>
                <!--I added the entire xs:annotation section-->
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="Name" type="xs:string" sql:field="ChildName" />
                    <!--Added the sql:field since it was different-->
                    <xs:element name="Sex" type="xs:string" sql:field="Gender"/>
                    <!--Added the sql:field since it was different-->
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Now lets save a copy of this output schema in the send handler folder

The way the adapter knows which schema to use is it starts 7 characters into the target namespace and replaces # with _ and adds .xsd, so our http://targetnamespace#rootnode is http://BulkXMLSample.Output#File so it becomes BulkXMLSample.Output_File.xsd

In the orchestration, in the construct shape I have a message assignment shape that has the following code:

xpath(OutputMsg.MessagePart,"/*[local-name()='File' and namespace-uri()='http://BulkXMLSample.Output']/*[local-name()='FileName' and namespace-uri()='']")=System.IO.Path.GetFileName(InputMsg(FILE.ReceivedFileName));

I deploy it and set the send port to point to the database:

Bind it and run a file through:

I look in the event log and see this:

Event Type:    Error
Event Source:    SQLBulkXML
Event Category:    None
Event ID:    0
Date:        2/9/2010
Time:        9:52:27 PM
User:        N/A
Computer:
Description:
The column 'Date' was defined in the schema, but does not exist in the database.
   at SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class.Execute(String bstrSchemaFile, Object vDataFile)
   at StottIS.BulkLoad.BulkLoadXML.ThreadProc()
SQLXMLBulkLoad4Class error: The column 'Date' was defined in the schema, but does not exist in the database.

The Visual Basic Script to test this message is here:C:\Users\Administrator\AppData\Local\Temp\1e0e7b04-92e1-4f39-a591-2bdedef93a8f.vbs

So without having to run data through again, I can go and look at the data that was attempting to be inserted into the database and run it manually.

I have one of the elements mapped incorrectly to the column, so I modify the schema:

        <xs:element name="Date" type="xs:string" sql:field="ImportDate" />
        <!--I need to map the Date element into the ImportDate column, I use the sql:field attribute to do it-->
        <xs:element maxOccurs="unbounded" name="FamilyRecord" sql:relation="Family" sql:key-fields="FamilyId">

If you run the vbs script and you experience this error

<?xml version="1.0"?>
<Result State="FAILED">
 <Error>
  <HResult>0x80004005</HResult>
  <Description>
   <![CDATA[No data was provided for column '{Primary Key Column}' on table '{Table}', and this column cannot contain NULL values.]]>
  </Description>
  <Source>General operational error</Source>
  <Type>FATAL</Type>
 </Error>
</Result>

Add this line to the vbs file

objBL.KeepIdentity=FALSE

After that I ran the vbs file, I am successful.

I can open up the tables:

Now that I have perfected the annotations to the mapping schema, I can run it through BizTalk without error.

To look at the final schema that is used by the bulkload adapter here:

Here is the sample data:

If you want to change the vbs script, here are the things you can change in the Bulk Load Object Model
Here are more definitions for the Bulk Load Annotations

Optimizing XML Queries in SQL Server

Eric Stott — Tue, 02 Feb 2010 10:12:39 GMT

In creating a process where SQL Server takes XML data that is stored in a table and populates a table, I have learned a few things on how to get SQL Server to more efficiently query XML data.

Setup:

Let’s first create a table to store the data:

CREATE TABLE dbo.XMLDataStore(
  link nvarchar(100) NULL,
  data xml NULL
) ON [PRIMARY]
GO

Let’s take a look at the XML that we are going to query (yes I know is is a large xml document, but I wanted to show the peformance in a real world situation, not a 3 node xml document that is normally demonstrated)

Now let’s insert it into the table:

INSERT INTO [XMLTutorial].[dbo].[XMLDataStore]
           ([link]
           ,[data])
     VALUES
           ('ABCDEFGHIJ'
           ,'<ns0:ORU_R01_231_GLO_DEF xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:ns0="http://labratory/DB/2X">
               ...
            </ns0:ORU_R01_231_GLO_DEF>')
GO

The first query starts at the Observation record (all 64 records) and traverses the xml document and creates the necessary columns in 16 seconds.

select  Observation.ref.value('((../../Patient/PID_PatientIdentificationSegment/PID_5_PatientName/XPN_1_GivenName/text())[1])','nvarchar(100)') as FirstName,
    Observation.ref.value('((../../Patient/PID_PatientIdentificationSegment/PID_5_PatientName/XPN_0_FamilyLastName/XPN_0_0_FamilyName/text())[1])','nvarchar(100)') as LastName,
    Observation.ref.value('((../../Patient/PID_PatientIdentificationSegment/PID_7_DateTimeOfBirth/TS_0_TimeOfAnEvent/text())[1])','nvarchar(100)') as BirthDate,
    Observation.ref.value('((../../Patient/PID_PatientIdentificationSegment/PID_2_PatientId/CX_0_Id/text())[1])','nvarchar(100)') as InsuranceNumber,
    Observation.ref.value('((../OBR_ObservationRequestSegment/OBR_1_SetIdObr/text())[1])','nvarchar(100)')as [OBRID],
    Observation.ref.value('((../OBR_ObservationRequestSegment/OBR_7_ObservationDateTime/TS_0_TimeOfAnEvent/text())[1])','nvarchar(10)') as ObservationDate,    
    Observation.ref.value('((../OBR_ObservationRequestSegment/OBR_4_UniversalServiceId/CE_1_Text/text())[1])','nvarchar(100)') as LabTestName,
    null as LabTestCode,
    Observation.ref.value('((./OBX_ObservationResultSegment/OBX_1_SetIdObx/text())[1])','nvarchar(100)') as [OBXID],
    Observation.ref.value('((./OBX_ObservationResultSegment/OBX_3_ObservationIdentifier/CE_4_AlternateText/text())[1])','nvarchar(100)') as LabResultName,
    Observation.ref.value('((./OBX_ObservationResultSegment/OBX_3_ObservationIdentifier/CE_0_Identifier/text())[1])','nvarchar(100)') as LabResultCode,
    Observation.ref.value('((./OBR_5_PriorityObr/text())[1])','nvarchar(100)') as [Priority],
    Observation.ref.value('((./OBX_ObservationResultSegment/OBX_14_DateTimeOfTheObservation/TS_0_TimeOfAnEvent/text())[1])','nvarchar(100)') as [ResultDate],
    Observation.ref.value('((./OBX_ObservationResultSegment/OBX_5_ObservationValue/CE_4_AlternateText/text())[1])','nvarchar(100)') as [ResultValue],
    Observation.ref.value('((./OBX_ObservationResultSegment/OBX_6_Units/CE_0_Identifier/text())[1])','nvarchar(100)') as [UnitOfMeasure],
    Observation.ref.value('((./OBX_ObservationResultSegment/OBX_7_ReferencesRange/text())[1])','nvarchar(100)') as [ReferenceRange]
from XMLDataStore x cross apply x.data.nodes('//Observation') Observation(ref)
where x.link='ABCDEFGHIJ'

The first optimization step is instead of using the Observation node and deriving all of the other columns from that, you can further use CROSS APPLY to create separate nodes to extract data from coupled with not using wild cards and diving directly to the exact node I need to. I went to the OBX_ObservationResultSegment as the originating node, and then from that node (named Observation), I derived two other nodes to reference in the query; Patient and Request.

This time the query completed in 4 seconds:

WITH XMLNAMESPACES ('http://labratory/DB/2X' AS "ns0")
select  Patient.node.value('(PID_5_PatientName/XPN_1_GivenName/text())[1]','nvarchar(100)') as FirstName,
    Patient.node.value('(PID_5_PatientName/XPN_0_FamilyLastName/XPN_0_0_FamilyName/text())[1]','nvarchar(100)') as LastName,
    Patient.node.value('(PID_7_DateTimeOfBirth/TS_0_TimeOfAnEvent/text())[1]','nvarchar(100)') as BirthDate,
    Patient.node.value('(PID_2_PatientId/CX_0_Id/text())[1]','nvarchar(100)') as InsuranceNumber,
    Request.node.value('(OBR_1_SetIdObr/text())[1]','nvarchar(100)')as [OBRID],
    Request.node.value('(OBR_7_ObservationDateTime/TS_0_TimeOfAnEvent/text())[1]','nvarchar(10)') as ObservationDate,    
    Request.node.value('(OBR_4_UniversalServiceId/CE_1_Text/text())[1]','nvarchar(100)') as LabTestName,
    null as LabTestCode,
    Observation.node.value('(OBX_1_SetIdObx/text())[1]','nvarchar(100)') as [OBXID],
    Observation.node.value('(OBX_3_ObservationIdentifier/CE_4_AlternateText/text())[1]','nvarchar(100)') as LabResultName,
    Observation.node.value('(OBX_3_ObservationIdentifier/CE_0_Identifier/text())[1]','nvarchar(100)') as LabResultCode,
    Observation.node.value('(OBR_5_PriorityObr/text())[1]','nvarchar(100)') as [Priority],
    Observation.node.value('(OBX_14_DateTimeOfTheObservation/TS_0_TimeOfAnEvent/text())[1]','nvarchar(100)') as [ResultDate],
    Observation.node.value('(OBX_5_ObservationValue/CE_4_AlternateText/text())[1]','nvarchar(100)') as [ResultValue],
    Observation.node.value('(OBX_6_Units/CE_0_Identifier/text())[1]','nvarchar(100)') as [UnitOfMeasure],
    Observation.node.value('(OBX_7_ReferencesRange/text())[1]','nvarchar(100)') as [ReferenceRange]
from XMLDataStore x 
cross apply x.data.nodes('ns0:ORU_R01_231_GLO_DEF/CompleteOrder/Order/Observation/OBX_ObservationResultSegment') Observation(node)
cross apply Observation.node.nodes('../../../Patient/PID_PatientIdentificationSegment') Patient(node)
cross apply Observation.node.nodes('../../OBR_ObservationRequestSegment') Request(node)
where x.link='ABCDEFGHIJ'

Never being satisfied, let’s add an index to the data. However, to add an xml index to the data, we need to create a clustered index on the table. If we simply try to add an xml index to the current table with this command:

CREATE PRIMARY XML INDEX PrimaryXMLIndex ON
dbo.XMLDataStore(data)
GO

We get the following error:

Msg 6332, Level 16, State 201, Line 1
Table 'dbo.XMLDataStore' needs to have a clustered primary key with less than 16 columns in it in order to create a primary XML index on it.

Not descriptive, so let’s create a new table:

CREATE TABLE dbo.OptimizedXMLDataStore(
  id INT IDENTITY PRIMARY KEY,
  link nvarchar(100) NOT NULL,
  data xml NOT NULL
) ON [PRIMARY]
GO

And creating the following indexes in the database:

CREATE PRIMARY XML INDEX PrimaryXMLIndex ON
dbo.OptimizedXMLDataStore(data)
GO
CREATE XML INDEX
XMLDataStore_XmlCol_PATH ON dbo.OptimizedXMLDataStore(data) 
USING XML INDEX PrimaryXMLIndex FOR PATH
GO

Now that the indexes are created, let’s insert the data into this table:

INSERT INTO [XMLTutorial].[dbo].[OptimizedXMLDataStore]
           ([link]
           ,[data])
     VALUES
           ('ABCDEFGHIJ'
           ,'<ns0:ORU_R01_231_GLO_DEF xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:ns0="http://labratory/DB/2X">
               ...
            </ns0:ORU_R01_231_GLO_DEF>')
GO

Now let’s run the same query (except pointing to the indexed table):

WITH XMLNAMESPACES ('http://labratory/DB/2X' AS "ns0")
select  Patient.node.value('(PID_5_PatientName/XPN_1_GivenName/text())[1]','nvarchar(100)') as FirstName,
    Patient.node.value('(PID_5_PatientName/XPN_0_FamilyLastName/XPN_0_0_FamilyName/text())[1]','nvarchar(100)') as LastName,
    Patient.node.value('(PID_7_DateTimeOfBirth/TS_0_TimeOfAnEvent/text())[1]','nvarchar(100)') as BirthDate,
    Patient.node.value('(PID_2_PatientId/CX_0_Id/text())[1]','nvarchar(100)') as InsuranceNumber,
    Request.node.value('(OBR_1_SetIdObr/text())[1]','nvarchar(100)')as [OBRID],
    Request.node.value('(OBR_7_ObservationDateTime/TS_0_TimeOfAnEvent/text())[1]','nvarchar(10)') as ObservationDate,    
    Request.node.value('(OBR_4_UniversalServiceId/CE_1_Text/text())[1]','nvarchar(100)') as LabTestName,
    null as LabTestCode,
    Observation.node.value('(OBX_1_SetIdObx/text())[1]','nvarchar(100)') as [OBXID],
    Observation.node.value('(OBX_3_ObservationIdentifier/CE_4_AlternateText/text())[1]','nvarchar(100)') as LabResultName,
    Observation.node.value('(OBX_3_ObservationIdentifier/CE_0_Identifier/text())[1]','nvarchar(100)') as LabResultCode,
    Observation.node.value('(OBR_5_PriorityObr/text())[1]','nvarchar(100)') as [Priority],
    Observation.node.value('(OBX_14_DateTimeOfTheObservation/TS_0_TimeOfAnEvent/text())[1]','nvarchar(100)') as [ResultDate],
    Observation.node.value('(OBX_5_ObservationValue/CE_4_AlternateText/text())[1]','nvarchar(100)') as [ResultValue],
    Observation.node.value('(OBX_6_Units/CE_0_Identifier/text())[1]','nvarchar(100)') as [UnitOfMeasure],
    Observation.node.value('(OBX_7_ReferencesRange/text())[1]','nvarchar(100)') as [ReferenceRange]
from OptimizedXMLDataStore x 
cross apply x.data.nodes('ns0:ORU_R01_231_GLO_DEF/CompleteOrder/Order/Observation/OBX_ObservationResultSegment') Observation(node)
cross apply Observation.node.nodes('../../../Patient/PID_PatientIdentificationSegment') Patient(node)
cross apply Observation.node.nodes('../../OBR_ObservationRequestSegment') Request(node)
where x.link='ABCDEFGHIJ'

The results came back in 0 seconds

Things I did not do:

Actually see Clark Kent (I think he was born before June of 1938, but it was the first time he was writtent about)
Question why the “The Last Son of Krypton” was actually getting lab work done
Imported schemas into the database

HIPAA 5010 support now officially available for BizTalk

Eric Stott — Fri, 29 Jan 2010 23:03:12 GMT

The HIPAA 5010 Support KB Article 973415 explains the new ability within BizTalk to support the next version of HIPAA standards for EDI data interchange.

Note: as noted in the Applies To section at the bottom of the article, this only works with BizTalk 2009, sorry BizTalk 2006 R2 users, looks like you will be migrating to 2009 sooner than you thought.

This also fixes an issue where the claim separation mechanism (multiple schema) was not working.

Bubble up searching within a BizTalk map

Eric Stott — Tue, 26 Jan 2010 05:42:32 GMT

A recent client had a high profile issue that took some interesting mapping to get working. I thought that I would write about it, as it is the second client that I have come across that has needed it. Since it has happened twice, it warrants a blog entry.

Requirement: I don’t know where a particular number is going to show up, but I need to find it and place it in this particular column in the output.

Specifically: I know that the Medicaid or Medicare number is going to show up in 1 of 4 places, I need to look for it and where ever it is, I need to place it in these two places in the output.

It is going to show up in the REF02 where the REF01 is 1C (Medicare) and 1D (Medicaid) in the following locations: 2420A, 2310B, 2010BA, 2010AA. I want to look first in the 2420A level, if it is not there, then I want to search the 2310B level, 2010BA, and finally the 2010AA and since there might (most likely) be multiple REF segments, I need it to search all of the REF segments (not just the first one).

Here is a few snippets of the xml where the data can reside:

              <ns0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A>
                <REF01__ReferenceIdentificationQualifier>1D</REF01__ReferenceIdentificationQualifier>
                <REF02__RenderingProviderSecondaryIdentifier>2420A Medicaid</REF02__RenderingProviderSecondaryIdentifier>
              </ns0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A>
              <ns0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A>
                <REF01__ReferenceIdentificationQualifier>1C</REF01__ReferenceIdentificationQualifier>
                <REF02__RenderingProviderSecondaryIdentifier>2420A Medicare</REF02__RenderingProviderSecondaryIdentifier>
              </ns0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A>

and

            <ns0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B>
              <REF01__ReferenceIdentificationQualifier>1D</REF01__ReferenceIdentificationQualifier>
              <REF02__RenderingProviderSecondaryIdentifier>2310B Medicaid</REF02__RenderingProviderSecondaryIdentifier>
            </ns0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B>
            <ns0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B>
              <REF01__ReferenceIdentificationQualifier>1C</REF01__ReferenceIdentificationQualifier>
              <REF02__RenderingProviderSecondaryIdentifier>2310B Medicare</REF02__RenderingProviderSecondaryIdentifier>
            </ns0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B>

and

        <ns0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB>
          <REF01__ReferenceIdentificationQualifier>1D</REF01__ReferenceIdentificationQualifier>
          <REF02__PaytoProviderIdentifier>2010AB Medicaid</REF02__PaytoProviderIdentifier>
        </ns0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB>
        <ns0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB>
          <REF01__ReferenceIdentificationQualifier>1C</REF01__ReferenceIdentificationQualifier>
          <REF02__PaytoProviderIdentifier>2010AB Medicare</REF02__PaytoProviderIdentifier>
        </ns0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB>

and

          <ns0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA_Loop>
            <ns0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA>
              <REF01__ReferenceIdentificationQualifier>1D</REF01__ReferenceIdentificationQualifier>
              <REF02__BillingProviderAdditionalIdentifier>2010AA Medicaid</REF02__BillingProviderAdditionalIdentifier>
            </ns0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA>
            <ns0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA>
              <REF01__ReferenceIdentificationQualifier>1C</REF01__ReferenceIdentificationQualifier>
              <REF02__BillingProviderAdditionalIdentifier>2010AA Medicare</REF02__BillingProviderAdditionalIdentifier>
            </ns0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA>
          </ns0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA_Loop>

So for the purposes of this blog entry I have created an output schema that creates an output row for each service line:

I first create some mapping so we can ensure that the map is creating the output correctly.

Which creates this (so far so good):

<ns0:Root xmlns:ns0="http://BubbleUp.Output">
  <ServiceLine ChargeAmt="914"></ServiceLine>
  <ServiceLine ChargeAmt="5299"></ServiceLine>
</ns0:Root>

Now we get into the ‘real work’

What we need to do is make the map query various portions of the xml document and because I am lazy, lets have the mapper do the heavy lifting for us:

Lets drag the REF02 from the 2420A loop into the MedicaidId and lets look at the XSL:

and the resulting xsl is:

<xsl:attribute name="MedicaidId">
  <xsl:value-of select="s0:TS837Q2_2420_SubLoop/s0:TS837Q2_2420A_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A/REF02__RenderingProviderSecondaryIdentifier/text()" />
</xsl:attribute>

and since we are going to want it from the other loops, let’s get the other REF segments in xsl:

<xsl:attribute name="MedicaidId">
  <xsl:value-of select="../s0:TS837Q2_2310_SubLoop/s0:TS837Q2_2310B_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B/REF02__RenderingProviderSecondaryIdentifier/text()" />
</xsl:attribute>

<xsl:attribute name="MedicaidId">
  <xsl:value-of select="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AB_Loop/s0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB/REF02__PaytoProviderIdentifier/text()" />
</xsl:attribute>

<xsl:attribute name="MedicaidId">
  <xsl:value-of select="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AA_Loop/s0:REF_TS837Q2_2010AA_SubLoop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA_Loop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA/REF02__BillingProviderAdditionalIdentifier/text()" />
</xsl:attribute>

Now we need to write the ‘bubble up’ logic:

The first thing is we need to query the correct REF01 qualifier

<xsl:value-of select="s0:TS837Q2_2420_SubLoop/s0:TS837Q2_2420A_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A[REF01__ReferenceIdentificationQualifier/text()='1D']/REF02__RenderingProviderSecondaryIdentifier/text()" />

and the other values in the other segments the same logic.

After that we merge all of these queries into a variable in a choose function:

<!--Lets create the Mediciad ID variable-->
<xsl:variable name="medicaidID">
  <xsl:choose>
    <xsl:when test="s0:TS837Q2_2420_SubLoop/s0:TS837Q2_2420A_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A/REF01__ReferenceIdentificationQualifier/text()='1D'">
      <xsl:value-of select="s0:TS837Q2_2420_SubLoop/s0:TS837Q2_2420A_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A[REF01__ReferenceIdentificationQualifier/text()='1D']/REF02__RenderingProviderSecondaryIdentifier/text()" />
    </xsl:when>
    <xsl:when test="../s0:TS837Q2_2310_SubLoop/s0:TS837Q2_2310B_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B/REF01__ReferenceIdentificationQualifier/text()='1D'">
      <xsl:value-of select="../s0:TS837Q2_2310_SubLoop/s0:TS837Q2_2310B_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B[REF01__ReferenceIdentificationQualifier/text()='1D']/REF02__RenderingProviderSecondaryIdentifier/text()" />
    </xsl:when>
    <xsl:when test="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AB_Loop/s0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB/REF01__ReferenceIdentificationQualifier/text()='1D'">
      <xsl:value-of select="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AB_Loop/s0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB[REF01__ReferenceIdentificationQualifier/text()='1D']/REF02__PaytoProviderIdentifier/text()" />
    </xsl:when>
    <xsl:when test="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AA_Loop/s0:REF_TS837Q2_2010AA_SubLoop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA_Loop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA/REF01__ReferenceIdentificationQualifier/text()='1D'">
      <xsl:value-of select="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AA_Loop/s0:REF_TS837Q2_2010AA_SubLoop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA_Loop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA[REF01__ReferenceIdentificationQualifier/text()='1D']/REF02__BillingProviderAdditionalIdentifier/text()" />
    </xsl:when>
    <xsl:otherwise>
      <xsl:value-of select="'Mediciad Id Not found'" />
    </xsl:otherwise>
  </xsl:choose>
</xsl:variable>

Now lets take the result of the result of the choose function and populate the attribute:

<!--Now that we have found it, lets put the value as the attribute-->
<xsl:attribute name="MedicaidId">
  <xsl:value-of select="$medicaidID" />
</xsl:attribute>

So the last step in this is to implement the code in the map, because like all of the maps I have ever worked in, I already have logic I have created, I just need to enhance what I already have there. I copy the above xsl into a scripting (inline xslt) functiod and attach it to the MediaidId attribute:

Now I need to find the MedicareId, so I simply replace the ID with 1C and change the variable name and attribute name I would have the following code:

<xsl:variable name="medicareID">
  <xsl:choose>
    <xsl:when test="s0:TS837Q2_2420_SubLoop/s0:TS837Q2_2420A_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A/REF01__ReferenceIdentificationQualifier/text()='1C'">
      <xsl:value-of select="s0:TS837Q2_2420_SubLoop/s0:TS837Q2_2420A_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A[REF01__ReferenceIdentificationQualifier/text()='1C']/REF02__RenderingProviderSecondaryIdentifier/text()" />
    </xsl:when>
    <xsl:when test="../s0:TS837Q2_2310_SubLoop/s0:TS837Q2_2310B_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B/REF01__ReferenceIdentificationQualifier/text()='1C'">
      <xsl:value-of select="../s0:TS837Q2_2310_SubLoop/s0:TS837Q2_2310B_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B[REF01__ReferenceIdentificationQualifier/text()='1C']/REF02__RenderingProviderSecondaryIdentifier/text()" />
    </xsl:when>
    <xsl:when test="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AB_Loop/s0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB/REF01__ReferenceIdentificationQualifier/text()='1C'">
      <xsl:value-of select="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AB_Loop/s0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB[REF01__ReferenceIdentificationQualifier/text()='1C']/REF02__PaytoProviderIdentifier/text()" />
    </xsl:when>
    <xsl:when test="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AA_Loop/s0:REF_TS837Q2_2010AA_SubLoop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA_Loop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA/REF01__ReferenceIdentificationQualifier/text()='1C'">
      <xsl:value-of select="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AA_Loop/s0:REF_TS837Q2_2010AA_SubLoop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA_Loop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA[REF01__ReferenceIdentificationQualifier/text()='1C']/REF02__BillingProviderAdditionalIdentifier/text()" />
    </xsl:when>
    <xsl:otherwise>
      <xsl:value-of select="'Medicare Id Not found'" />
    </xsl:otherwise>
  </xsl:choose>
</xsl:variable>
<xsl:attribute name="MedicareId">
  <xsl:value-of select="$medicareID" />
</xsl:attribute>

Put it into a scripting functiod and you would have the following map:

The output looks like this:

<ns0:Root xmlns:ns0="http://BubbleUp.Output">
  <ServiceLine ChargeAmt="914" MedicaidId="2420A Medicaid" MedicareId="2420A Medicare"></ServiceLine>
  <ServiceLine ChargeAmt="5299" MedicaidId="2310B Medicaid" MedicareId="2010AB Medicare"></ServiceLine>
</ns0:Root>

As a bonus: the client wanted to sum up Deductible and CoInsurance amounts from the CAS segment where the qualifier was 1 and 2 respectively, so the following example has that xsl included in it.

Here is the solution (BizTalk 2009 solution only: please don’t ask for 2006, as I don’t have time to make a version for that!)

What I would look for in a BizTalk Healthcare class/Healthcare book for BizTalk

Eric Stott — Tue, 15 Dec 2009 12:12:15 GMT

I have been thinking of BizTalk healthcare classes or writing a book for BizTalk in relation to healthcare, and I thought that I would put the things that I would look for if I was going to pay for a class or heaven forbid; buy a morbid book.

1. The teacher/writer has to have healthcare experience.

2. I want to have a brief understanding of the ‘flow’ of document in a healthcare situation:

     837 (Claims) are submitted to a health plan for payment
     997 (Acknowledgments) are returned to know if they pass Type 1 and Type 2 validation
     824 (Application Notification) can be returned for Type 3 through Type 7 validation, depending on the capability of the backend system
     835 (Claim Status) are sent back to notify of Claim status
     270 (Eligibility Request) are sent to health plan to ask for status of patient eligibility for service
     271 (Eligibility Response) are sent from health plan in response to patient eligibility request
     276 (Claim Status Request) are sent to health plan to as for status of claim
     277 (Claim Status Response) are sent from health plan to in response of claim request
     820 (Payment Notification) are sent from the health plan to notify of payment of the claims
     834 (Eligibility) are sent for notification of eligibility

3. I want to map a 837, as that will be the transaction I will most deal with, spend a majority of the the time mapping the 837 transaction if I have to, because that is my biggest pain point.

4. Explain HL7, where it is used

Provider transactions, largely used in intra department messaging

5. Create a sample ORU^R01 message from a flat file and create a flat file from a ORU^R01 message so I can learn the following HL7 nuances:

    Schema Generation (partner specific schema creation)
    Multipart Messages
    MSH Segment
    BodySegments
    ZSegments
    How to map the flat XML message that the BTAHL7 pipeline (DASM) creates / (ASM) expects
    Explain MLLP, integrate it into one of the ORU labs

Things I want to briefly want to learn:

    Pipelines
    Orchestrations
    Adapters
    (I need to know what they are, but it should not be the focus of a the class/book)

Things I don’t want to learn:

BAM: BizTalk in a Healthcare Scenario should never be the system of record, the Claim Payment application should be, the out-of-the-box EDI reporting functionality that comes with BizTalk accounts for 99% of what I need
BRE: BizTalk would not be used for message modification/routing/anything else the BRE can be used for, as there are entire workflows designed in every claim payment processing system that deals with changing business rules
AS2: It is too big of a beast to teach along with all of the things that I listed above, and second: NO HEALTHCARE COMPANY TRANSMITS DATA USING AS2
Purchase Orders, Invoices, Advanced Shipment Notifications: I can look at a whole plethora of examples that Microsoft publish. Maybe a MCO deals with ordering supplies for an office, but by in large, I want to know about HIPAA transactions; not transactions I would never see at work.

Anything that I missed? I am thinking of putting together an online class (or via CD) or a book, what would you like to see? Is there any interest in a BizTalk/Healthcare book?

Tell me your experiences!

Preserve Interchange – what is it, and why use it

Eric Stott — Wed, 09 Dec 2009 06:21:28 GMT

The below setting is somewhat misunderstood:

If you are to set this setting, what is passed to the Message Box, and what is the behavior.

To simulate this, I created a send port with the following setting:

When I ran the 867 (found in the samples folder of the SDK) I got the following error message:

There was a failure executing the send pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLTransmit, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "XML assembler" Send Port: "EDI Drop Off" URI: "C:\Users\Administrator\Documents\Visual Studio 2008\Projects\EDI\Output\%MessageID%.xml" Reason: This Assembler cannot retrieve a document specification using this type: http://schemas.microsoft.com/BizTalk/EDI/X12/2006/InterchangeXML#X12InterchangeXml.

I could not find the schema in the BizTalk 2009 folder.

Here is the data that is created by the EDI DASM with the Preserve Interchange setting:

<ins0:X12InterchangeXml DelimiterSetSerializedData="126:13:10:42:58:-1:-1:-1:-1" xmlns:ins0="http://schemas.microsoft.com/BizTalk/EDI/X12/2006/InterchangeXML">
  <ns0:ISA xmlns:ns0="http://schemas.microsoft.com/Edi/X12ServiceSchema">
    <ISA01>00</ISA01>
    <ISA02>          </ISA02>
    <ISA03>00</ISA03>
    <ISA04>          </ISA04>
    <ISA05>ZZ</ISA05>
    <ISA06>7654321        </ISA06>
    <ISA07>ZZ</ISA07>
    <ISA08>1234567        </ISA08>
    <ISA09>991221</ISA09>
    <ISA10>1226</ISA10>
    <ISA11>U</ISA11>
    <ISA12>00401</ISA12>
    <ISA13>000000025</ISA13>
    <ISA14>0</ISA14>
    <ISA15>T</ISA15>
    <ISA16>:</ISA16>
  </ns0:ISA>
  <FunctionalGroup DocType="http://schemas.microsoft.com/BizTalk/EDI/X12/2006#X12_00401_864">
    <ns0:GS xmlns:ns0="http://schemas.microsoft.com/Edi/X12ServiceSchema">
      <GS01>TX</GS01>
      <GS02>01</GS02>
      <GS03>1234567</GS03>
      <GS04>19991221</GS04>
      <GS05>1226</GS05>
      <GS06>2</GS06>
      <GS07>X</GS07>
      <GS08>004010</GS08>
    </ns0:GS>
    <TransactionSet DocType="http://schemas.microsoft.com/BizTalk/EDI/X12/2006#X12_00401_864">
      <ns0:X12_00401_864 xmlns:ns0="http://schemas.microsoft.com/BizTalk/EDI/X12/2006">
        <ST>
          <ST01>864</ST01>
          <ST02>3313</ST02>
        </ST>
        <ns0:BMG>
          <BMG01>00</BMG01>
        </ns0:BMG>
        <ns0:DTM>
          <DTM01>001</DTM01>
        </ns0:DTM>
        <ns0:N1Loop1>
          <ns0:N1>
            <N101>001</N101>
          </ns0:N1>
          <ns0:N2>
            <N201>N201</N201>
          </ns0:N2>
          <ns0:N3>
            <N301>N301</N301>
          </ns0:N3>
          <ns0:N4>
            <N401>N401</N401>
          </ns0:N4>
          <ns0:REF>
            <REF01>01</REF01>
            <ns0:C040>
              <C04001>01</C04001>
              <C04002>C04002</C04002>
            </ns0:C040>
          </ns0:REF>
          <ns0:PER>
            <PER01>1A</PER01>
          </ns0:PER>
        </ns0:N1Loop1>
        <ns0:MITLoop1>
          <ns0:MIT>
            <MIT01>MIT01</MIT01>
          </ns0:MIT>
          <ns0:N1Loop2>
            <ns0:N1_2>
              <N101>001</N101>
            </ns0:N1_2>
            <ns0:N2_2>
              <N201>N201</N201>
            </ns0:N2_2>
            <ns0:N3_2>
              <N301>N301</N301>
            </ns0:N3_2>
            <ns0:N4_2>
              <N401>N401</N401>
            </ns0:N4_2>
            <ns0:REF_2>
              <REF01>01</REF01>
              <ns0:C040_2>
                <C04001>01</C04001>
                <C04002>C04002</C04002>
              </ns0:C040_2>
            </ns0:REF_2>
            <ns0:PER_2>
              <PER01>1A</PER01>
            </ns0:PER_2>
          </ns0:N1Loop2>
          <ns0:MSG>
            <MSG01>MSG01</MSG01>
          </ns0:MSG>
        </ns0:MITLoop1>
        <SE>
          <SE01>18</SE01>
          <SE02>3313</SE02>
        </SE>
      </ns0:X12_00401_864>
    </TransactionSet>
    <ns0:GE xmlns:ns0="http://schemas.microsoft.com/Edi/X12ServiceSchema">
      <GE01>1</GE01>
      <GE02>2</GE02>
    </ns0:GE>
  </FunctionalGroup>
  <ns0:IEA xmlns:ns0="http://schemas.microsoft.com/Edi/X12ServiceSchema">
    <IEA01>1</IEA01>
    <IEA02>000000025</IEA02>
  </ns0:IEA>
</ins0:X12InterchangeXml>

So what can consume this message?

If you look at Preserving a Received Batched EDI Interchange, it does not explain about what happens to the document.

If you look at Sending a Preserved Batch Interchange, it will recreate the interchange based on the settings described on the page.

I asked why there is this option, to preserve interchange, getting a document that is only consumable by the EDI ASM, and was told that you can create a map that transforms all of the EDI transactions from one to another and keep the batch together, however this does not seem correct, because a map would be looking for a MessageType of http://schemas.microsoft.com/BizTalk/EDI/X12/2006#X12_00401_864, not http://schemas.microsoft.com/BizTalk/EDI/X12/2006/InterchangeXML#X12InterchangeXml

It seems that this is only for validation, or re-enveloping.

Google Chrome Operating System

Eric Stott — Tue, 24 Nov 2009 10:55:27 GMT

I know this is VERY off subject, but I wanted to see what all of the hubub was about with the new OS that is going to be hitting the market in late 2010.

I installed it and ran it. Not terribly interesting. Here are two of the main shots:

and the really interesting screen

My take on it:

I guess it will be good for those who love to browse the internet and that is it. I am a big fan of Android, but an OS that is centered simply around the web? I guess the time is coming … “welcome to cloud computing.”

Trim leading characters from a string in TSQL

Eric Stott — Fri, 23 Oct 2009 17:12:59 GMT

The easiest way to do this is multiply by 1, but if it is a (n)varchar data type, it is a little more challenging.

This was the query I had to run:

DECLARE @insurancenumber nvarchar(100),@charactertopurge nvarchar(1)
SET @charactertopurge='0'
SET @insurancenumber='00PK102947'
SELECT @GMPI=GMPI
from Patient.dbo.PatientAlias
where AliasField=substring(@insurancenumber, patindex('%[^'+@charactertopurge+']%',@insurancenumber), len(@insurancenumber))