#### Transformation rules

When an XML schema is transformed to a relational schema automatically, the following transformation rules will be applied:

- A simple element in the XML schema will be transformed according to the following two rules:
  - If the Is Multiple property is set to true, elements will be transformed to a relational table. Contents of the simple element will be transformed to records of a column in the table and the column is named the same with the element.
  - If the Is Multiple property is set to false, contents of the simple element will be transformed to columns in the table transformed from the parent element of the simple element, and columns are named the same with the simple element.

  Note: The value of the Is Multiple property of a simple element is defined by the value of its maxOccurs property in the XSD. If you modify it and make it not match with the maxOccurs property in the transformation process, the element will be transformed according to the Is Multiple property regardless of the maxOccurs property. The following are the two conditions:

  - If the value of maxOccurs property of a simple element in XSD is greater than 1 or unbounded, but its Is Multiple property is modified to False in the transformation process, then only the last content of this element in XML instance can be transformed to a column.
  - If the value of maxOccurs property of a simple element in XSD is less than or equals to 1, but its Is Multiple property is modified to True in the transformation process, then the element will be transformed to a table with only one column and one record.
- Attributes of an element in the XML schema will be transformed to columns in the table, which is transformed from the element.
- A complex element in the XML schema will be transformed to a relational table. Contents of complex elements may contain three kinds of sub-nodes: text, simple element and sub-complex element.
  - Text in the XML will be ignored in the transformation process.
  - Each simple element will be transformed to a column in the table according to the rules above.
  - Each sub-complex element will be transformed to a table according to the rules above.

Below presents four illustrative examples for you to understand the transformation rules more easily.

##### Example 1: Transforming a simple element occurred more than once to a table

In the example, Employee is a simple element and its Is Multiple property is true according to the value of maxOccurs in the XSD which is 10, so it will be transformed to a table, named Employee. And contents of the simple element will be transformed to records of a column with the same name as the Employee table.

The following is a segment of an XSD file:

    ...
    <xs:element name="Employee" maxOccurs="10" type="xs:string"/>
    ...

The following is a segment of the corresponding XML instance:

    ...
    <Employee>John</Employee>
    <Employee>Sally</Employee>
    ...

The following table, named Employee, is the result which is transformed from the simple element occurred more than once in the XML instance:

In the result, these two columns - NodePrimaryKey and NodeForeignKey are generated by system automatically in the transformation process, and the column Employee is transformed from contents of the simple element.

Note: If the value of the maxOccurs property in the XSD is greater than 1 or unbounded, the simple element will occur more than once in the XML, and the Is Multiple property is true by default, but if you set Is Multiple to false in the transformation process, only the last element of the simple element in the XML will be transformed to a column of a table, which is transformed from the parent element of the simple element.

##### Example 2: Transforming a simple element occurred once or not occurred to a column

In the example, Employee is a simple element and its property Is Multiple is false according to the value of maxOccurs in the XSD which is less than or equals to 1, so the simple element Employee will be transformed to a column, named Employee in the Employees table which is transformed from the parent element.

The following is a segment of an XSD file:

    ...
    <xs:element name="Employee" type="xs:string" maxOccurs="1"/>
    ...

The following is a segment of the corresponding XML instance:

    ...
    <Employees>
      <Employee>John</Employee>
    </Employees>
    ...

The following table, named Employees, is the transformed result which is transformed from the parent element of the simple element:

In the result table, these two columns - NodePrimaryKey and NodeForeignKey are generated by system automatically in the transformation process, and the column Employee is transformed from the simple element.

##### Example 3: Transforming an attribute of an element to a column

In the example, the complex element - Employees will be transformed to a table, named Employees, the sub-elements Employee will be transformed to another table, named Employee, and attributes of these sub-elements will be transformed to columns in the Employee table.

The following is a segment of an XSD file:

    ...
    <xsd:attribute name="id" type="xsd:long"/>
    <xsd:attribute name="name" type="xsd tring"/>
    <xsd:attribute name="age" type="xsd:int"/>
    ...

The following is a segment of an XML file:

    ...
    <Employees groupId="1";>
    <Employee id="1", name="John", age="23"/>
    <Employee id="9" name="Sally" age="22"/>
    ...
    </Employees>
    ...

The following table named Employees is transformed from the parent element - Employees:

In the result table, these two columns NodePrimaryKey and NodeForeignKey are generated by system automatically in the transformation process.

The following table named Employee is transformed from sub-elements - Employee:

In the result table, these two columns NodePrimaryKey and NodeForeignKey are generated by system automatically in the transformation process and the foreign key in the Employee is mapped to the primary key in the Employees table.

##### Example 4: Transforming a complex element to a table

In the example, the element StockMarket is of complex type and its property Is Multiple is true according to the value of maxOccurs in the XSD file which is 10, so StockMarket will be transformed to a table, named StockMarket and its sub-elements such as: Date, Open, High, Low, Close, Volume and ID will all be transformed to columns of the table automatically.

The following is a segment of an XSD file:

    <xs:element name="StockMarket">
      <xs:complexType>
        <xs:choice maxOccurs="10">
          <xs:element name="Date" type="xs:date" />
          <xs:element name="Open" type="xs:double" />
          <xs:element name="High" type="xs:double" />
          <xs:element name="Low" type="xs:double" />
          <xs:element name="Close" type="xs:double" />
          <xs:element name="Volume" type="xs:double" />
          <xs:element name="ID" type="xs:long" />
        </xs:choice>
      </xs:complexType>
    </xs:element>

The following is a segment of a corresponding XML file:

    <StockMarket>
      <Date>1999-02-11</Date>
      <Open>11.5</Open>
      <High>12.4375</High>
      <Low>11.5</Low>
      <Close>12.4375</Close>
      <Volume>26600</Volume>
      <id>284</id>
    </StockMarket>

The following table named StockMarket is the transformed result:

In the result table, these two columns NodePrimaryKey and NodeForeignKey are generated by system automatically in the transformation process and the columns- id, Date, Open, Low, Close, Volume are transformed from sub-elements of the complex element StockMarket.