The XML structure outputted by a Convert Recordset to XML The Convert Recordset to XML tool converts one or more flat-structure BPA Platform recordsets into a hierarchical XML structure. It provides "translation services" between the standard BPA Platform tools, such as Database Query (ODBC), and advanced BPA Platform tools that accept XML as their input. step can either be:

►

Automatically generated — The input recordset structure you define in the Links tab determines the XML structure. Note that all recordset columns are included and cannot be removed.

►

Custom-defined — After defining the input recordset structure, you use the recordset columns from the Task Browser to form the required Output XML structure. If defining a parent-child XML structure, each level must contain a Key field from the input recordset (see Links tab).

Note that the input recordset is read record by record. The key field is checked to see if the value has changed since the last read; if so, a new instance of the object is created at the key field level in the XML structure. To prevent unexpected XML output, ensure the recordset structure created previously contains the correct links and key fields.

Example 1 — Auto-Generating Output

A task contains two Database Query (ODBC) You typically use the Database Query (ODBC) tool to extract specific records from a relational database, using a valid ODBC driver to make the connection. These are then made available to other task steps as a recordset. steps which obtain Account and Contact data from a CRM system. These queries return the following recordsets:

Account Query

AccountId	Name	Sector
1	Customer 1	Engineering
2	Customer 2	IT
3	Customer 3	Engineering

Contact Query

ContactId	AccountId	FirstName	LastName
1	1	Fred	Bloggs
2	1	John	Smith
3	1	Jane	Smith
4	2	Tim	Jones
5	3	Rod	Huggins
6	3	Phillip	Otter

In the Main tab, both steps are selected as input recordsets. A link is created from the Account to the Contact table, using the AccountId field in both tables. Auto Generate Output XML is used, which creates the following structure:

<Root>
     <Account>
          <AccountId>1</AccountId>
          <Name>Customer 1</Name>
          <Sector>Engineering</Sector>
          <Contact>
               <ContactId>1</ContactId>
               <AccountId>1</AccountId>
               <FirstName>Fred</FirstName>
               <LastName>Bloggs</LastName>
          </Contact>
          <Contact>
               <ContactId>2</ContactId>
               <AccountId>1</AccountId>
               <FirstName>John</FirstName>
               <LastName>Smith</LastName>
          </Contact>
          <Contact>
               <ContactId>3</ContactId>
               <AccountId>1</AccountId>
               <FirstName>Jane</FirstName>
               <LastName>Smith</LastName>
          </Contact>
     </Account>
     <Account>
          <AccountId>2</AccountId>
          <Name>Customer 2</Name>
          <Sector>IT</Sector>
          <Contact>
               <ContactId>4</ContactId>
               <AccountId>2</AccountId>
               <FirstName>Tim</FirstName>
               <LastName>Jones</LastName>
          </Contact>
     </Account>
     <Account>
          <AccountId>3</AccountId>
          <Name>Customer 3</Name>
          <Sector>Engineering</Sector>
          <Contact>
               <ContactId>5</ContactId>
               <AccountId>3</AccountId>
               <FirstName>Rod</FirstName>
               <LastName>Huggins</LastName>
          </Contact>
          <Contact>
               <ContactId>6</ContactId>
               <AccountId>3</AccountId>
               <FirstName>Phillipe</FirstName>
               <LastName>Otter</LastName>
          </Contact>
     </Account>
</Root>

Example 2 — Manually Generating Output

A task contains a single Database Query (OLEDB) You typically use the Database Query (OLEDB) tool to extract specific records from a relational database, using a valid ODBC driver to make the connection. These are then made available to other task steps as a recordset. step outputting a recordset consisting of customer and address data:

Code	Name	AddressId	Address_Street	Address_City
Cust1	Customer 1	1	2 Test Street	Somewhere
Cust1	Customer 1	2	2 Another Street	Nowheresville
Cust2	Customer 2	3	22 Acacia Avenue	Demotown

Click the image to view full size.

We want to create an XML structure that splits this single recordset into a parent and child XML structure, where the customer details form the parent node and the address details form the child node.

In the Main tab, this step is selected as the input recordset. Auto Generate Output XML is left as is. In the Output tab, the Root node is pre-defined. You cannot reorder elements once added to the XML structure, so you must create the parent node (right-click on Root and select Add Node) and add the relevant elements to it before creating the child node (right-click the parent node and select Add Node). In the example above, we create the Customer node first and drag Code and Name from the RecordSource1 node in the Task Browser before adding the Address child node.

The output XML looks like this:

<Root>
     <Customer>
          <Code>Cust1</Code>
          <Name>Customer 1</Name>
          <Address>
               <AddressId>1</AddressId>
               <Address_Street>2 Test Street</Address_Street>
               <Address_City>Somewhere</Address_City>
          </Address>
          <Address>
               <AddressId>2</AddressId>
               <Address_Street>2 Another Street</Address_Street>
               <Address_City>Nowheresville</Address_City>
          </Address>
     </Customer>
     <Customer>
          <Code>Cust2</Code>
          <Name>Customer 2</Name>
          <Address>
               <AddressId>3</AddressId>
               <Address_Street>22 Acacia Avenue</Address_Street>
               <Address_City>Demotown</Address_City>
          </Address>
     </Customer>
</Root>