XML Join
Description
The XML Join transform adds XML tags from one stream into a leading XML structure from a second stream.
The target stream must have only one row, since it represents an XML document. The other stream can consist of many rows, and the tags from all rows are added to the target document by the join.
Only one row is produced after the join. This single row contains the fields of the target transform plus the result field of the join.
Options
The options are grouped the way the dialog shows them. The XML field options list the fields of the transform selected above them. You can also type a field name or a variable. When exactly two transforms send rows to the XML Join, picking one of them as the target or the source fills in the other when it is still empty.
| Option | Description |
|---|---|
Transform name |
Name of the transform. |
Target document (one row) |
|
Transform |
The transform that sends the XML document to add the elements to. Only its first row is used. |
XML field |
The field of the target transform that holds the XML document. |
Source elements (one per row) |
|
Transform |
The transform that sends the XML elements to add, one element per row. |
XML field |
The field of the source transform that holds the XML element to add. |
Where to add them |
|
Join by key |
Off: every element is added to the node the XPath statement finds. On: every |
XPath statement |
Where in the target document to add the elements, for example The line under the XPath statement checks it as you type, once the target document is loaded (see below). In green it names the node the elements go under and its parent, and when joining by key the key that node has. In red it says that the statement finds no node, or why it is not a valid XPath statement. When joining by key it also warns when other nodes have the same key as the highlighted node: the transform puts the elements for that key under the first of them. Before the document is loaded, it says what the statement does and what is missing. |
Key field |
The field of the source transform whose value replaces |
Result |
|
XML field |
The field that holds the joined XML document. The output row has the fields of the target transform plus this field. |
Encoding |
The encoding written in the XML header.
Characters the encoding cannot hold are written as character references, for example |
Omit XML header |
Whether the XML header is omitted from the output. The character encoding of the target XML is preserved regardless of whether the header is included. |
Omit null values |
Whether null values are removed from the output. If this option is not selected, null values are included in the XML output as an empty element, such as <abc/>. When this option is selected, empty elements without attributes are removed, and so is an element that is left with no attributes, elements or text. Whitespace counts as text: in an indented document, an element whose empty children were removed keeps the whitespace between them, and stays. This is useful to save space in the output file (for high volume transactions) or to use the empty tags for special conditions in the output. |
Target document
Beside the options, Load shows the XML document in the first row of the target transform as a tree. Only the target transform and the transforms before it run for this, in preview. When any of them may write or run something else, such as a Table output, an Execute SQL script or a Pipeline executor, Load names them and asks before it runs them.
-
Expand all and Collapse all, the icons beside Load, open or close every node under the selected node, or the whole document when no node is selected. In a large document, Expand all opens level by level and stops at 5,000 nodes.
-
Click the node to add the elements to, and the XPath statement for it is filled in.
-
To join by key, also choose the attribute or text that holds the key below the tree. When the key field is empty, a key with the name of a source field is chosen, and that field becomes the key field. Choosing another key field afterwards chooses the key with its name, when the node has one.
-
The node the XPath statement finds is highlighted, also for a statement you type, and the line under the XPath statement says which node that is. Without join by key, that is the first node the statement finds, the one the elements go under. The tree lists the first 200 elements under a node; the line says when the node the statement finds is not among them.
When you switch Join by key on or off, a statement written by a click is written again for the new setting. A statement you typed stays as it is.