how to transform XML?

Question

I'm trying to build an org chart, and I'm thinking I might try to get my data into tree structure like this D3.js example http://mbostock.github.io/d3/talk/20111018/tree.html

I have an input file that is generated from a query in an MS Access database. The XML looks like this...

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:noNamespaceSchemaLocation="Query1.xsd" generated="2015-06-25T15:35:33">
<Query1>
    <title>CEO</title>
    <reportsTo>1</reportsTo>
    <ID>1</ID>
</Query1>
<Query1>
    <title>Director of Operations</title>
    <reportsTo>1</reportsTo>
    <ID>2</ID>
</Query1>
<Query1>
    <title>Human Resources Manager</title>
    <reportsTo>2</reportsTo>
    <ID>3</ID>
</Query1>
</dataroot>

What I want is output that looks like this...

<?xml version="1.0" encoding="UTF-8"?>
<employees>
<employee title="CEO" reportsTo="1" id="1">
    <employee title="Director of Operations" reportsTo="1" id="2">
        <employee title="Human Resources Manager" reportsTo="2" id="3"></employee>
    </employee>
</employee>
</employees>

The important thing here is the relationships between the employees. I (think) I need the nesting of the elements. The output from the Access db is very flat, but it still has the staff ID numbers, and the "reportsTo" number that corresponds to each employees manager. If employee 2 reports to employee 1, then they should be nested under employee 1. (I've made a special case for the CEO who reports only to themselves).

Once I get this structure, it should be easy for me to write an xslt to go from my output XML to the JS input structure for D3.js. It should look like this...

var treeData = [
   {
    "name": "CEO",
    "parent": "null",
    "children": [
      {
        "name": "Director of Operations",
        "parent": "Top Level",
        "children": [
          {
            "name": "Human Resources Manager",
            "parent": "Level 2: A"
          }
       ]
      }
    ]
  }
];

Currently, I have written a Python script to read my input XML, and I'm currently working on getting it to generate my output XML.

But I think I'm missing something. Can I write an xslt to go straight from input xml to the output xml file? Or even straight from the input xml file right to the JS output?

Perhaps I've been thinking too much in terms of "code" solutions. How can i do this in xslt without using my python script?

Answer 1

First let's ignore the problem of cycles, and let's assume we know the root of the organisation tree is $boss (it might be the first employee, or the one with ID=1, or the one where reportsTo=ID).

Define a key

<xsl:key name="k" match="Query1" use="reportsTo"/>

And a template rule:

<xsl:template match="Query1">
  <employee title="{title}" reportsTo="{reportsTo}" ID="{ID}">
    <xsl:apply-templates select="key('k', ID)"/>
  </employee>
</xsl:template>

Now you can kick things off with

<xsl:apply-templates select="$boss"/>

The only remaining problem is that if you have cycles in your data, this will recurse indefinitely. You do have one cycle in your data: the CEO reports to herself. There may be other cycles as well. You could handle the CEO as a special case, or you could add logic to detect cycles in the general case; this is basically done by passing a parameter to the template giving the list of ancestor employees. But at this stage it starts to become a lot easier with XSLT 2.0 than with 1.0, and you haven't said which you are using.