An XML document can be described as having a tree structure with many levels. A database table, however, can only store data in a flat structure—at the field level. For example, an incoming XML document can contain data in different scopes, where the data belongs in the same scope in the database.
<BillTo> node of an XML sales order
document could contain a
element. The value of the
might sometimes need to be assigned to all sales items, and
sometimes to only one of them. If you declare the
<PaymentTerms> node as the Element node type and
the Text source type, the XMLport can store the value and therefore
when it starts to process the sales item information, you can
specify which records the value is to be assigned to.
To avoid writing data directly to a table, you can apply a stylesheet to the XML document to ensure that the XML node names are transferred in the correct order.
Alternatively, you could design an XMLport that writes the data
to a temporary table. You could then write code that inserts the
data into the relevant table using the correct order. The following
code example shows an XML sales order document that contains a
<Items> <Item PartNum="LS-75"> <ProductName>Loudspeaker, Cherry, 75W</ProductName> <Quantity>10</Quantity> <UnitPrice>79</UnitPrice> <Comment>Confirm the voltage is 75W</Comment> </Item> <Item PartNum="1908-8"> <ProductName>LONDON Swivel Chair, blue</ProductName> <Quantity>12</Quantity> <UnitPrice>190,926</UnitPrice> <ShipDate>12-05-04</ShipDate> <DocumentNo>9999</DocumentNo> </Item> </Items>
The value of this node, the number of the document, needs to be
written to the Sales Header table in the database. Before this can
happen, the value needs to be checked to ensure that it is valid.
However, the XMLport design will not work because the position of
<DocumentNo> node violates the validation
order of the Sales Lines table.
To solve the problem, you could assign the XML node names that
were mapped to the Sales Lines table to a temporary table. You
could then write code that assigns the value of the
<DocumentNo> node to the correct table, and in
the correct validation order, after the value has been assigned to
a temporary table record. This means that the
<DocumentNo> value must be copied from the
temporary record to the real record before any of the other fields
are copied to the Sales Lines table.
By using temporary tables, you will be able to solve most of the cases where you can see that an incoming XML document contains nodes in a sequence that would violate the database validation order.