Example of XML File Upload

This example shows how the number of rows uploaded is different when selecting a subset of columns in the hierarchy of elements in an XML file

Suppose you have an orders.xml file that contains the following hierarchy of elements:

Order, which consists of:
- Person name who placed the order
- Date the order was placed
- Ship to information, which consists of:
  - Name
  - Address
  - Order ID
- Order item information, which consists of:
  - item description
  - item ID

This sample orders.xml file has four person elements that each has one order with two ordered items. Here is a partial view of the data in this orders.xml file that shows the first two persons:

<?xml version="1.0" encoding="UTF-8"?>
<testOrders>
	<order>
		<person>Dora</person>
		<datetime>2015-05-30T09:00:00</datetime>
		<shipto>
			<name>Dora Receiver</name>
               <address>Address 1</address>
			<lastOrder>
				<name>order 1</name>
				<id>1111</id>
			</lastOrder>
		</shipto>
		<orderedItem>
			<description>
				<name>Item 1_1</name>
				<name>Item 2_1</name>
			</description>
			<id>1111</id>
		</orderedItem>
	</order>
	<order>
		<person>Bob</person>
		<datetime>2015-05-30T10:00:00</datetime>
		<shipto>
			<name>Bob Receiver</name>
			<address>Address 2</address>
			<lastOrder>
				<name>order 2</name>
				<id>2222</id>
			</lastOrder>
		</shipto>
		<orderedItem>
			<description>
				<name>Item 1_2</name>
				<name>Item 2_2</name>
			</description>
			<id>10002</id>
		</orderedItem>
	</order>

To display this hierarchical data, SAP Agile Data Preparation flattens the data into separate tables and acquires the data by joining the tables based on the columns selected in the Preview data area of the Upload a New File dialog. Therefore, the number of rows acquired will depend on the columns you select in the Preview data area.

The application flattens this XML hierarchy into the following tables:

order.person table with 4 rows, one for each of the 4 persons
order.datetime table with 4 rows, one for each of the 4 persons
shipto table with 4 rows, one for each of the 4 orders
OrderItem table with 8 rows, 2 for each order

If you keep all columns selected in the Preview data area, the application joins the tables and acquires 8 rows:

(4 person and date values) x (1 order) x (2 items per order) = 8

Here is a partial view of the 8 acquired rows:

order.person	order.date	order.shipto.name	...	order.shipto.orderID	order.item.description	order.ordereditem.id
Dora	2015-05-30T09:00:00	Dora Receiver	...	1111	Item 1_1	Item1
Dora	2015-05-30T09:00:00	Dora Receiver	...	1111	Item 2_1	Item2
Bob	2015-05-30T10:00:00	Bob Receiver	...	2222	Item 1_2	Item1
Bob	2015-05-30T10:00:00	Bob Receiver	...	2222	Item 2_2	Item2
Jane	2015-05-30T11:00:00	Jane Receiver	...	3333	Item 1_3	Item1
Jane	2015-05-30T11:00:00	Jane Receiver	...	3333	Item 2_3	Item2
Ken	2015-05-30T12:00:00	Ken Receiver	...	4444	Item 1_4	Item1
Ken	2015-05-30T12:00:00	Ken Receiver	...	4444	Item 2_4	Item2

If you are only interested in seeing where the orders are shipped, you can deselect columns order.item.description and order.orderedItem.id. The application now acquires only 4 rows:

(4 person and date) x (1 orders) = 4

Here is a partial view of the 4 acquired rows:

order.person	order.date	order.shipto.name	...	order.shipto.orderID
Dora	2015-05-30T09:00:00	Dora Receiver	...	1111
Bob	2015-05-30T10:00:00	Bob Receiver	...	2222
Jane	2015-05-30T11:00:00	Jane Receiver	...	3333
Ken	2015-05-30T12:00:00	Bob Receiver	...	4444