XSLT to transform xml to xml pulling out specific values and mapping to new format

Question

I am looking for some help with XSLT to transform one xml file into another format.

The input xml file is below:

<PATIENTLIST ELAPSEDMS="234" >
    <PATIENT ID="MGH000007">
        <ADDRESS1>550 BREZHNEV ST</ADDRESS1>
        <ADDRESS2></ADDRESS2>
        <CITY>MOSCOW</CITY>
        <STATE>MA</STATE>
        <ZIP>02139</ZIP>
        <COUNTRY ISO3166-1="USSR"></COUNTRY>
        <DATEOFBIRTH>1934/04/10</DATEOFBIRTH>
        <DAYPHONE>(617) 111-1111 </DAYPHONE>
        <FIRSTNAME>TEST</FIRSTNAME>
        <HOMEPHONE>(617) 111-1111</HOMEPHONE>
        <LASTNAME>TEST MGH</LASTNAME>
        <LIMITEDACCESS>False</LIMITEDACCESS>
        <MARITALSTATUS>SINGLE</MARITALSTATUS>
        <MEDICALRECORDNUMBERS>
            <MEDICALRECORDNUMBER>
                <SITE>BWH</SITE>
                <STATUS>A</STATUS>
                <VALUE>0000007</VALUE>
            </MEDICALRECORDNUMBER>
            <MEDICALRECORDNUMBER>
                <SITE>BWI</SITE>
                <STATUS>A</STATUS>
                <VALUE>0000007</VALUE>
            </MEDICALRECORDNUMBER>
            <MEDICALRECORDNUMBER>
                <SITE>MEEI</SITE>
                <STATUS>A</STATUS>
                <VALUE>0000007</VALUE>
            </MEDICALRECORDNUMBER>
            <MEDICALRECORDNUMBER>
                <SITE>MGH</SITE>
                <STATUS>A</STATUS>
                <VALUE>0000007</VALUE>
            </MEDICALRECORDNUMBER>
            <MEDICALRECORDNUMBER>
                <SITE>SHC</SITE>
                <STATUS>A</STATUS>
                <VALUE>0000007</VALUE>
            </MEDICALRECORDNUMBER>
            <MEDICALRECORDNUMBER>
                <SITE>OLD #</SITE>
                <STATUS>M</STATUS>
                <VALUE>0000007</VALUE>
            </MEDICALRECORDNUMBER>
        </MEDICALRECORDNUMBERS>
        <MIDDLEINITIAL>R</MIDDLEINITIAL>
        <MOTHERSMAIDENNAME></MOTHERSMAIDENNAME>
        <MRNR>0000007</MRNR>
        <NAME>TEST MGH, TEST R</NAME>
        <NAMESUFFIX></NAMESUFFIX>
        <NAMEPREFIX></NAMEPREFIX>
        <PRIMARYCAREPROVIDERID>512513</PRIMARYCAREPROVIDERID>
        <PRIMARYLANGUAGE>ENGLISH</PRIMARYLANGUAGE>
        <RACE CODE1="BLACK" CODE2="" FREETEXT="">BLACK</RACE>
        <ETHNICITY CODE1="AFRICAN AMERICAN" CODE2="" FREETEXT="">AFRICAN AMERICAN</ETHNICITY>
        <RELIGION>NO PREFERENCE</RELIGION>
        <SEX>M</SEX>
        <SSN></SSN>
        <UID>101662537</UID>
        <VETERAN>NO</VETERAN>
    </PATIENT>
</PATIENTLIST>

The output file needs to look like:

<?xml version="1.0" encoding="utf-8" ?>
<eCliPSEDataIntegrationServiceRequest xmlns="http://iocent.com/eCliPSEDataIntegrationServiceRequest.xsd">
    <PatientIdentifierRecord MedicalRecordNumber="MGH000007" LastName="Person" FirstName="Test" MiddleInitial="A" DateOfBirth="04/10/1934" Operation="Add" OverwriteExistingData="true" />
    <PatientDataRecord MedicalRecordNumber="MGH000007" ParameterName="Gender" ParameterValue="2" TimeStamp="8/30/2011" Operation="Add" OverwriteExistingData="true" />
    <PatientDataRecord MedicalRecordNumber="MGH000007" ParameterName="Race" ParameterValue="1" TimeStamp="8/30/2011" Operation="Add" OverwriteExistingData="true" />
