Home > database >  How to get dynamic columns and map Row Values to Column Header with XSLT 1.0
How to get dynamic columns and map Row Values to Column Header with XSLT 1.0

Time:09-26

I got a XML and need to create the below showed Table. As you can see, the Result in the Row needs to mapp the Column Header. And those Header Columns are dynamic (depending on the provided Time-Stamps)

Medi Einh Dosis 07:30 09:30 12:00 18:00 22:00
A Stk 1-0-1 1 0 1
B Stk 1-0-0-1 1 0 0 1

And the Source XML i got looks like this:

<XML_DATA>
  <MRO_MEDIKAMENT>
    <MEDI>A</MEDI>
    <EINH>Stk</EINH>
    <DOSIS>1-0-1</DOSIS>
    <EINZELDOSIS>
        <DOSIS>1</DOSIS>
        <ZEIT>07:30</ZEIT>
    </EINZELDOSIS>
    <EINZELDOSIS>
        <DOSIS>0</DOSIS>
        <ZEIT>12:00</ZEIT>
    </EINZELDOSIS>
    <EINZELDOSIS>
        <DOSIS>1</DOSIS>
        <ZEIT>18:00</ZEIT>
    </EINZELDOSIS>
  </MRO_MEDIKAMENT>
  <MRO_MEDIKAMENT>
    <MEDI>B</MEDI>
    <EINH>Stk</EINH>
    <DOSIS>1-0-0-1</DOSIS>
    <EINZELDOSIS>
        <DOSIS>1</DOSIS>
        <ZEIT>09:30</ZEIT>
    </EINZELDOSIS>
    <EINZELDOSIS>
        <DOSIS>0</DOSIS>
        <ZEIT>12:00</ZEIT>
    </EINZELDOSIS>
    <EINZELDOSIS>
        <DOSIS>0</DOSIS>
        <ZEIT>18:00</ZEIT>
    </EINZELDOSIS>
    <EINZELDOSIS>
        <DOSIS>1</DOSIS>
        <ZEIT>22:00</ZEIT>
    </EINZELDOSIS>
  </MRO_MEDIKAMENT>
</XML_DATA>

I have no idea how this dynamic can be achieved with xslt 1.0

CodePudding user response:

With XSLT 3 (supported these days on lots of platforms by Saxon HE 10 or 11, or SaxonJS 2):

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    exclude-result-prefixes="#all"
    expand-text="yes"
    version="3.0">
  
  <xsl:template match="XML_DATA">
    <table>
      <xsl:variable name="times" select="distinct-values(MRO_MEDIKAMENT/EINZELDOSIS/ZEIT) => sort()"/>
      <thead>
        <tr>
          <xsl:apply-templates select="*[1]/(* except EINZELDOSIS), $times" mode="th"/>
        </tr>
      </thead>
      <tbody>
        <xsl:apply-templates>
          <xsl:with-param name="times" select="$times" tunnel="yes"/>
        </xsl:apply-templates>
      </tbody>
    </table>
  </xsl:template>
  
  <xsl:template match="*" mode="th">
    <th>{local-name()}</th>
  </xsl:template>
  
  <xsl:template match="." mode="th">
    <th>{.}</th>
  </xsl:template>
  
  <xsl:template match="MRO_MEDIKAMENT">
    <xsl:param name="times" tunnel="yes"/>
    <tr>
      <xsl:apply-templates select="* except EINZELDOSIS, for $time in $times return (EINZELDOSIS[ZEIT = $time]/DOSIS, $time)[1]"/>
    </tr>
  </xsl:template>
  
  <xsl:template match="MRO_MEDIKAMENT//*">
    <td>{.}</td>
  </xsl:template>
  
  <xsl:template match=".[. instance of xs:untypedAtomic]">
    <td></td>
  </xsl:template>

  <xsl:mode on-no-match="shallow-copy"/>

  <xsl:output method="html" indent="yes" html-version="5"/>

  <xsl:template match="/">
    <html>
      <head>
        <title>Example</title>
      </head>
      <body>
        <xsl:apply-templates/>
      </body>
    </html>
  </xsl:template>
  
</xsl:stylesheet>

Transcribed to XSLT 1:

