How can I extract xml data to create customized html tables

Question

I hope I can describe the problem in a understandable way:

I have a big XML file with more than 1000 entries which must be displayed in a HTML file. The entries (lets say employee names) are grouped in different sections (departments). In the HTML file I have to create a table for each department and list all employees with some attributes.

The XML structure is like this:

<section name="department_A">
  <entry name="john doe" gender="m" age="20"></entry>
  <entry name="luke smith" gender="m" age="34"></entry>
</section>
<section name="department_B">
  <entry name="jane doe" gender="f" age="25"></entry>
  <entry name="ben b" gender="m" age="43"></entry>
</section>
<section name="department_A and department_B">
  <entry name="john doe" gender="m" age="20"></entry>
  <entry name="nick fury" gender="m" age="53"></entry>
</section>
<section name="different departments">
  <entry name="ben b" gender="m" age="27">
     <desciption>Belongs to dep_B and dep_C</description>
  </entry>
  <entry name="chris h" gender="m" age="33">
     <description>Belongs to dep_C and dep_F</description>
  </entry>
</section>

In the HTML file, each section name is displayed as a heading, each entry is a table row and each attribute is a table column. The following xls file is used for this:

<xsl:for-each select="section">

  <h1><a name="{@name}"> [<xsl:value-of select="@name"/>] </a></h1>

  <table>
  <tr>
    <th>Name</th>
    <th>Gender</th>
    <th>Age</th>
  </tr>

  <xsl:for-each select="entry">
  <tr>
    <td><xsl:value-of select="@name"/></td>
    <td><xsl:value-of select="@gender"/></td>
    <td><xsl:value-of select="@age"/></td>
  </tr>
  </xsl:for-each>

Till now the XML and XSL files worked fine. However, the problem is that I have to create html tables a little more "independent" from the XML structure, since some entries must be listed in several tables.

For example, the employee "john doe" only occurs in section="department_A", but it must be displayed in the table for department_A, department_B and department_C. Is there any way to do this without copying each entry to all regarding sections?

My approach was to add another attribute to the regarding entry (like entry name="john doe" gender="m" age="20" dep="department_B,department_C"). Is it possible to create the table for a section, fill in all entries of the section (e.g. of department_B) and then browse all entries in the entire document, check if there are entries with attribute "dep" which contains the name equal to the section name and append this entry to the table?

Or is there any other way to do this (without xls)? I hope the description is sufficient. Thanks for the help!

What determines in which (additional) departments the employee should appear? You speak like someone who has control over the format of the input XML. If you do, then I suggest you use multiple department elements, instead of a single attribute with multiple substring tokens.. — michael.hor257k
– michael.hor257k, Commented Jul 27, 2016 at 10:35
There are different department elements already. Someone added sections like "dep_A & dep_B", "dep_A & dep_C".. However, this is no comprehensive view since there are more than 30 section plus the "combined sections". .. You look at all employees of dep_A but you won't know if this are really all names, unless you take a look at all the other tables too. Furthermore, if you have to change something you have to do this for every instance (since the names with all attributes occur several times). Therefore I want to delete the copies and only keep one entry for each employee. — kronk
– kronk, Commented Jul 27, 2016 at 10:58
Well, do you have control over the input XML format? Ideally, departments would be listed separately from employees, and each employee would have a department element for each department he/she belongs to (same as in a relational DB). If you cannot change the format, then edit your question and show an example of the format that you have to work with. -- P.S. "dep_A & dep_B" are not different department elements. — michael.hor257k
– michael.hor257k, Commented Jul 27, 2016 at 11:02
Yes, I have control over the input XML. However, like I mentioned before, there are so many entries that I'd prefer if I won't have to change the entire XML. There is a example of the format in my question - just sections with a lot of entries. — kronk
– kronk, Commented Jul 27, 2016 at 11:30
"there are so many entries that I'd prefer if I won't have to change the entire XML" How are you changing the XML? Hopefully not manually? I thought this was an output from a DB or something similar. If not, then my original question still stands: what determines in which (additional) departments the employee should appear? I don't see it listed in your XML, so where is the additional information coming from? -- "There is a example of the format in my question" No, there isn't. There isn't a single employee with more than one department. — michael.hor257k
– michael.hor257k, Commented Jul 27, 2016 at 11:39

