In order to create a new XML document you can use the XML Output step. In this recipe, you will create a new XML file from a database containing books information.
You will need a books' database with the structure described in the Appendix,
Carry out the following steps:
- Create a new transformation.
- Drop a Table Input step, in order to obtain the books' information and type the following query:
SELECT id_title , title , genre , price , concat(lastname,", ",firstname) author FROM Books LEFT JOIN Authors ON Authors.id_author=Books.id_author
- Add an XML Output step.
- In the Filename textbox of the File tab, type the destination filename, including its complete path (without extension). In the Extension textbox, leave the default value,
xml. - Fill the Content tab: As Parent XML element, type
Booksand as Row XML element, typeBook. - Under the Fields tab, use the Get Fields button to get the fields. In the
pricefield, set the Format to$0.00. - Run the transformation and look at the generated XML file. It should look like the following:
<Books> <Book> <id_title>123-346</id_title> <title>Carrie </title> <genre>Fiction</genre> <price>$41,00</price> <author>King, Stephen</author> </Book> <Book> <id_title>123-347</id_title> <title>Salem›s Lot </title> … </Book> … </Books>
The XML output step does the entire task. It creates the XML file with rows coming in the stream, using the Parent XML element and Row XML element values to complete the structure of the XML file. It encloses each row between tags with the name you provided for Row XML element (<Book> and</Book>) and the whole structure between tags with the name provided for Parent XML element (<Books> and</Books>).
The XML output step has some properties in common with other output steps. For example, the option to add the date and time as part of the name of the file or to split the output in several files using the Split every ... rows textbox from the Content tab.
In the recipe, you wrote the XML information into a file, but you may want to have the information in XML format as a new column of your dataset. The following section explains how to do this.
If, rather than generating the XML structure in a file, you want the structure as a new field, then you should use the Add XML step from the Transform category instead of using the XML output step.
The Add XML step encodes several fields into an XML fragment. In this step, you must set the Root XML element (for example Book) and the name for the new column. The Fields tab is quite similar to the same one in the XML output step, but here you can also specify if the element is a node or an attribute. In the example, you can set the field id_title as an attribute of the element Book: Set Attribute as Y and Attribute parent name as Book, and you will have the following XML structure:
<book id_title ="123-346"> <title>Carrie </title> <genre>Fiction</genre> <price>41.00</price> <author>King, Stephen</author> </book>
This step is particularly useful for generating complex structures, as you will see in the next recipe.