Skip to main content

Transforming an XML Schema to a Relational Schema

  • Updated

Previous Topic  Next Topic

Transforming an XML Schema to a Relational Schema

After you have finished importing an XML schema, you can transform it to relational tables. In the transformation process, elements in the XML schema will be transformed to either tables or columns in tables according to the ideographic transformation rules, and the hierarchical logic in the XML schema will be maintained in the transformed relational schema.

Below is a list of the sections covered in this topic:

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 as 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 as 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 equal 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 to help you understand the transformation rules more easily.

Example 1: Transforming a simple element occurring 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 occurring more than once in the XML instance:

In the result, these two columns - NodePrimaryKey and NodeForeignKey are generated by the 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 occurring once 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 equal 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 the 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 string"/>
<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 the 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 the 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 the 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.

XML Hierarchical Logic in relational Schema

The XML hierarchical logic will be maintained in the transformed relational schema. The parent-child relationship in the XML schema can be maintained by the following two ways:

  • If the parent and children nodes are transformed to different tables, the parent-child relationship will be maintained by primary key and foreign key in tables, and this relationship can be reproduced by applying the join of the primary key and foreign key. When you build a query using the generated schema the query will automatically add the appropriate joins.
  • If the children nodes can be transformed to columns, the parent-child relationship in the XML hierarchical logic will be maintained in the relationship of table-column.

Previous Topic  Next Topic

Related articles