</eCliPSEDataIntegrationServiceRequest>

So I am looking to pull out the

Patient ID and use it as the MedicalRecordNumber= value

DATEOFBIRTH node as the DateOfBirth value - with format changed from YYYY/MM/DD to MM/DD/YYYY

FIRSTNAME node becomes value for FirstName

LASTNAME node becomes value for LastName

MIDDLEINITIAL node becomes value for MiddleInitial

SEX node becomes value for Gender Male=1, Female=2

RACE node becomes Value for Race - based on a lookup table (Caucasian=1,Afican American=2, etc.) -

So I need to pull out these values, get the format changed, in some cases do a table-like translation (for gender and race), and write the file out in the new format.

I am limited to XSLT 1.0

I am very new to XSLT and so any help would be greatly appreciated!!!

thanks!

Can you clarify **Gender** and **Race** nodes? They are not present in the sample output. — Emiliano Poggi, Aug 31 '11 at 07:25
Yes - value of Gender comes from Sex node Race from Race in orignal xml file — John Mc, Aug 31 '11 at 13:17

score 1 · Answer 1 · answered Aug 31 '11 at 19:06

I appreciate the start that I received above. I am posting the xslt that I came up with to solve my problem below:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:variable name='newline'><xsl:text> 
</xsl:text>
</xsl:variable>

<xsl:variable name='MRN'>
    <xsl:value-of select="PATIENTLIST/PATIENT/@ID"/>
</xsl:variable>

<xsl:variable name='Gender'>
    <xsl:if test="PATIENTLIST/PATIENT/SEX='M'">1</xsl:if>
    <xsl:if test="PATIENTLIST/PATIENT/SEX='F'">2</xsl:if>
</xsl:variable>

 <xsl:variable name='RaceIn'>
   <xsl:value-of select="PATIENTLIST/PATIENT/RACE"/>
 </xsl:variable>  

<xsl:variable name='Race'>
  <xsl:choose>
    <xsl:when test="$RaceIn='WHITE'">1</xsl:when>
    <xsl:when test="$RaceIn='BLACK'">2</xsl:when>
    <xsl:when test="$RaceIn='HISPANIC'">3</xsl:when>
    <xsl:when test="$RaceIn='ASIAN'">4</xsl:when>
    <xsl:when test="$RaceIn='NATIVE AMERICAN'">5</xsl:when>
    <xsl:when test="$RaceIn='INDIAN'">5</xsl:when>
    <xsl:otherwise>7</xsl:otherwise>
  </xsl:choose>
</xsl:variable>

 <xsl:variable name='CurrDate'>
    <xsl:value-of select="'MM/DD/YYY'"/>
</xsl:variable>

<xsl:variable name='Operation'>
    <xsl:value-of select="'Add'"/>
</xsl:variable>

<xsl:variable name='Overwrite'>
    <xsl:value-of select="'true'"/>