<xsl:stylesheet
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:exsl="http://exslt.org/common"
    xmlns:msxml="urn:schemas-microsoft-com:xslt"
    exclude-result-prefixes="exsl msxml"
    version="1.0">
  
  <xsl:key name="time" match="ZEIT" use="."/>
  
  <xsl:template match="XML_DATA">
    <table>
      <xsl:variable name="times-rtf">
        <xsl:for-each select="MRO_MEDIKAMENT/EINZELDOSIS/ZEIT[generate-id() = generate-id(key('time', .)[1])]">
          <xsl:sort select="."/>
          <xsl:copy-of select="."/>
        </xsl:for-each>
      </xsl:variable>
      <xsl:variable name="times" select="exsl:node-set($times-rtf)/*"/>
      <thead>
        <tr>
          <xsl:apply-templates select="*[1]/*[not(self::EINZELDOSIS)] | $times" mode="th"/>
        </tr>
      </thead>
      <tbody>
        <xsl:apply-templates>
          <xsl:with-param name="times" select="$times"/>
        </xsl:apply-templates>
      </tbody>
    </table>
  </xsl:template>
  
  <xsl:template match="*" mode="th">
    <th>
      <xsl:value-of select="local-name()"/>
    </th>
  </xsl:template>
  
  <xsl:template match="ZEIT" mode="th">
    <th>
      <xsl:value-of select="."/>
    </th>
  </xsl:template>
  
  <xsl:template match="MRO_MEDIKAMENT">
    <xsl:param name="times"/>
    <tr>
      <xsl:apply-templates select="*[not(self::EINZELDOSIS)]"/>
      <xsl:apply-templates select="$times">
        <xsl:with-param name="med" select="current()"/>
      </xsl:apply-templates>
    </tr>
  </xsl:template>
  
  <xsl:template match="MRO_MEDIKAMENT//*">
    <td>
      <xsl:value-of select="."/>
    </td>
  </xsl:template>
  
  <xsl:template match="ZEIT">
    <xsl:param name="med"/>
    <td>
      <xsl:value-of select="$med/EINZELDOSIS[ZEIT = current()]/DOSIS"/>
    </td>
  </xsl:template>

  <xsl:output method="html" indent="yes" version="5" doctype-system="about:legacy-doctype"/>

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

  <xsl:template match="/">
    <html>
      <head>
        <title>Example</title>
      </head>
      <body>
        <xsl:apply-templates/>
      </body>
    </html>
  </xsl:template>

</xsl:stylesheet>

CodePudding user response:

I would do it this way:

XSLT 1.0

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:exsl="http://exslt.org/common"
extension-element-prefixes="exsl">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:key name="data-by-time" match="EINZELDOSIS" use="ZEIT" />
<xsl:key name="data-by-cell" match="EINZELDOSIS" use="concat(ZEIT, '|', generate-id(..))" />

<xsl:variable name="distinct-times-RTF">
        <xsl:for-each select="/XML_DATA/MRO_MEDIKAMENT/EINZELDOSIS[count(. | key('data-by-time', ZEIT)[1]) = 1]">
        <xsl:sort select="ZEIT" data-type="text" order="ascending"/>
            <xsl:copy-of select="ZEIT"/>
        </xsl:for-each>
</xsl:variable>

<xsl:variable name="distinct-times" select="exsl:node-set($distinct-times-RTF)/ZEIT" />

<xsl:variable name="source_document" select="/" />

<xsl:template match="/XML_DATA">
    <table border="1">
        <thead>
            <tr>
                <th>Medi</th>
                <th>Einh</th>
                <th>Dosis</th>
                <!-- a column header for each distinct time point -->
                <xsl:for-each select="$distinct-times">
                    <th>
                        <xsl:value-of select="."/>
                    </th>
                </xsl:for-each>
            </tr>
        </thead>
        <tbody>
            <xsl:for-each select="MRO_MEDIKAMENT">
                <xsl:variable name="row-id" select="generate-id()" />
                <tr>
                    <td>
                        <xsl:value-of select="MEDI"/>
                    </td>
                    <td>
                        <xsl:value-of select="EINH"/>
                    </td>
                    <td>
                        <xsl:value-of select="DOSIS"/>
                    </td>
                    <!-- create a cell for each distinct time point -->
                    <xsl:for-each select="$distinct-times">
                        <xsl:variable name="zeit" select="." />
                        <td>
                            <!-- get matching data point -->
                            <!-- switch the context back to the source document -->
                            <xsl:for-each select="$source_document">
                                <xsl:value-of select="key('data-by-cell', concat($zeit, '|', $row-id))/DOSIS" />
                            </xsl:for-each>
                        </td>
                    </xsl:for-each>
                </tr>
            </xsl:for-each>
        </tbody>
    </table>
</xsl:template>

</xsl:stylesheet>

This performs Muenchian grouping of all provided time points and sorts the results. Next, a column header is created for each distinct time point. Then, in each row we create a cell for each distinct time point and populate it with the data from the corresponding intersection of the current row and the current time-point.

CodePudding user response:

Distributing sparse attribute values into columns can be solved with XSLT 1.0 and a few EXSLT functions (), here using . Assuming a POSIX shell, indentation and line continuation characters added for readability.

xmlstarlet select --text \
  --template \
  --var ofs -o "$(printf '\t')" -b \
  --var ors -n -b \
  --var zeiten_rtf \
    -m 'set:distinct(//EINZELDOSIS/ZEIT)' \
      -s 'A:T:-' '.' \
      -e punkt -v '.' -b \
    -b \
  -b \
  --var zeiten='exslt:node-set($zeiten_rtf)/punkt' \
  -m 'str:split("Medi Einh Dosis") | $zeiten' \
    --if 'position() != 1' -v '$ofs' -b \
    -v '.' \
  -b \
  -v '$ors' \
  -m '/*/MRO_MEDIKAMENT' \
    --var med='.' \
    -v 'concat(MEDI,$ofs,EINH,$ofs,DOSIS)' \
    -m '$zeiten' \
      -v 'concat($ofs, $med/EINZELDOSIS[ ZEIT=current() ]/DOSIS)' \
    -b \
    -v '$ors' \
  -b \
file.xml
  • the ofs and ors variables hold output field and record separators, respectively (tab and newline)
  • all unique time-of-day values are extracted (using EXSLT function set:distinct), sorted, and stored in a result tree fragment (RTF), then coerced into a nodeset using EXSLT's node-set() function
  • the header line is lists fixed fields (using str:split) and all time-of-day values
  • items are iterated using straight XSLT 1.0, an empty string is output if no time-of-day value exists in a column
  • options: -o is xsl:text, -n prints a newline, -m is xsl:for-each, -s is xsl:sort, -v is xsl:value-of (when used with a single node), -e is xsl:element, --if / --elif is xsl:when, -b marks end of block

Output (when piped through expand -t 8):

Medi    Einh    Dosis   07:30   09:30   12:00   18:00   22:00
A       Stk     1-0-1   1               0       1       
B       Stk     1-0-0-1         1       0       0       1

Adding a -C option before --template lists the generated XSLT stylesheet without running it:

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:set="http://exslt.org/sets" xmlns:exslt="http://exslt.org/common" xmlns:str="http://exslt.org/strings" version="1.0" extension-element-prefixes="exslt set str">
  <xsl:output omit-xml-declaration="yes" indent="no" method="text"/>
  <xsl:template match="/">
    <xsl:variable name="ofs">
      <xsl:text>    </xsl:text>
    </xsl:variable>
    <xsl:variable name="ors">
      <xsl:value-of select="'&#10;'"/>
    </xsl:variable>
    <xsl:variable name="zeiten_rtf">
      <xsl:for-each select="set:distinct(//EINZELDOSIS/ZEIT)">
        <xsl:sort order="ascending" data-type="text" select="."/>
        <xsl:element name="punkt">
          <xsl:call-template name="value-of-template">
            <xsl:with-param name="select" select="."/>
          </xsl:call-template>
        </xsl:element>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable select="exslt:node-set($zeiten_rtf)/punkt" name="zeiten"/>
    <xsl:for-each select="str:split(&quot;Medi Einh Dosis&quot;) | $zeiten">
      <xsl:choose>
        <xsl:when test="position() != 1">
          <xsl:call-template name="value-of-template">
            <xsl:with-param name="select" select="$ofs"/>
          </xsl:call-template>
        </xsl:when>
      </xsl:choose>
      <xsl:call-template name="value-of-template">
        <xsl:with-param name="select" select="."/>
      </xsl:call-template>
    </xsl:for-each>
    <xsl:call-template name="value-of-template">
      <xsl:with-param name="select" select="$ors"/>
    </xsl:call-template>
    <xsl:for-each select="/*/MRO_MEDIKAMENT">
      <xsl:variable select="." name="med"/>
      <xsl:call-template name="value-of-template">
        <xsl:with-param name="select" select="concat(MEDI,$ofs,EINH,$ofs,DOSIS)"/>
      </xsl:call-template>
      <xsl:for-each select="$zeiten">
        <xsl:call-template name="value-of-template">
          <xsl:with-param name="select" select="concat($ofs, $med/EINZELDOSIS[ ZEIT=current() ]/DOSIS)"/>
        </xsl:call-template>
      </xsl:for-each>
      <xsl:call-template name="value-of-template">
        <xsl:with-param name="select" select="$ors"/>
      </xsl:call-template>
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="value-of-template">
    <xsl:param name="select"/>
    <xsl:value-of select="$select"/>
    <xsl:for-each select="exslt:node-set($select)[position()&gt;1]">
      <xsl:value-of select="'&#10;'"/>
      <xsl:value-of select="."/>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>
  • Related