Chapter 4: XML Mapping Functions

Sample data and its tree-structured XML representation

SQL data is stored in tables, using foreign-key and primary-key columns to provide the tree-structured relationships between tables. When such data is depicted in XML, the tree-structured relationships are commonly represented with nested elements.

For example, consider tables with the data shown in Table 4-1.

**Table 4-1: Sample tables**
Table data
depts(dept_id, dept_name) emps(emp_id, emp_name, dept_id) emp_phones(emp_id, phone_no projects(project_id, dept_id)

The tree-structured XML representation of the data in Table 4-1 is:

<sample xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<depts>
<dept>
      <dept_id>D123</dept_id>
      <dept_name>Main</dept_name>
       <emps>
           <emp>
               <emp_id>E123</emp_id>
               <emp_name>Alex Allen</emp_name>
               <salary>912.34</salary>
               <phones>
                   <phone><phone_no>510.555.1987</phone_no></phone>
                   <phone><phone_no>510.555.1876</phone_no></phone>
                   <!-- other phone elements for this emp -->
               </phones>
            <!-- Other emp elements for this dept -- >
       </emps>

       <projects>
             <project>
                  <project_id>PABC</project_id>
                  <budget>598.76</budget>
             </project>
             <!-- Other project elements for this dept - ->
         </projects>
     </dept>
    <!-- other dept elements for this set of depts. -->
</depts>
</sample>

Using ForXmlTree to map SQL data to hierarchic XML

View this book as PDF