Articles in this section

See more

Serialize data set into specific Format (JSON, XML, etc.)

  • Updated
Follow

Overview

In most cases, you will be using source-to-destination transformation to transform any data source to any Format. However, there are cases when your source is already a DataSet, and you want to convert it to the JSON or XML string in one simple call. For example:

  • When the source is a nested document that you want to flatten and load into the database table but, at the same time, you want to preserve some nested node as-is without transforming it and save it as a field. 
  • When the source is a nested document that you want to transform into another nested document in the same or different Format but want to keep some of the nested elements as-is.

Also, read how to do the opposite — convert string in any Format into the data set.

Serialize data set using JavaScript

You can use the following code to convert a DataSet into the string in the specific data exchange Format using ConnectorUtils#dataSet2Str(...). 

var str = com.toolsverse.etl.connector.ConnectorUtils.dataSet2Str(dataSet, 
'fully.qualified.connector.class.name', parameters);

Available connectors

  • JSON: 'com.toolsverse.etl.connector.json.JsonConnector'
  • XML: 'com.toolsverse.etl.connector.xml.XmlObjectConnector'
  • CSV: 'com.toolsverse.etl.connector.text.TextConnector'

Parameters

The parameters are either a key=value;key=value string or null, which means that the default values for the parameters will be used.

To JSON

Available parameters

Parameter Description Possible values Default value
start The type of the start element
  • array
  • object
  • namedobject
  • variable
 array
rootname The name of the named root object any string null
nested The type of nested elements
  • array
  • object
  • variable
 array
forsql Convert column names to SQL-compatible false | true false

 

Example

var jsonStr = com.toolsverse.etl.connector.ConnectorUtils.dataSet2Str(dataSet, 
'com.toolsverse.etl.connector.json.JsonConnector', 
'start=variable;nested=variable');

To XML

Available parameters

Parameter Description Possible values Default value
rootname The name of the root element any string automatic
namespace The namespace valid namespace null
xmlversion XML version 1.0 | 1.1 1.0
rowtag Tag for repeating elements Valid XML tag name automatic
parseattrs Parse XML attributes false | true false
parsecdata Parse XML attributes in root node true | false true
parserootattrs Parse values in CDATA tag true | false true
forsql
 Convert column names to SQL-compatible true | false false

 

Example

var xmlStr = com.toolsverse.etl.connector.ConnectorUtils.dataSet2Str(dataSet, 
'com.toolsverse.etl.connector.xml.XmlObjectConnector', 
'rootname=products;rowtag=product');

To CSV

Available parameters

Parameter Description Possible values Default value
delimiter Field delimiter any valid character ,
lineseparator  Line separator
  • u - Unix
  • w- Windows
  • s - Automatic
 s
charseparator Enclosure character any valid character "
alwaysquote Always enclose fields false | true false
escapequote Escape double-quotes false | true false
forsql Convert column names to SQL-compatible false | true false

 

Example

var csvStr = com.toolsverse.etl.connector.ConnectorUtils.dataSet2Str(dataSet, 
'com.toolsverse.etl.connector.text.TextConnector', 
'alwaysquote=true;escapequote=true');

Serialize data set inside SELECT SQL statement

In Etlworks, it is possible to execute SQL against any data object: file, the response from a web service, etc.

You can use the following code to convert a DataSet available inside the SELECT statement into the string in the specific data exchange Format:

SELECT com.toolsverse.etl.connector.ConnectorUtils.
dataSet2Str(dataSet, 'fully.qualified.connector.class.name', parameters) 
AS field_name, other_fields

Available connectors

  • JSON: 'com.toolsverse.etl.connector.json.JsonConnector'
  • XML: 'com.toolsverse.etl.connector.xml.XmlObjectConnector'
  • CSV: 'com.toolsverse.etl.connector.text.TextConnector'

Parameters

The parameters are either a key\u003Dvalue\u003Bkey\u003Dvalue string or null, which means that the default values for the parameters will be used.

Notice that character = is replaced with Unicode \u003D and character ; with unicode \u003B.

Other than that, the same parameters are available in SQL as when using the JavaScript.

Related articles

Comments

0 comments

Please sign in to leave a comment.