XML Data Question

OL Connect Professional DataMapper
1

I have some XML data where the second element is defines each row, but multiple rows belong to a document. There isn’t any organizing element between the rows for multiple documents are all organized on the same level

Looking for ideas.

2020-07-01_16h47_32
2020-07-01_16h47_32885×777 93.4 KB

2

You’d think there’d be an easy XPATH statement to do this, but it turns out to be quite the challenge … which I wasn’t able to solve, unfortunately

However, you can write an XSLT transform and apply it in Workflow before sending the data file to the DataMapper:

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

    <Start>

    <xsl:for-each select="//Start/row[not(reference = preceding-sibling::row/reference)]">
      <xsl:variable name="current" select="reference" />
      <record>

      <xsl:apply-templates select="."/>

      <xsl:for-each select="following-sibling::row[reference[text()=$current]]">
        <xsl:apply-templates select="."/>
      </xsl:for-each>

      </record>

    </xsl:for-each>

    </Start>

  </xsl:template>
  <xsl:template match="@*|node()">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
  </xsl:template>
</xsl:stylesheet>

This code wraps all related elements inside a new <record> element, which then makes it easy for you to set your record boundaries in the DataMapper.

1 Like
3

At first it didn’t seem to work, but then I noticed your XSLT script had the node REFERENCE as lowercase reference.

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

    <Start>

    <xsl:for-each select="//Start/row[not(REFERENCE = preceding-sibling::row/REFERENCE)]">
      <xsl:variable name="current" select="REFERENCE" />
      <record>

      <xsl:apply-templates select="."/>

      <xsl:for-each select="following-sibling::row[REFERENCE[text()=$current]]">
        <xsl:apply-templates select="."/>
      </xsl:for-each>

      </record>

    </xsl:for-each>

    </Start>

  </xsl:template>
  <xsl:template match="@*|node()">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
  </xsl:template>
</xsl:stylesheet>

Thanks, this looks much more manageable in the data mapper!

2020-07-02_10h41_53
2020-07-02_10h41_53578×826 62.8 KB

1 Like
4

If I needed to sort the records by the REFERENCE number, where would be the best/easiest place to do this?

5

You could use another XSLT, after the one you already implemented. Something like the following should work:

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

<xsl:template match="node()|@*">
    <xsl:copy>
        <xsl:apply-templates select="node()|@*"/>
    </xsl:copy>
</xsl:template>

<xsl:template match="/Start">
    <xsl:copy>
        <xsl:apply-templates select="record">
            <xsl:sort select="./row/REFERENCE" data-type="number"/>
        </xsl:apply-templates>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>
1 Like
6

Thanks, I need to learn more about XSLT