Question

JSON with an empty property name in TimeXtender REST API 6.1

  • 30 April 2024
  • 7 replies
  • 90 views

Hello,

I am encountering a challenge with the REST connector when it deals with JSON data that includes empty property names. This issue arises specifically during the conversion of JSON to XML.

The REST connector fails when trying to convert JSON data that contains properties with empty names. Here is the error message I receive:

Failed to execute endpoint 'authenticate':
XmlNodeConverter cannot convert JSON with an empty property name to XML. Path 'TX_Autogenerated_Element.menu.categories.', line 1, position 94530.
 

This error stops the ingestion process because XML does not support tags with empty names, which are present in the JSON structure. This is the problematic part of the JSON structure:
{
    "categories": {
        "": {
            "collapsed": false,
            "description": "<No Category>",
            "id": ""
        },
        "BUD": {
            "collapsed": false,
            "description": "Budget",
            "id": "BUD"
        }
        // Additional categories
    }
}
 

In this JSON snippet, one of the categories has an empty string as a key, which leads to the mentioned error during conversion. Not a problem in JSON, but not allowed in XML.

There is no way for us to remove the category with an empty name from the endpoint, and the endpoint is needed in order to fetch a token.

Is this something that could be fixed in a future update? Is there a workaround?

Thanks!


7 replies

Userlevel 6
Badge +5

Hi @sigsol 

Our new feature we just released today may help with this issue. The guide is not released yet, but a short summary can be applied.

We have a new Table Builder tool added to our REST data source.

You will see it when you click on the marked area in the Table flattening section.

In there you can generate the XSLT document you need and it can handle empty fields.

First start by looking at this image.

When you have added some code in the input field and applied your fields, I used this https://jsonplaceholder.typicode.com/users for mine, you can do some right-click options.

Then you can apply a default value for the field.

Let me know if you can make this work.

@Thomas Lind Thanks! I will definitely check this out next week.

I’m hoping this will avoid TimeXtender trying to convert the problematic json property to xml in the step before flattening, since it is the creation of the unflattened xml file that fails currently.

 Hi @Thomas Lind 

Does the new feature work with TX 6590.1?

I first tried it out by excluding the part of the json that had an empty property. I was then able to choose fields and generate a XSLT. But when I run the synchronization in Desktop, I get the same error as above. I have activated caching to disk and the xml file “Data_” seems to be empty. Seems to me that the problem occurs prior to the flattening part.

I tried again including the problematic part. The selection tool then fails with this error:

“Error: Something bad happened; please try again later.”

 

Userlevel 6
Badge +5

Hi @sigsol 

I don’t think it would be about the version, as the XSLT part already existed before and that should be what is sent to the server.

Do you continue to get it now? It could be an unrelated thing, since it is so vague in the message.

If you copy the generated XSLT and move it to another document, then delete the part you made, create a new one and without using the table builder, imports the code does it still not work?

I tried again now, both with and without the empty property, and by copy-pasting as you suggested, with the same result.

I think the two errors are related. If you paste this json into the table builder, it works:
 

{
"menu": {
"categories": {
"BUD": {
"collapsed": false,
"description": "Budsjett",
"id": "BUD"
},
"UTVPROJ": {
"collapsed": false,
"description": "Utviklingsprosjekter",
"id": "UTVPROJ"
}
}
}
}

However, by including the blank category, it fails:

{
"menu": {
"categories": {
"": {
"collapsed": false,
"description": "<No Category> ",
"id": ""
},
"BUD": {
"collapsed": false,
"description": "Budsjett",
"id": "BUD"
},
"UTVPROJ": {
"collapsed": false,
"description": "Utviklingsprosjekter",
"id": "UTVPROJ"
}
}
}
}

 

And this makes sense considering the error message in TimeXtender:


XmlNodeConverter cannot convert JSON with an empty property name to XML. Path 'TX_Autogenerated_Element.menu.categories.', line 1, position 94530.

 

The empty property name is the “”-category.

Userlevel 6
Badge +5

Hi @sigsol 

You are right it does not work. The default value option is just that, a value replacement, there is no option to handle an empty name.

I tried to just type NULL where it had the empty field and then you can see the what gets generated.

XSLT

<?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/menu/categories" mode="rows"/>
</tx_root>
</xsl:template>
<xsl:template match="/TX_Autogenerated_Root/TX_Autogenerated_Element/menu/categories" mode="rows">
<test>
<xsl:element name="NULL_collapsed">
<xsl:value-of select="NULL/collapsed"/>
</xsl:element>
<xsl:element name="NULL_description">
<xsl:value-of select="NULL/description"/>
</xsl:element>
<xsl:element name="NULL_id">
<xsl:choose>
<xsl:when test="NULL/id != ''">
<xsl:value-of select="NULL/id" />
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="'1'" />
</xsl:otherwise>
</xsl:choose>
</xsl:element>
<xsl:element name="BUD_collapsed">
<xsl:value-of select="BUD/collapsed"/>
</xsl:element>
<xsl:element name="BUD_description">
<xsl:value-of select="BUD/description"/>
</xsl:element>
<xsl:element name="BUD_id">
<xsl:value-of select="BUD/id"/>
</xsl:element>
<xsl:element name="UTVPROJ_collapsed">
<xsl:value-of select="UTVPROJ/collapsed"/>
</xsl:element>
<xsl:element name="UTVPROJ_description">
<xsl:value-of select="UTVPROJ/description"/>
</xsl:element>
<xsl:element name="UTVPROJ_id">
<xsl:value-of select="UTVPROJ/id"/>
</xsl:element>
</test>
</xsl:template>
</xsl:stylesheet>

XML

<?xml version="1.0" encoding="utf-8"?>
<tx_root xmlns:xs="http://www.w3.org/2001/XMLSchema">
<test>
<NULL_collapsed>false</NULL_collapsed>
<NULL_description>&lt;No Category&gt; </NULL_description>
<NULL_id>1</NULL_id>
<BUD_collapsed>false</BUD_collapsed>
<BUD_description>Budsjett</BUD_description>
<BUD_id>BUD</BUD_id>
<UTVPROJ_collapsed>false</UTVPROJ_collapsed>
<UTVPROJ_description>Utviklingsprosjekter</UTVPROJ_description>
<UTVPROJ_id>UTVPROJ</UTVPROJ_id>
</test>
</tx_root>

How would this document know how to look for a category without a name. 

Hi @Thomas Lind 

Great, our initial guess was right. 

The thing is, this empty property name is not a problem in JSON. The REST connector converts the JSON into XML, where the empty property name is indeed a problem.

That suggests that the connector should handle this problem, right? Because (1) the empty value is perfectly fine in JSON and the connector is supposed to support JSON, and (2) we can’t choose whether or not to receive empty values from an external endpoint that we have no control over. In this case we are now unable to receive a token from the endpoint because of this lack of handling of the blank value case.

Reply