CODEWITHSUNDEEP
xmlxslt
FordPrefect141
FordPrefect141

Reputation: 205

Use XSLT to add value from one element to another

I have a source XML file as follows:

<section name="Test" code="" type="Table" fundid="15" subtype="SOI1" style="" xmlns="http://composition.bowne.com/2010/v4">
  <table xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" accountperiod="2014-07-31" accountperiodtype="0" code="I2" name="Holdings" fundid="15" type="" cols="2">
    <colspec colnum="1" colname="1" />
    <colspec colnum="2" colname="2" />
    <tbody>
      <tr type="categoryhead" level="1" itemtype="categoryhead" categorykey="621" hierarchykey="989">
        <td colname="1">
          <datapoint type="Regular" subtype="" name="Caption" value="Health Care Equipment &amp; Supplies" display="always">Health Care Equipment &amp; Supplies</datapoint>
        </td>
        <td colname="2">
          <datapoint type="Regular" subtype="" name="PercentOfNetAssets" value="16.6" display="always">16.6</datapoint>
        </td>
      </tr>
      <tr type="categoryhead" level="1" itemtype="categoryhead" categorykey="623" hierarchykey="989">
        <td colname="1">
          <datapoint type="Literal" subtype="Custom" name="All Others*" value="All Others*" display="always">All Others*</datapoint>
        </td>
        <td colname="2">
          <datapoint type="Regular" subtype="" name="PercentOfNetAssets" value="1.5" display="always">[~PONT]1.5[#~PONT]</datapoint>     (Line A)
        </td>
      </tr>
      <tr type="otherassets" level="1" itemtype="otherassets" hierarchykey="858">
        <td colname="1">
          <datapoint type="Literal" subtype="Custom" name="Other Assets" value="Other Assets" display="always">Other Assets</datapoint>
        </td>
        <td colname="2">
          <datapoint type="Regular" subtype="" name="PercentOfNetAssets" value="0.1" display="always">[~PONT]0.1[#~PONT]</datapoint>     (Line B)
        </td>
      </tr>
    </tbody>
  </table>
</section>

What I would like to do is take the 0.1 between the [~PONT] and [#~PONT] tags from (Line B) and add it to the 1.5 between the [~PONT] and [#~PONT] tags on (Line A), then suppress the node containing (Line B). The resultant XML should look like this:

<section name="Test" code="" type="Table" fundid="15" subtype="SOI1" style="" xmlns="http://composition.bowne.com/2010/v4">
  <table xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" accountperiod="2014-07-31" accountperiodtype="0" code="I2" name="Holdings" fundid="15" type="" cols="2">
    <colspec colnum="1" colname="1" />
    <colspec colnum="2" colname="2" />
    <tbody>
      <tr type="categoryhead" level="1" itemtype="categoryhead" categorykey="621" hierarchykey="989">
        <td colname="1">
          <datapoint type="Regular" subtype="" name="Caption" value="Health Care Equipment &amp; Supplies" display="always">Health Care Equipment &amp; Supplies</datapoint>
        </td>
        <td colname="2">
          <datapoint type="Regular" subtype="" name="PercentOfNetAssets" value="16.6" display="always">16.6</datapoint>
        </td>
      </tr>
      <tr type="categoryhead" level="1" itemtype="categoryhead" categorykey="623" hierarchykey="989">
        <td colname="1">
          <datapoint type="Literal" subtype="Custom" name="All Others*" value="All Others*" display="always">All Others*</datapoint>
        </td>
        <td colname="2">
          <datapoint type="Regular" subtype="" name="PercentOfNetAssets" value="1.6" display="always">[~PONT]1.6[#~PONT]</datapoint>
        </td>
      </tr>
    </tbody>
  </table>
</section>

I know I can isolate the numeric values by using something like:

<xsl:variable name="Value1" select="substring-before(substring-after(string(.),'[~PONT]'),'[#~PONT]')"/>

Unfortunately that's about all I do know. I apologise if this question seems rather vague, it's kind of difficult to explain, so please ask me for any more details. Thanks in advance for any advice. I am using XSLT version 1 by the way.

Upvotes: 0

Views: 1398

Answers (2)

matthias_h
matthias_h

Reputation: 11416

I'm not sure if this matches the requirements as they are a bit unspecific, but following XSLT

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:tab="http://composition.bowne.com/2010/v4" version="1.0">
 <xsl:output method="html" doctype-public="XSLT-compat" 
  omit-xml-declaration="yes" encoding="UTF-8" indent="yes" />
 <xsl:strip-space elements="*"/>
  <xsl:template match="/">
    <xsl:apply-templates/> 
  </xsl:template>
  <xsl:template match="@*|node()">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
  </xsl:template>
  <xsl:template match="tab:datapoint[parent::*/preceding-sibling::*/tab:datapoint[@value='All Others*']]/@value">
    <xsl:attribute name="value">
      <xsl:value-of select="sum(.) + 
          sum(//tab:tr[@type='otherassets']/tab:td[2]/tab:datapoint/@value)"/>
      </xsl:attribute>  
  </xsl:template>
  <xsl:template match="tab:datapoint[parent::*/preceding-sibling::*/tab:datapoint[@value='All Others*']]/text()">
    <xsl:text>[~PONT]</xsl:text>
    <xsl:value-of select="sum(./parent::*/@value) + sum(//tab:tr[@type='otherassets']/tab:td[2]/tab:datapoint/@value)"/>
    <xsl:text>[#~PONT]</xsl:text>
  </xsl:template>
  <xsl:template match="tab:tr[@type='otherassets']"/>
</xsl:transform>

when applied to the input XML in the question produces the output

<section xmlns="http://composition.bowne.com/2010/v4" name="Test" code="" type="Table" fundid="15" subtype="SOI1" style="">
 <table xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" accountperiod="2014-07-31" accountperiodtype="0" code="I2" name="Holdings" fundid="15" type="" cols="2">
  <colspec colnum="1" colname="1"></colspec>
  <colspec colnum="2" colname="2"></colspec>
  <tbody>
     <tr type="categoryhead" level="1" itemtype="categoryhead" categorykey="621" hierarchykey="989">
        <td colname="1">
           <datapoint type="Regular" subtype="" name="Caption" value="Health Care Equipment &amp; Supplies" display="always">Health Care Equipment &amp; Supplies</datapoint>
        </td>
        <td colname="2">
           <datapoint type="Regular" subtype="" name="PercentOfNetAssets" value="16.6" display="always">16.6</datapoint>
        </td>
     </tr>
     <tr type="categoryhead" level="1" itemtype="categoryhead" categorykey="623" hierarchykey="989">
        <td colname="1">
           <datapoint type="Literal" subtype="Custom" name="All Others*" value="All Others*" display="always">All Others*</datapoint>
        </td>
        <td colname="2">
           <datapoint type="Regular" subtype="" name="PercentOfNetAssets" value="1.6" display="always">[~PONT]1.6[#~PONT]</datapoint>     (Line A)

        </td>
     </tr>
  </tbody>
 </table>

For this transformation I added as example namespace in the XSLT for the additional namespace xmlns="http://composition.bowne.com/2010/v4" of the input XML

xmlns:tab="http://composition.bowne.com/2010/v4"

The template matching

<xsl:template match="tab:tr[@type='otherassets']"/>

is empty and removes this tr.

The template matching

<xsl:template match="tab:datapoint[parent::*/preceding-sibling::*
                    /tab:datapoint[@value='All Others*']]/@value">

changes the value of the value attribute to the sum of this value and the value of the datapoint of the tr[@type='otherassets'] :

<xsl:attribute name="value">
      <xsl:value-of select="sum(.) + 
          sum(//tab:tr[@type='otherassets']/tab:td[2]/tab:datapoint/@value)"/>
</xsl:attribute>

To change the text, the template matching the text() of this tr

<xsl:template match="tab:datapoint[parent::*/preceding-sibling::*
                    /tab:datapoint[@value='All Others*']]/text()">

doesn't use the substring-before() and substring-after() as suggested in the question, but instead the values of the value attributes of the datapoints to get the sum of both values: 

 <xsl:value-of select="sum(./parent::*/@value) + 
       sum(//tab:tr[@type='otherassets']/tab:td[2]/tab:datapoint/@value)"/>

This is based on the assumption that the value of the value attribute is the same as the value in the text between the [~PONT][#~PONT].

Update: For the question in the comment how this could be adjusted in case the text in the match patterns wouldn't be hardcoded but parameters:

Following XSLT

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:tab="http://composition.bowne.com/2010/v4" version="1.0">
<xsl:output method="html" doctype-public="XSLT-compat" 
 omit-xml-declaration="yes" encoding="UTF-8" indent="yes" />
 <xsl:strip-space elements="*"/>
  <xsl:param name="otherRemove" select="'otherassets'"/>
  <xsl:param name="otherKeep" select="'All Others*'"/>
  <xsl:template match="/">
    <xsl:apply-templates/> 
  </xsl:template>
  <xsl:template match="@*|node()">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
  </xsl:template>
  <xsl:template match="tab:datapoint[@name='PercentOfNetAssets']/@value">
    <xsl:choose>
      <xsl:when test="parent::tab:datapoint/parent::tab:td
                /preceding-sibling::*/tab:datapoint[@value=$otherKeep]">
        <xsl:attribute name="value">
          <xsl:value-of select="sum(.) + 
           sum(//tab:tr[@type=$otherRemove]/tab:td[2]/tab:datapoint/@value)"/>
        </xsl:attribute> 
      </xsl:when>
      <xsl:otherwise>
        <xsl:copy>
          <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
  <xsl:template match="tab:datapoint[@name='PercentOfNetAssets']/text()">
    <xsl:choose>
      <xsl:when test="parent::tab:datapoint/parent::tab:td
                /preceding-sibling::*/tab:datapoint[@value=$otherKeep]">
        <xsl:text>[~PONT]</xsl:text>
          <xsl:value-of select="sum(./parent::*/@value) + 
             sum(//tab:tr[@type=$otherRemove]/tab:td[2]/tab:datapoint/@value)"/>
        <xsl:text>[#~PONT]</xsl:text>
      </xsl:when>
      <xsl:otherwise>
        <xsl:copy>
          <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
  <xsl:template match="tab:tr">
    <xsl:choose>
       <xsl:when test="@type=$otherRemove"/>
       <xsl:otherwise>
         <xsl:copy>
           <xsl:apply-templates select="@*|node()"/>
         </xsl:copy>
       </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
</xsl:transform>

produces the same result.

As adjustments the two parameters

  <xsl:param name="otherRemove" select="'otherassets'"/>
  <xsl:param name="otherKeep" select="'All Others*'"/>

have been added.
To remove the row, now the template matching all tr

<xsl:template match="tab:tr">
  <xsl:choose>
    <xsl:when test="@type=$otherRemove"/>
    <xsl:otherwise>
      <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
    </xsl:otherwise>
  </xsl:choose>
</xsl:template>

copies all tr if they don't have the @type attribute value $otherRemove which is otherassets.
The templates matching text() and @value are adjusted in the same way: 

<xsl:template match="tab:datapoint[@name='PercentOfNetAssets']/text()">

and

<xsl:template match="tab:datapoint[@name='PercentOfNetAssets']/@value">

Both templates check in an <xsl:choose> 

<xsl:when test="parent::tab:datapoint/parent::tab:td
          /preceding-sibling::*/tab:datapoint[@value=$otherKeep]">

for the same condition that was the hardcoded template match pattern of the first version as $otherKeep is All Others*, adjusts the values of matching elements and copies all other text() and @value elements.

Upvotes: 2

kjhughes
kjhughes

Reputation: 111561

  1. Start with the identity transform so that by default all nodes are copied over to the output.
  2. Then add an override to suppress the tr you no longer want. In the example below, I keyed off of @itemtype='otherassets'.
  3. Then add an override for the datapoint to receive the tally. In the example below, I keyed off of categorykey, itemtype, and colname. You may wish to adapt/generalize per your full example, but this criteria works with your sample input and should give you a feel for what's needed.

This XSLT:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:b="http://composition.bowne.com/2010/v4">
  <xsl:template match="@*|node()">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="b:tr[@categorykey='623']/b:td[@colname='2']/b:datapoint">
    <xsl:variable name="v1"
                  select="substring-before(substring-after(string(.),'[~PONT]'),'[#~PONT]')"/>
    <xsl:variable name="otherdp"
                  select="../../../b:tr[@itemtype='otherassets']/b:td[@colname='2']/b:datapoint"/>
    <xsl:variable name="v2"
                  select="substring-before(substring-after(string($otherdp),'[~PONT]'),'[#~PONT]')"/>
    <xsl:copy>[~PONT]<xsl:value-of select="$v1 + $v2"/>[#~PONT]</xsl:copy>
  </xsl:template>

  <xsl:template match="b:tr[@itemtype='otherassets']"/>

</xsl:stylesheet>

Yields the desired XML output:

<?xml version="1.0" encoding="UTF-8"?><section xmlns="http://composition.bowne.com/2010/v4" name="Test" code="" type="Table" fundid="15" subtype="SOI1" style="">
  <table xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" accountperiod="2014-07-31" accountperiodtype="0" code="I2" name="Holdings" fundid="15" type="" cols="2">
    <colspec colnum="1" colname="1"/>
    <colspec colnum="2" colname="2"/>
    <tbody>
      <tr type="categoryhead" level="1" itemtype="categoryhead" categorykey="621" hierarchykey="989">
        <td colname="1">
          <datapoint type="Regular" subtype="" name="Caption" value="Health Care Equipment &amp; Supplies" display="always">Health Care Equipment &amp; Supplies</datapoint>
        </td>
        <td colname="2">
          <datapoint type="Regular" subtype="" name="PercentOfNetAssets" value="16.6" display="always">16.6</datapoint>
        </td>
      </tr>
      <tr type="categoryhead" level="1" itemtype="categoryhead" categorykey="623" hierarchykey="989">
        <td colname="1">
          <datapoint type="Literal" subtype="Custom" name="All Others*" value="All Others*" display="always">All Others*</datapoint>
        </td>
        <td colname="2">
          <datapoint>[~PONT]1.6[#~PONT]</datapoint>
        </td>
      </tr>

    </tbody>
  </table>
</section>

Upvotes: 1

Related Questions