CODEWITHSUNDEEP
xpathxslt-3.0
NealWalters
NealWalters

Reputation: 18227

XSLT preceding-sibling how-to get previous non-blank value

I receive a poorly designed CSV file that looks like this (overly simplified here):

enter image description here

BizTalk converts to XML, but the "header" values are missing on the "detail" rows. I need to create another file now where I "carry down" the Meter and State to the rows that have the volume data. The XML looks something like this.

<VendorFile xmlns="http://etc/VendorFile">
    <Row xmlns="">
        <Meter>123</Meter>
        <State>TX</State>
        <StartDate/>
        <Volume/>
    </Row>
    <Row xmlns="">
        <Meter></Meter>
        <State></State>
        <StartDate>1/1/2024</StartDate>
        <Volume>100</Volume>
    </Row>
    <Row xmlns="">
        <Meter></Meter>
        <State></State>
        <StartDate>1/2/2024</StartDate>
        <Volume>110</Volume>
    </Row>
    <Row xmlns="">
        <Meter></Meter>
        <State></State>
        <StartDate>1/3/2024</StartDate>
        <Volume>107</Volume>
    </Row>
    <Row xmlns="">
        <Meter>456</Meter>
        <State>OK</State>
        <StartDate/>
        <Volume/>
    </Row>
    <Row xmlns="">
        <Meter></Meter>
        <State></State>
        <StartDate>1/1/2024</StartDate>
        <Volume>200</Volume>
    </Row>
    <Row xmlns="">
        <Meter></Meter>
        <State></State>
        <StartDate>1/2/2024</StartDate>
        <Volume>205</Volume>
    </Row>
    <Row xmlns="">
        <Meter></Meter>
        <State></State>
        <StartDate>1/3/2024</StartDate>
        <Volume>210</Volume>
    </Row>
</VendorFile>

The desired output will look like this (but different field names because I'm mapping it to another internal canonical schema) and send to one of our third party software systems.

<VendorFile xmlns="http://etc/VendorFile">
    <Row xmlns="">
        <Meter>123</Meter>
        <State>TX</State>
        <StartDate>1/1/2024</StartDate>
        <Volume>100</Volume>
    </Row>
    <Row xmlns="">
        <Meter>123</Meter>
        <State>TX</State>
        <StartDate>1/2/2024</StartDate>
        <Volume>110</Volume>
    </Row>
    <Row xmlns="">
        <Meter>123</Meter>
        <State>TX</State>
        <StartDate>1/3/2024</StartDate>
        <Volume>107</Volume>
    </Row>
    <Row xmlns="">
        <Meter>456</Meter>
        <State>OK</State>
        <StartDate>1/1/2024</StartDate>
        <Volume>200</Volume>
    </Row>
    <Row xmlns="">
        <Meter>456</Meter>
        <State>OK</State>
        <StartDate>1/2/2024</StartDate>
        <Volume>205</Volume>
    </Row>
    <Row xmlns="">
        <Meter>456</Meter>
        <State>OK</State>
        <StartDate>1/3/2024</StartDate>
        <Volume>210</Volume>
    </Row>
</VendorFile>

My XSLT so far looks like this:

<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 s0" version="1.0" xmlns:ns0="http://etc/GasMeasurementDataCanonical" xmlns:s0="http://etc/VendorFile">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:template match="/">
    <xsl:apply-templates select="/s0:VendorFile" />
  </xsl:template>
  <xsl:template match="/s0:VendorFile">
    <ns0:GasMeasurementDataCanonical>
      <xsl:for-each select="Row[StartDate[text()!='']]">
        <Row>
          <Meter_Number>
            <xsl:value-of select="preceding-sibling::Meter[text()!='']" />
          </Meter_Number>
          <On_Date_Time>
            <xsl:value-of select="StartDate/text()" />
          </On_Date_Time>
     ...

For the Meter_Number - I want to first preceding sibling (going in backward order) where the Meter is not an empty string.

I start with this:

<xsl:for-each select="Row[StartDate[text()!='']]">

and that correctly pulls out the rows that have volume on them. Now I need to look back to get the Meter.

These are some of the variations I tried:

<xsl:value-of select="preceding-sibling::Meter[text()!='']" />
<xsl:value-of select="../preceding-sibling::Meter[text()!='']" />
<xsl:value-of select="/*preceding-sibling::Meter[text()!='']" />

Is it possible to do what I want, and how would I fix the select statement?

Upvotes: 0

Views: 45

Answers (2)

Martin Honnen
Martin Honnen

Reputation: 167716

I would suggest to use for-each-group group-starting-with e.g.

<xsl:for-each-group select="//Row" group-starting-with="Row[normalize-space(Meter) and normalize-space(State)]">
  <xsl:apply-templates select="tail(current-group())"/>
</xsl:for-each-group>

and then with

<xsl:template match="Row">
    <xsl:copy>
        <xsl:apply-templates select="current-group()[1]!(Meter, State), * except (Meter, State)"/>
    </xsl:copy>
</xsl:template>

you can transform each Row to include the Meter and State info from the "group header". I have used xsl:copy but you can of course use a literal result element to create whatever element you need to transform Row to.

Upvotes: 2

Michael Kay
Michael Kay

Reputation: 163595

The main reason it is failing is that your Row elements do not have a preceding-sibling called Meter. You want preceding-sibling::Row/Meter.

The predicate text()!='' is also wrong. If an element is empty then it has no text node; a text node itself is never zero-length. To test whether an element is empty, using the predicate [. != ''].

Upvotes: 2

Related Questions