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.
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. |
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. |
The transformation sample file is :
samples/transformations/XML Add - encode fields in XML and add the field.ktr |
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:
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 |
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> |
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.