Skip to main content
To homepage To homepage

The Table builder is a powerful tool allowing users to transform nested XML or JSON data into a flattened table format with columns and rows by using simple drag-and-drop functionality. This guide will walk you through the various features and steps involved in using the Table builder effectively.

Prerequisites

  1. Create a TimeXtender REST API data source, provide a name for the data source, and add a Base URL. For example by using the following https://jsonplaceholder.typicode.com/users as the source. Add https://jsonplaceholder.typicode.com/ as the Base URL
  2. Add an endpoint. Using https://jsonplaceholder.typicode.com/users it will be users as Path and Users as Name for the endpoint.

     

Instructions

Here follows a step-by-step guide to how you add and use the table builder to create a table flattening XSLT.

Add table flattening

  1. Within your REST API endpoint, click “Add”  under Table flattening

     

  2. Give your Table flattening a name (this will be your resulting flattened table name) and click the “open” button to open the table builder interface

    1. The table builder  interface consists of 5 different sections:
      1. Input, this is where you paste in the XML or JSON you want to flatten and also select the applicable data structure for your input data
      2. Nodes in table, this is where the schema for your input data is displayed
      3. Root, this is where in the schema you want to iterate over your data records
      4. Columns, this is where your selected nodes are dropped from the schema to become the columns in the resulting table
      5. Output/XSLT, this is where you can toggle between seeing the result of your input data transformed with the generated XSLT and you can also inspect the generated XSLT
  3. Retrieve a JSON or XML output from your REST API source
    , and paste the output you wish to flatten in the “Input” box.

     

  4. Click the “Get schema” button to retrieve the schema for the JSON or XML in the Input box.

     

Understanding Root manipulation

The root XPath defines where in the source document your data records iterate. If your data records contain multiple levels of iteration, for example, if your “Employee” records also contain a list of “Phone numbers” for each employee, you can set your root to the XPath of the phone numbers nested iteration level and the table builder will repeat the outside data for each iteration of the root iteration.

Example:

<
  {
    “Employee Name”: “John Doe”,
   “Phone numbers”: a
     999 00 999,
     666 00 666
   ]
  },{…
]

Setting the root to “Phone numbers” in the above example will result in the following records (if “Employee Name” and “Phone numbers” is selected as columns:

d
  { “John Doe”, 999 00 999 },
  { “John Doe”, 666 00 666 }

]

It is important to set the root to the correct level to achieve the desirable transformation. Also, it is useful to set this before you drag and drop nodes into the Column section as the automatic column names are generated relative to the root in the schema hierarchy.

Selecting columns for table creation

  1. Drag and drop nodes from the “Nodes in table” section to choose the columns for your flattened table. Dragging a “parent” node will add all child leaf nodes to the table.

     

  2. Apply data transformations on the selected nodes in the Columns section by right-clicking on the nodes and selecting:
    1. Delete: to remove the node from the selection
    2. Edit name: to edit the name of the column in the resulting table

       

    3. Set replace value: a simple string replacement transformation on the values in the column

       

    4. Set default value: a default value when the column value is a null or empty string.

       

  3. By clicking “Add static node” you can add a static column with an appropriate value to your flattened table. Here it is also worth mentioning that it is also possible to use Dynamic values within all table builder transformations to inject a dynamic value into the table.

If you want to try to change the root with a real example look at the added JSON examples.zip file. It contains a file called extraCompany.json which can show how to apply an alternate root node to get a different result like this. It is based on the jsonplaceholder source with some additions.

l
    {
        "id": 1,
        "name": "Leanne Graham",
        "username": "Bret",
        "email": "Sincere@april.biz",
        "address": {
            "street": "Kulas Light",
            "suite": "Apt. 556",
            "city": "Gwenborough",
            "zipcode": "92998-3874",
            "geo": {
                "lat": "-37.3159",
                "lng": "81.1496"
            }
        },
        "phone": "1-770-736-8031 x56442",
        "website": "hildegard.org",
        "company":  
            {
                "name": "Romaguera-Crona",
                "catchPhrase": "Multi-layered client-server neural-net",
                "bs": "harness real-time e-markets"
            },
            {
                "name": "Another company",
                "catchPhrase": "The better company",
                "bs": "do stuff better than others"
            }
        ]
    },
    {
        "id": 2,
        "name": "Ervin Howell",
        "username": "Antonette",
        "email": "Shanna@melissa.tv",
        "address": {
            "street": "Victor Plains",
            "suite": "Suite 879",
            "city": "Wisokyburgh",
            "zipcode": "90566-7771",
            "geo": {
                "lat": "-43.9509",
                "lng": "-34.4618"
            }
        },
        "phone": "010-692-6593 x09125",
        "website": "anastasia.net",
        "company": b
            {
                "name": "Deckow-Crist",
                "catchPhrase": "Proactive didactic contingency",
                "bs": "synergize scalable supply-chains"
            },
            {
                "name": "Second company",
                "catchPhrase": "Get shit done",
                "bs": "eventually"
            }
        ]
    }
]

