XSLT 1.0 Change output of record based on sibling record parameter

Question

I have an incoming XML file that has a list of participants and dependents that I need to transform slightly so the dependent information is attached as child records to the participant information and the participant record's EN_PLAN_TYPE node gets a value based on how many dependents a participant has as well as their relationship.

INCOMING XML

  <EligibilityRecords xmlns="http://Eligibility_LSDD">
    <Eligibility_Detail_Record xmlns="">
      <PT_PARTICIPANT_FILE_IMP_ID>553739837</PT_PARTICIPANT_FILE_IMP_ID>
      <PT_LST_NM>DOE</PT_LST_NM>
      <PT_FRST_NM>JANE</PT_FRST_NM>
      <EN_PLAN_NAME>Health Reimbursement Arrangement</EN_PLAN_NAME>
      <EN_MBRSHP_EFF_STRT_DT>01012017</EN_MBRSHP_EFF_STRT_DT>
      <EN_MBRSHP_EFF_END_DT></EN_MBRSHP_EFF_END_DT>
      <EN_PLAN_YEAR_STRT_DT>01012017</EN_PLAN_YEAR_STRT_DT>
    </Eligibility_Detail_Record>
    <Eligibility_Detail_Record xmlns="">
        <PT_PARTICIPANT_FILE_IMP_ID>561859010</PT_PARTICIPANT_FILE_IMP_ID>
        <PT_LST_NM>MCLEOD</PT_LST_NM>
        <PT_FRST_NM>CONOR</PT_FRST_NM>
        <EN_PLAN_NAME>Health Reimbursement Arrangement</EN_PLAN_NAME>
        <EN_MBRSHP_EFF_STRT_DT>01012016</EN_MBRSHP_EFF_STRT_DT>
        <EN_MBRSHP_EFF_END_DT></EN_MBRSHP_EFF_END_DT>
        <EN_PLAN_YEAR_STRT_DT>01012016</EN_PLAN_YEAR_STRT_DT>
    </Eligibility_Detail_Record>
    <Eligibility_Detail_Record xmlns="">
        <PT_PARTICIPANT_FILE_IMP_ID>561859010</PT_PARTICIPANT_FILE_IMP_ID>
        <PT_LST_NM>MCLEOD</PT_LST_NM>
        <PT_FRST_NM>CONOR</PT_FRST_NM>
        <EN_PLAN_NAME>Health Reimbursement Arrangement</EN_PLAN_NAME>
        <EN_MBRSHP_EFF_STRT_DT>01012016</EN_MBRSHP_EFF_STRT_DT>
        <EN_MBRSHP_EFF_END_DT></EN_MBRSHP_EFF_END_DT>
        <EN_PLAN_YEAR_STRT_DT>01012016</EN_PLAN_YEAR_STRT_DT>
        <DP_PARTICIPANT_FILE_IMP_ID>561859010</DP_PARTICIPANT_FILE_IMP_ID>
        <DP_DEPENDENT_FILE_IMP_ID>SC000018241906</DP_DEPENDENT_FILE_IMP_ID>
        <DP_RELATIONSHIP>Dependent</DP_RELATIONSHIP>
        <DP_LST_NM>MCLEOD</DP_LST_NM>
        <DP_FRST_NM>DUNCAN</DP_FRST_NM>
    </Eligibility_Detail_Record>
    <Eligibility_Detail_Record xmlns="">
      <PT_PARTICIPANT_FILE_IMP_ID>610051908</PT_PARTICIPANT_FILE_IMP_ID>
      <PT_LST_NM>JACKSON</PT_LST_NM>
      <PT_FRST_NM>MICHAEL</PT_FRST_NM>
      <EN_PLAN_NAME>Health Reimbursement Arrangement</EN_PLAN_NAME>
      <EN_MBRSHP_EFF_STRT_DT>01012017</EN_MBRSHP_EFF_STRT_DT>
      <EN_MBRSHP_EFF_END_DT></EN_MBRSHP_EFF_END_DT>
      <EN_PLAN_YEAR_STRT_DT>01012017</EN_PLAN_YEAR_STRT_DT>
    </Eligibility_Detail_Record>
    <Eligibility_Detail_Record xmlns="">
      <PT_PARTICIPANT_FILE_IMP_ID>610051908</PT_PARTICIPANT_FILE_IMP_ID>
      <PT_LST_NM>JACKSON</PT_LST_NM>
      <PT_FRST_NM>MICHAEL</PT_FRST_NM>
      <EN_PLAN_NAME>Health Reimbursement Arrangement</EN_PLAN_NAME>
      <EN_MBRSHP_EFF_STRT_DT>01012017</EN_MBRSHP_EFF_STRT_DT>
      <EN_MBRSHP_EFF_END_DT></EN_MBRSHP_EFF_END_DT>
      <EN_PLAN_YEAR_STRT_DT>01012017</EN_PLAN_YEAR_STRT_DT>
      <DP_PARTICIPANT_FILE_IMP_ID>610051908</DP_PARTICIPANT_FILE_IMP_ID>
      <DP_DEPENDENT_FILE_IMP_ID>NC110015202761</DP_DEPENDENT_FILE_IMP_ID>
      <DP_RELATIONSHIP>Spouse</DP_RELATIONSHIP>
      <DP_LST_NM>JACKSON</DP_LST_NM>
      <DP_FRST_NM>MELISSA</DP_FRST_NM>
    </Eligibility_Detail_Record>
    <Eligibility_Detail_Record xmlns="">
      <PT_PARTICIPANT_FILE_IMP_ID>553739837</PT_PARTICIPANT_FILE_IMP_ID>
      <PT_LST_NM>DOE</PT_LST_NM>
      <PT_FRST_NM>JANE</PT_FRST_NM>
      <EN_PLAN_NAME>Health Reimbursement Arrangement</EN_PLAN_NAME>
      <EN_MBRSHP_EFF_STRT_DT>01012016</EN_MBRSHP_EFF_STRT_DT>
      <EN_MBRSHP_EFF_END_DT></EN_MBRSHP_EFF_END_DT>
      <EN_PLAN_YEAR_STRT_DT>01012016</EN_PLAN_YEAR_STRT_DT>
      <DP_PARTICIPANT_FILE_IMP_ID>553739837</DP_PARTICIPANT_FILE_IMP_ID>
      <DP_DEPENDENT_FILE_IMP_ID>NC110012077673</DP_DEPENDENT_FILE_IMP_ID>
      <DP_RELATIONSHIP>Spouse</DP_RELATIONSHIP>
      <DP_LST_NM>DOE</DP_LST_NM>
      <DP_FRST_NM>JOHN</DP_FRST_NM>
    </Eligibility_Detail_Record>
    <Eligibility_Detail_Record xmlns="">
      <PT_PARTICIPANT_FILE_IMP_ID>553739837</PT_PARTICIPANT_FILE_IMP_ID>
      <PT_LST_NM>DOE</PT_LST_NM>
      <PT_FRST_NM>JANE</PT_FRST_NM>
      <EN_PLAN_NAME>Health Reimbursement Arrangement</EN_PLAN_NAME>
      <EN_MBRSHP_EFF_STRT_DT>01012016</EN_MBRSHP_EFF_STRT_DT>
      <EN_MBRSHP_EFF_END_DT></EN_MBRSHP_EFF_END_DT>
      <EN_PLAN_YEAR_STRT_DT>01012016</EN_PLAN_YEAR_STRT_DT>
      <DP_PARTICIPANT_FILE_IMP_ID>553739837</DP_PARTICIPANT_FILE_IMP_ID>
      <DP_DEPENDENT_FILE_IMP_ID>NC110012077680</DP_DEPENDENT_FILE_IMP_ID>
      <DP_RELATIONSHIP>Dependent</DP_RELATIONSHIP>
      <DP_LST_NM>DOE</DP_LST_NM>
      <DP_FRST_NM>JIMMY</DP_FRST_NM>
    </Eligibility_Detail_Record>
    <Eligibility_Detail_Record xmlns="">
      <PT_PARTICIPANT_FILE_IMP_ID>573794953</PT_PARTICIPANT_FILE_IMP_ID>
      <PT_LST_NM>GABRIEL</PT_LST_NM>
      <PT_FRST_NM>PETER</PT_FRST_NM>
      <EN_PLAN_NAME>Health Reimbursement Arrangement</EN_PLAN_NAME>
      <EN_MBRSHP_EFF_STRT_DT>01012016</EN_MBRSHP_EFF_STRT_DT>
      <EN_MBRSHP_EFF_END_DT></EN_MBRSHP_EFF_END_DT>
      <EN_PLAN_YEAR_STRT_DT>01012016</EN_PLAN_YEAR_STRT_DT>
    </Eligibility_Detail_Record>
  </EligibilityRecords>