</xsl:variable>




  <xsl:template match="/">
    <xsl:text>
    </xsl:text>
      <eCliPSEDataIntegrationServiceRequest >
        <xsl:text>
        </xsl:text>
        <PatientIdentifierRecord>
          <xsl:attribute name="MedicalRecordNumber">
            <xsl:value-of select="$MRN"/>
          </xsl:attribute>
          <xsl:attribute name="LastName">
            <xsl:value-of select="PATIENTLIST/PATIENT/LASTNAME"/>
          </xsl:attribute>
          <xsl:attribute name="FirstName">
            <xsl:value-of select="PATIENTLIST/PATIENT/FIRSTNAME"/>
          </xsl:attribute>
          <xsl:attribute name="MiddleInitial">
            <xsl:value-of select="PATIENTLIST/PATIENT/MIDDLEINITIAL"/>
          </xsl:attribute>
          <xsl:attribute name="DateOfBirth">
            <xsl:value-of select="PATIENTLIST/PATIENT/DATEOFBIRTH"/> <!-- do we need to change the date format?-->
          </xsl:attribute>
          <xsl:attribute name="Operation">
            <xsl:value-of select="$Operation"/>
          </xsl:attribute>
          <xsl:attribute name="OverwriteExistingData">
            <xsl:value-of select="$Overwrite"/>
          </xsl:attribute>
        </PatientIdentifierRecord>
        <xsl:text>
            </xsl:text>
          <PatientDataRecord>
            <xsl:attribute name="MedicalRecordNumber">
              <xsl:value-of select="$MRN"/>
            </xsl:attribute>
            <xsl:attribute name="ParameterName">
              <xsl:value-of select="'Gender'"/>
            </xsl:attribute>
            <xsl:attribute name="ParameterValue">
              <xsl:value-of select="$Gender"/>
            </xsl:attribute>
            <xsl:attribute name="Timestamp">
              <xsl:value-of select="$CurrDate"/>
            </xsl:attribute>
            <xsl:attribute name="Operation">
              <xsl:value-of select="$Operation"/>
            </xsl:attribute>
            <xsl:attribute name="OverwriteExistingData">
              <xsl:value-of select="$Overwrite"/>
            </xsl:attribute>
          </PatientDataRecord>
        <xsl:text>
            </xsl:text>
        <PatientDataRecord>
          <xsl:attribute name="MedicalRecordNumber">
            <xsl:value-of select="$MRN"/>
          </xsl:attribute>
          <xsl:attribute name="ParameterName">
            <xsl:value-of select="'Race'"/>
          </xsl:attribute>
          <xsl:attribute name="ParameterValue">
            <xsl:value-of select="$Race"/>           <!-- this needs to be transformed based on race table and race_7_groups parameter -->
          </xsl:attribute>
          <xsl:attribute name="Timestamp">
            <xsl:value-of select="$CurrDate"/>                  <!-- how do i get the current date?-->
          </xsl:attribute>
          <xsl:attribute name="Operation">
            <xsl:value-of select="$Operation"/>
          </xsl:attribute>
          <xsl:attribute name="OverwriteExistingData">
            <xsl:value-of select="$Overwrite"/>
          </xsl:attribute>
        </PatientDataRecord>
        <xsl:text>
    </xsl:text>
      </eCliPSEDataIntegrationServiceRequest>
  </xsl:template>
</xsl:stylesheet>

I am still working on a solution to get the current date. Options I have looked at - passing in as parameter - writing a script function to get the current date

Also, I am not sure yet if I need to change the format of the date from YYYY/MM/DD to MM/DD/YYYY.

If anyone has some suggestions on the two topics above, they would be welcomed.

thanks

score 1 · Accepted Answer · answered Sep 02 '11 at 02:53

Based on feedback - I am updating my solution and posting here

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output indent="yes"/>

  <xsl:variable name="smallcase" select="'abcdefghijklmnopqrstuvwxyz'" />
  <xsl:variable name="uppercase" select="'ABCDEFGHIJKLMNOPQRSTUVWXYZ'" />

  <xsl:variable name='MRN'>
    <xsl:value-of select="PATIENTLIST/PATIENT/MRNR"/>
  </xsl:variable>

  <xsl:variable name='BirthDate'>
    <xsl:value-of select="PATIENTLIST/PATIENT/DATEOFBIRTH"/>
  </xsl:variable>

  <xsl:variable name="BDayYear" select="substring($BirthDate,1,4)" />
  <xsl:variable name="BDayMonth" select="substring($BirthDate,6,2)" />
  <xsl:variable name="BDayDay" select="substring($BirthDate,9,2)" />

  <xsl:variable name='BirthDateUse'>
    <xsl:value-of select="concat($BDayMonth, '/', $BDayDay, '/', $BDayYear)"/>
  </xsl:variable>

  <xsl:variable name='Gender'>
    <xsl:if test="translate(PATIENTLIST/PATIENT/SEX, $smallcase, $uppercase)='M'">1</xsl:if>
    <xsl:if test="translate(PATIENTLIST/PATIENT/SEX, $smallcase, $uppercase)='F'">2</xsl:if>
  </xsl:variable>

  <xsl:variable name='RaceUC'>
    <xsl:value-of select="translate(PATIENTLIST/PATIENT/RACE, $smallcase, $uppercase)"/>
  </xsl:variable>

  <xsl:variable name='Race'>
    <xsl:choose>
      <xsl:when test="$RaceUC='WHITE'">1</xsl:when>
      <xsl:when test="$RaceUC='BLACK'">2</xsl:when>
      <xsl:when test="$RaceUC='AFRICAN AMERICAN'">2</xsl:when>
      <xsl:when test="$RaceUC='HISPANIC'">3</xsl:when>
      <xsl:when test="$RaceUC='ASIAN'">4</xsl:when>
      <xsl:when test="$RaceUC='NATIVE AMERICAN'">5</xsl:when>
      <xsl:when test="$RaceUC='INDIAN'">6</xsl:when>
      <xsl:otherwise>7</xsl:otherwise>
    </xsl:choose>
  </xsl:variable>

  <xsl:variable name='Operation'>
    <xsl:value-of select="'Add'"/>
  </xsl:variable>

  <xsl:variable name='Overwrite'>
    <xsl:value-of select="'true'"/>
  </xsl:variable>

  <xsl:template match="/">
    <eCliPSEDataIntegrationServiceRequest xmlns="http://iocent.com/eCliPSEDataIntegrationServiceRequest.xsd">
      <PatientIdentifierRecord
        MedicalRecordNumber="{$MRN}"
        LastName="{PATIENTLIST/PATIENT/LASTNAME}"
        FirstName="{PATIENTLIST/PATIENT/FIRSTNAME}"
        MiddleInitial="{PATIENTLIST/PATIENT/MIDDLEINITIAL}"
        DateOfBirth ="{$BirthDate}"
        Operation="{$Operation}"
        OverwriteExistingData="{$Overwrite}"
      />
      <PatientDataRecord
          MedicalRecordNumber="{$MRN}"
          ParameterName="Gender" ParameterValue="{$Gender}"
          Operation="{$Operation}"
          OverwriteExistingData="{$Overwrite}"
          />
      <PatientDataRecord
        MedicalRecordNumber="{$MRN}"
        ParameterName="Race_7_Groups" ParameterValue="{$Race}"
        Operation="{$Operation}"
        OverwriteExistingData="{$Overwrite}"
        />
    </eCliPSEDataIntegrationServiceRequest>
  </xsl:template>
