Description

The XML column step allows you to encode the content of a number of fields in a row in XML. This XML is added to the row in the form of a String field.

Options

Content Tab

Option	Description
Step name	Name of the step; this name has to be unique in a single transformation
Encoding	The encoding to use; this encoding is specified in the header of the XML file
Output Value	The name of the new field that contains the XML
Root XML element	The name of the root element in the generated element
Omit XML header	Enable to not include the XML header in the output.
Omit null values from the XML result	Do not add elements or attributes with null values. This is often used to limit the size of the target XML.

Fields Tab

The Fields tab is where you configure the output fields and their formats. The table below describes each of the available properties for a field:

Option	Description
Fieldname	Name of the field
Element name	The name of the element in the XML file to use
Type	Type of the field can be either String, Date, or Number
Format	Format mask with which to convert data; see Number Formats for a complete description of format specifiers
Length	Output string is padded to this length if it is specified
Precision	The precision to use
Currency	Symbol used to represent currencies like $10,000.00 or E5.000,00
Decimal	A decimal point can be a "." (10,000.00) or "," (5.000,00)
Grouping	A grouping can be a "," (10,000.00) or "." (5.000,00)
Null	The string to use in case the field value is null.
Attribute	Make this an attribute (N means : element)
Attribute parent name	You can specify the name of the parent element to add the attribute to if previous parameter attribute is set to Y. If no parent name is specified, the attribute is set in the parent element.

Example

Transformation

The transformation sample file is :

samples/transformations/XML Add - encode fields in XML and add the field.ktr

Use case

Below is data that comes in a variety of classes and which needs to be stored as XML in a database. You want to turn the raw data into the database layout below:

Raw data

SHAPE	COLOUR	id	X	Y	RADIUS
circle	blue	1	3	5	5
circle	red	2	1	3	5
circle	blue	5	5	9	5
circle	blue	6	8	2	5
circle	red	7	9	7	5

SHAPE	COLOUR	id	X	Y	LENGTH	WIDTH
rectangle	blue	3	3	1	6	4
rectangle	red	6	2	4	6	4
rectangle	blue	10	8	2	6	4
rectangle	red	12	7	8	6	4
rectangle	blue	14	5	2	6	4

Output Sample

ID	X	Y	CLASS_DATA
3	4	7	<SHAPE type="circle"><COLOUR>blue</COLOUR><RADIUS> 5</RADIUS></SHAPE>
1	6	3	<SHAPE type="rectangle"><COLOUR>blue</COLOUR><WIDTH> 4</WIDTH><LENGTH> 6</LENGTH></SHAPE>
2	8	8	<SHAPE type="rectangle"><COLOUR>blue</COLOUR><WIDTH> 4</WIDTH><LENGTH>6</LENGTH></SHAPE>
5	5	2	<SHAPE type="circle"><COLOUR>blue</COLOUR><RADIUS> 5</RADIUS></SHAPE>

Metadata Injection Support (7.x and later)

All fields of this step support metadata injection. You can use this step with ETL Metadata Injection to pass metadata to your transformation at runtime.

Browser not supported