REQUIREMENTS

Create a new Dependent_Record that is a child record of Eligibility_Detail_Record for each DP_PARTICIPANT_FILE_IMP_ID that matches a PT_PARTICIPANT_FILE_IMP_ID
Add an EN_PLAN_TYPE field for each Record that has an EN_PLAN_NAME of "Health Reimbursement Arrangement" that reads:
- "Ind" if no dependents are found
- "IndSpouse" if one and only one dependent that has DP_RELATIONSHIP of Spouse
- "IndChild" if one and only one dependent that has DP_RELATIONSHIP of Dependent
- "Family" if more than one dependent

EXPECTED OUTPUT

<EligibilityRecords xmlns="http://Eligibility_LSDD">
  <Eligibility_Detail_Record xmlns="">
    <PT_PARTICIPANT_FILE_IMP_ID>553739837</PT_PARTICIPANT_FILE_IMP_ID>
    <PT_LST_NM>DOE</PT_LST_NM>
    <PT_FRST_NM>JANE</PT_FRST_NM>
    <EN_PLAN_NAME>Health Reimbursement Arrangement</EN_PLAN_NAME>
    <EN_PLAN_TYPE>Family</EN_PLAN_TYPE>
    <EN_MBRSHP_EFF_STRT_DT>01012017</EN_MBRSHP_EFF_STRT_DT>
    <EN_MBRSHP_EFF_END_DT></EN_MBRSHP_EFF_END_DT>
    <EN_PLAN_YEAR_STRT_DT>01012017</EN_PLAN_YEAR_STRT_DT>
    <Dependent_Record>
      <DP_PARTICIPANT_FILE_IMP_ID>553739837</DP_PARTICIPANT_FILE_IMP_ID>
      <DP_DEPENDENT_FILE_IMP_ID>NC110012077673</DP_DEPENDENT_FILE_IMP_ID>
      <DP_RELATIONSHIP>Spouse</DP_RELATIONSHIP>
      <DP_LST_NM>DOE</DP_LST_NM>
      <DP_FRST_NM>JOHN</DP_FRST_NM>
    </Dependent_Record>
    <Dependent_Record>
      <DP_PARTICIPANT_FILE_IMP_ID>553739837</DP_PARTICIPANT_FILE_IMP_ID>
      <DP_DEPENDENT_FILE_IMP_ID>NC110012077680</DP_DEPENDENT_FILE_IMP_ID>
      <DP_RELATIONSHIP>Dependent</DP_RELATIONSHIP>
      <DP_LST_NM>DOE</DP_LST_NM>
      <DP_FRST_NM>JIMMY</DP_FRST_NM>
    </Dependent_Record>
  </Eligibility_Detail_Record>
  <Eligibility_Detail_Record xmlns="">
    <PT_PARTICIPANT_FILE_IMP_ID>610051908</PT_PARTICIPANT_FILE_IMP_ID>
    <PT_LST_NM>JACKSON</PT_LST_NM>
    <PT_FRST_NM>MICHAEL</PT_FRST_NM>
    <EN_PLAN_NAME>Health Reimbursement Arrangement</EN_PLAN_NAME>
    <EN_PLAN_TYPE>IndSpouse</EN_PLAN_TYPE>
    <EN_MBRSHP_EFF_STRT_DT>01012017</EN_MBRSHP_EFF_STRT_DT>
    <EN_MBRSHP_EFF_END_DT></EN_MBRSHP_EFF_END_DT>
    <EN_PLAN_YEAR_STRT_DT>01012017</EN_PLAN_YEAR_STRT_DT>
    <Dependent_Record>
      <DP_PARTICIPANT_FILE_IMP_ID>610051908</DP_PARTICIPANT_FILE_IMP_ID>
      <DP_DEPENDENT_FILE_IMP_ID>NC110015202761</DP_DEPENDENT_FILE_IMP_ID>
      <DP_RELATIONSHIP>Spouse</DP_RELATIONSHIP>
      <DP_LST_NM>JACKSON</DP_LST_NM>
      <DP_FRST_NM>MELISSA</DP_FRST_NM>
    </Dependent_Record>
  </Eligibility_Detail_Record>
  <Eligibility_Detail_Record xmlns="">
    <PT_PARTICIPANT_FILE_IMP_ID>561859010</PT_PARTICIPANT_FILE_IMP_ID>
    <PT_LST_NM>MCLEOD</PT_LST_NM>
    <PT_FRST_NM>CONOR</PT_FRST_NM>
    <EN_PLAN_NAME>Health Reimbursement Arrangement</EN_PLAN_NAME>
    <EN_PLAN_TYPE>IndChild</EN_PLAN_TYPE>
    <EN_MBRSHP_EFF_STRT_DT>01012016</EN_MBRSHP_EFF_STRT_DT>
    <EN_MBRSHP_EFF_END_DT></EN_MBRSHP_EFF_END_DT>
    <EN_PLAN_YEAR_STRT_DT>01012016</EN_PLAN_YEAR_STRT_DT>
    <Dependent_Record>
      <DP_PARTICIPANT_FILE_IMP_ID>561859010</DP_PARTICIPANT_FILE_IMP_ID>
      <DP_DEPENDENT_FILE_IMP_ID>SC000018241906</DP_DEPENDENT_FILE_IMP_ID>
      <DP_RELATIONSHIP>Dependent</DP_RELATIONSHIP>
      <DP_LST_NM>MCLEOD</DP_LST_NM>
      <DP_FRST_NM>DUNCAN</DP_FRST_NM>
    </Dependent_Record>
  </Eligibility_Detail_Record>
  <Eligibility_Detail_Record xmlns="">
    <PT_PARTICIPANT_FILE_IMP_ID>573794953</PT_PARTICIPANT_FILE_IMP_ID>
    <PT_LST_NM>GABRIEL</PT_LST_NM>
    <PT_FRST_NM>PETER</PT_FRST_NM>
    <EN_PLAN_NAME>Health Reimbursement Arrangement</EN_PLAN_NAME>
    <EN_MBRSHP_EFF_STRT_DT>01012016</EN_MBRSHP_EFF_STRT_DT>
    <EN_MBRSHP_EFF_END_DT></EN_MBRSHP_EFF_END_DT>
    <EN_PLAN_YEAR_STRT_DT>01012016</EN_PLAN_YEAR_STRT_DT>
    <EN_PLAN_TYPE>Ind</EN_PLAN_TYPE>
  </Eligibility_Detail_Record>