If you add this to the input area and keep the setup as shown in the above example, it will only return the first company name. If you drag company as the root node it will show all companies for all users.

Before doing the change:

After adding company as the root node:

Working with different data structures

Currently, there are two options of data structures available in the table builder. The most common one is “Generic”. This is when your data structure is verbose, meaning that data points appear as nodes or objects in the data structure.

Example:

 { “My data”:
  s
    { “My datapoint”: “My value1”, “My datapoint2”: “My value1” },
    { “My datapoint”: “My value2”, “My datapoint2”: “My value2”  },
     …
  ]
}

The other data structure option currently available is “Column”. This is when your datapoints reside within arrays or said differently each datapoint contains the values of the entire column in one array.

Example:

{ “My data”:
  { “My datapoint”: i“My value1”, “My value2”, …] } ,
  { “My datapoint2”: c“My value1”, “My value2”, …] },
   …
}

When working with “Column” based data structures it is important to know that the root will behave differently. Instead of setting where your data iterates, you need to set the root to the parent node of the nodes containing the arrays/columns of data. In the example above, setting the root to “My data” will result in a table containing two columns “My datapoint” and “My datapoint2”.

For column structure to work properly the corresponding arrays of data must be the same length.

Using the other file in the attached JSON examples.zip file called columnBased.json you can try out how to use the Column option.

Start by applying the content from the file in the input field, change the data structure to column, click Get schema and expand the nodes.

The data node is what you drag into the Root and times and one of the series option, in my example max, is what you drag into the columns area.

The output XSLT data will look as follows.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:ext="http://exslt.org/common">
    <xsl:output method="xml" indent="yes" />




    <xsl:key name="k" match="colbnot(@static)]" use="@pos"/>

    <xsl:template match="/">
        <xsl:variable name="flatten">
            <table>
                <xsl:apply-templates select="/TX_Autogenerated_Root/TX_Autogenerated_Element/result/data" mode="rows"/>
            </table>
        </xsl:variable>

        <tx_root>
            <xsl:for-each select="ext:node-set($flatten)/table/colucount(. | key('k', @pos)l1]) = 1]">
                <users>
                    <xsl:for-each select="key('k', @pos)">
                        <xsl:element name="{@colName}">
                            <xsl:value-of select="."/>
                        </xsl:element>
                    </xsl:for-each>
                    <xsl:apply-templates select="/table/col/@static]"/>

                </users>
            </xsl:for-each>
        </tx_root>
    </xsl:template>

    <xsl:template match="/table/cols@static]">
        <xsl:element name="{@colName}">
            <xsl:value-of select="." />
        </xsl:element>
    </xsl:template>

    <xsl:template match="/TX_Autogenerated_Root/TX_Autogenerated_Element/result/data" mode="rows">
        <xsl:apply-templates select="times" mode="times"/>
<xsl:apply-templates select="series/max" mode="series_max"/>
    </xsl:template>

    <xsl:template match="times" mode="times">
    <xsl:element name="col">

        <xsl:attribute name="colName">times</xsl:attribute>
        <xsl:attribute name="pos"><xsl:value-of select="position()"/></xsl:attribute>
        <xsl:value-of select="." />
    </xsl:element>
</xsl:template>
<xsl:template match="series/max" mode="series_max">
    <xsl:element name="col">

        <xsl:attribute name="colName">series_max</xsl:attribute>
        <xsl:attribute name="pos"><xsl:value-of select="position()"/></xsl:attribute>
        <xsl:value-of select="." />
    </xsl:element>
</xsl:template>

</xsl:stylesheet>

If it was set up as generic it would look like this.

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



    <xsl:template match="/">
        <tx_root>
            <xsl:apply-templates select="/TX_Autogenerated_Root/TX_Autogenerated_Element/result/data" mode="rows"/>
        </tx_root>
    </xsl:template>
    <xsl:template match="/TX_Autogenerated_Root/TX_Autogenerated_Element/result/data" mode="rows">
    <users>
        <xsl:element name="times">
    <xsl:value-of select="times"/>
</xsl:element>
<xsl:element name="series_max">
    <xsl:value-of select="series/max"/>
</xsl:element>
    </users>
</xsl:template>
</xsl:stylesheet>

You can see how it points at the columns using a for_each loop compared to how it looks when it is set to generic.

Saving and recalling settings

  1. After selecting columns and configuring your table, remember to save your settings. The resulting XSLT will be saved along with the selected nodes in the Column section as well as the root XPath. Note that when you save you will overwrite the existing XSLT.

     

  2. Upon reopening the Table builder, your previously selected root and column nodes will be retained, but you will need to provide new data in the Input section and click “Get schema” to get Nodes in the table. If your loaded nodes in the Column section do not have a matching XPath in the Nodes in the table schema section, it will be indicated with a red icon. This way you can easily detect schema drift.

     

 

Be the first to reply!

Reply


Terms and ConditionsCookie settings