michael.hor257k · Accepted Answer · 2016-07-27 13:43:59Z

I suggest you try this as your starting point:

XSLT 1.0 (+ EXSLT)

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

<xsl:key name="emp" match="person" use="all-attributes" />

<xsl:template match="/root">
    <xsl:variable name="people-rtf">
        <xsl:for-each select="section/entry">
            <person>
                <xsl:copy-of select="@*"/>
                <all-attributes>
                    <xsl:for-each select="@*">
                        <xsl:value-of select="."/>
                        <xsl:text>|</xsl:text>
                    </xsl:for-each>
                </all-attributes>
                <xsl:choose>
                    <xsl:when test="../@name='different departments'">
                        <xsl:for-each select="str:split(substring-after(description, 'Belongs to '), ' and ')">
                            <section>
                                <xsl:value-of select="."/>
                            </section>
                        </xsl:for-each>
                    </xsl:when>
                    <xsl:otherwise>
                        <xsl:for-each select="str:split(../@name, ' and ')">
                            <section>
                                <xsl:value-of select="."/>
                            </section>
                        </xsl:for-each>
                    </xsl:otherwise>
                </xsl:choose>
            </person>
        </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="people" select="exsl:node-set($people-rtf)/person" />
    <xsl:copy>
        <sections>
            <xsl:copy-of select="set:distinct($people/section)"/>
        </sections>
        <employees>
            <xsl:for-each select="set:distinct($people/all-attributes)">
                <employee>
                    <xsl:copy-of select="../@*"/>
                    <xsl:copy-of select="set:distinct(key('emp', .)/section)"/>
                </employee>
            </xsl:for-each>
        </employees>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>

Applied to the following input:

XML

<root>
    <section name="department_A">
      <entry name="john doe" gender="m" age="20"></entry>
      <entry name="luke smith" gender="m" age="34"></entry>
    </section>
    <section name="department_B">
      <entry name="jane doe" gender="f" age="25"></entry>
      <entry name="ben b" gender="m" age="43"></entry>
    </section>
    <section name="department_A and department_B">
      <entry name="john doe" gender="m" age="20"></entry>
      <entry name="nick fury" gender="m" age="53"></entry>
    </section>
    <section name="different departments">
      <entry name="ben b" gender="m" age="43">
         <description>Belongs to dep_B and dep_C</description>
      </entry>
      <entry name="chris h" gender="m" age="33">
         <description>Belongs to dep_C and dep_F</description>
      </entry>
    </section>
</root>

the result will be:

<?xml version="1.0" encoding="UTF-8"?>
<root>
  <sections>
    <section>department_A</section>
    <section>department_B</section>
    <section>dep_B</section>
    <section>dep_C</section>
    <section>dep_F</section>
  </sections>
  <employees>
    <employee name="john doe" gender="m" age="20">
      <section>department_A</section>
      <section>department_B</section>
    </employee>
    <employee name="luke smith" gender="m" age="34">
      <section>department_A</section>
    </employee>
    <employee name="jane doe" gender="f" age="25">
      <section>department_B</section>
    </employee>
    <employee name="ben b" gender="m" age="43">
      <section>department_B</section>
      <section>dep_B</section>
      <section>dep_C</section>
    </employee>
    <employee name="nick fury" gender="m" age="53">
      <section>department_A</section>
      <section>department_B</section>
    </employee>
    <employee name="chris h" gender="m" age="33">
      <section>dep_C</section>
      <section>dep_F</section>
    </employee>
  </employees>
</root>

Collectives™ on Stack Overflow

How can I extract xml data to create customized html tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related