</xsl:stylesheet>

Thank you for the feedback and suggestions.

This version handles case conversion/comparison as well as formatting the bday in a format that I needed.

I like the AVT syntax to simplify the code and the to remove the "manual formatting" that I was doing - thanks for those tips!

The right way to say thanks to useful answers here is by upvoting them ;-). You are welcome anyway. +1 for your effort. — Emiliano Poggi, Sep 02 '11 at 06:04

score 0 · Answer 3 · answered Aug 30 '11 at 23:21

this should get you started down the right path:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="PATIENTLIST">
        <xsl:for-each select="PATIENT">                                             
            <eCliPSEDataIntegrationServiceRequest>
                <xsl:attribute name="xmlns">
                    http://iocent.com/eCliPSEDataIntegrationServiceRequest.xsd
                </xsl:attribute>
                <PatientIdentifierRecord>
                    <xsl:attribute name="MedicalRecordNumber">
                        <xsl:value-of select="@ID"/>
                    </xsl:attribute>
                    ...
                    <xsl:for-each select="MEDICALRECORDNUMBER">
                        <xsl:attribute name="MedicalRecordNumber">
                            <xsl:value-of select="@ID"/>
                        </xsl:attribute>
                        ...
                    </xsl:for-each>                                             
                </PatientIdentifierRecord>
            </eCliPSEDataIntegrationServiceRequest>
         </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

You will probably need some hard coded <xsl:if test="SEX='MALE'">1</xsl:if> statements to do the "enum" switching

Thanks very much for getting me started! I am going to follow up with a post for what I ended up creating to solve it. — John Mc, Aug 31 '11 at 19:03

score 0 · Answer 4 · edited May 23 '17 at 09:59

Just a few hints.

Use AVT syntax to simplify your code. Example:

    <PatientIdentifierRecord
      MedicalRecordNumber="{$MRN}"
      LastName="{PATIENTLIST/PATIENT/LASTNAME}"
     ...
    />

For line feed, use code from ISO-8859-1 character set:

<xsl:variable name="lf" select="'&#10;'"/>

For date-time see this topic and check your parser documentation.

If you need to change date format, use substring() and concat() function. Check this similar topic.

It seems that you are using line feed to indent your code. For this job you have a proper instruction (which might be supported depending on your parser):

<xsl:output indent="yes"/>

XSLT to transform xml to xml pulling out specific values and mapping to new format

4 Answers4

Linked