</EligibilityRecords>

XSLT 1.0

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output indent="yes" omit-xml-declaration="yes"/>
  <xsl:strip-space elements="*"/>

  <xsl:key name="participants" match="Eligibility_Detail_Record" use="PT_PARTICIPANT_FILE_IMP_ID"/>

  <xsl:template match="/*">
    <xsl:copy>
      <xsl:for-each select="Eligibility_Detail_Record[string(PT_PARTICIPANT_FILE_IMP_ID)]
        [count(.|key('participants',PT_PARTICIPANT_FILE_IMP_ID)[1])=1]">
        <xsl:variable name="participantCount" select="count(key('participants',PT_PARTICIPANT_FILE_IMP_ID)[string(DP_PARTICIPANT_FILE_IMP_ID)])"/>
        <Eligibility_Detail_Record>
          <xsl:copy-of select="*[starts-with(name(),'PT_')]|EN_PLAN_NAME"/>
          <xsl:choose>
            <!--Add an EN_PLAN_TYPE field for each Record that has an EN_PLAN_NAME of "Health Reimbursement Arrangement" that reads:-->
            <xsl:when test="EN_PLAN_NAME='Health Reimbursement Arrangement'">
              <xsl:call-template name="HRA">
                <xsl:with-param name="participantCount" select="$participantCount"/>
              </xsl:call-template>
            </xsl:when>
          </xsl:choose>
          <xsl:copy-of select="*[not(self::EN_PLAN_NAME) and starts-with(name(),'EN_')]"/>
          <!--Create a new Dependent_Record that is a child record of Eligibility_Detail_Record for each DP_PARTICIPANT_FILE_IMP_ID that matches a PT_PARTICIPANT_FILE_IMP_ID-->
          <xsl:for-each select="key('participants',PT_PARTICIPANT_FILE_IMP_ID)[string(DP_PARTICIPANT_FILE_IMP_ID)]">
            <Dependent_Record>
              <xsl:copy-of select="*[starts-with(name(),'DP_')]"/>
            </Dependent_Record>
          </xsl:for-each>
        </Eligibility_Detail_Record>
      </xsl:for-each>
    </xsl:copy>
  </xsl:template>

  <xsl:template name="HRA">
    <xsl:param name="participantCount"/>
    <EN_PLAN_TYPE>
      <xsl:choose>
        <!--"Family" if more than one dependent-->
        <xsl:when test="$participantCount > 1">Family</xsl:when>
        <!--"IndChild" if one and only one dependent that has DP_RELATIONSHIP of Dependent-->
        <xsl:when test="$participantCount = 1 and DP_RELATIONSHIP = 'Dependent'">IndChild</xsl:when>
        <!--"IndSpouse" if one and only one dependent that has DP_RELATIONSHIP of Spouse-->
        <xsl:when test="$participantCount = 1 and DP_RELATIONSHIP = 'Spouse'">IndSpouse</xsl:when>
        <!--"Ind" if no dependents are found-->
        <xsl:when test="$participantCount = 0">Ind</xsl:when>
      </xsl:choose>
    </EN_PLAN_TYPE>
  </xsl:template>

