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.

Example

The <BillTo> node of an XML sales order document could contain a <PaymentTerms> child element. The value of the <PaymentTerms> node 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 <DocumentNo> node.

  CopyCode imageCopy Code 
<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 the <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.

See Also