An additional Hierarchy Level

Obviously, we do not have the source information, but the XML should be build that way. With DI its easy, we create a new schema, make it the current and drag and drop the CUST_ID into it (and the CUST_ROLE is any constant).

But why can we copy the CUST_ID into the schema although the "From" clause is empty? For the same reason we have been able to use the SALESORDER.SALES_ORDER_NUMBER in the where clause of the SALESITEM: The child belongs to the parent thus has all the information of the parent.

But if you would execute the flow, the CUSTOMER schema would never contain any rows. Why? Because it outputs as many rows as the "select ... from ... where.." returns. We do not select from anywhere, so we produce zero rows. So what to put in the from clause?

A common mistake is looking at the columns. We need a SALESORDER.CUST_ID, let us take SALESORDER into the from clause then. But remember the "How many rows...?" question. This would mean for each SALESORDER we want as many rows as there are SALESORDERS - all orders in other words. Okay, we can specify a where clause, but what for? How many rows do we want? One. One and only one. So we generate that one row with the Row Generation Transform and this should be the "From" for this schema. Okay, we still create a cartesian product, but with one Row Generation row only!

The same trick applies in case the XML should have one root node which then will contain all SALESORDERS. At root level we would have one column only called "ALL_ORDERS" and of type table - a schema in other words - and the "From" would be a Row Generation Transform only.