</xsl:stylesheet>

The issue is that DP_RELATIONSHIP is being tested against the first occurrence of each ELIGIBILITY_DETAIL_RECORD for a particular participant which will always be blank. I'm just not sure how to easily adjust it to test against the first occurrence of DP_RELATIONSHIP for records where a dependent exists (DP_DEPENDENT_FILE_IMP_ID is found) and participantCount is 1 (I should probably rename the variable to dependentCount but I'll worry about that later)

Instead of linking to a previous question, please completely describe your current problem here. From your question, it is not clear what the expected and actual output is and there are parts of your code missing. It is very difficult to understand what you're asking if we first need to read and understand the previous question *and* its answer. — flyx, Feb 07 '17 at 09:44

score 0 · Accepted Answer · answered Feb 07 '17 at 21:52

Rather than collecting all participants with a key, I'd suggest just collecting the dependents.

Template top level records first then EN_PLAN_TYPE and Dependent_Record elements using the key:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes" omit-xml-declaration="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:key name="dependents" match="Eligibility_Detail_Record" use="DP_PARTICIPANT_FILE_IMP_ID"/>

    <xsl:template match="/*">
        <xsl:copy>
            <xsl:apply-templates select="Eligibility_Detail_Record[not(DP_PARTICIPANT_FILE_IMP_ID)]"/>
        </xsl:copy>
    </xsl:template>

    <!-- Template top level records -->
    <xsl:template match="Eligibility_Detail_Record[not(DP_PARTICIPANT_FILE_IMP_ID)]">
        <xsl:copy>
            <xsl:copy-of select="*[starts-with(name(),'PT_')]|EN_PLAN_NAME"/>
            <!--Add an EN_PLAN_TYPE field for each Record that has an EN_PLAN_NAME of "Health Reimbursement Arrangement" -->
            <xsl:if test="EN_PLAN_NAME='Health Reimbursement Arrangement'">
                <xsl:call-template name="HRA"/>
            </xsl:if>
            <xsl:copy-of select="*[not(self::EN_PLAN_NAME) and starts-with(name(),'EN_')]"/>
            <xsl:apply-templates select="key('dependents', PT_PARTICIPANT_FILE_IMP_ID)"/>
        </xsl:copy>
    </xsl:template>

    <!-- Template dependent records -->
    <xsl:template match="Eligibility_Detail_Record[DP_PARTICIPANT_FILE_IMP_ID]">
        <Dependent_Record>
            <xsl:copy-of select="*[starts-with(name(), 'DP_')]"/>
        </Dependent_Record>
    </xsl:template>

    <!-- Plan type -->
    <xsl:template name="HRA">
        <xsl:variable name="dependents" select="key('dependents', PT_PARTICIPANT_FILE_IMP_ID)"/>
        <xsl:variable name="participantCount" select="count($dependents)"/>
        <EN_PLAN_TYPE>
            <xsl:choose>
                <!--"Family" if more than one dependent-->
                <xsl:when test="$participantCount > 1">Family</xsl:when>
                <!--"IndChild" if one and only one dependent that has DP_RELATIONSHIP of Dependent-->
                <xsl:when test="$participantCount = 1 and $dependents/DP_RELATIONSHIP = 'Dependent'">IndChild</xsl:when>
                <!--"IndSpouse" if one and only one dependent that has DP_RELATIONSHIP of Spouse-->
                <xsl:when test="$participantCount = 1 and $dependents/DP_RELATIONSHIP = 'Spouse'">IndSpouse</xsl:when>
                <!--"Ind" if no dependents are found-->
                <xsl:when test="$participantCount = 0">Ind</xsl:when>
            </xsl:choose>
        </EN_PLAN_TYPE>
    </xsl:template>

</xsl:stylesheet>

This answer seems so obvious to me now after seeing it. Staring at a problem for too long can really mess with the thought process. Thanks for the help! — Ritley572, Feb 08 '17 at 08:11

XSLT 1.0 Change output of record based on sibling record parameter

1 Answers1