The Java-based function ForXmlTree maps a set of SQL tables or result sets to a tree-structured XML document. It is based on the for xml clause of the SQL select command, which was introduced in Adaptive Server 12.5.1.
select...for xml performs these tasks:
Maps a single SQL result set to a single XML document.
Generates a direct mapping of the SQL result set to XML. For example, if select returns a result set with 1000 rows, each having 20 columns, then the XML document returned by for xml has 1000 elements for the rows, each having 20 elements for the columns.
The Java-based function ForXmlTree:
Can be invoked in the SQL server, a client command line, or a client or server Java application.
Maps a collection of results sets to a single tree-structured XML document.
Requires a <forxmltree> specification argument, which describes the desired output tree and the SQL data to be included at each node of the tree.
Generates a for xml-style mapping of XML data at each node of the output tree-structured XML document.
As a result, you can regard the ForXmlTree capability as a two-dimensional for xml mapping. For example, the following <forxmltree> input for ForXmlTree generates the XML document shown in “Sample data and its tree-structured XML representation”.
1) <!-- A forxmltree spec for depts-emps-phones-projects, with aggregation --> 2) <forxmltree treename="sample"> 3) <node> <!-- The node element for depts --> 4) <query> select * from depts order by dept_id </query> 5) <options> tablename=depts rowname=dept </options> 6) <link variablename="@dept_id" columnname="dept_id" type="char(11)" /> 7) <node> <!-- The node element for emps, under depts --> 8) <query> 9) select emp_id, emp_name, salary from emps e 10) where e.dept_id = @dept_id order by emp_id 11) </query> 12) <options> tablename=emps rowname=emp </options> 13) <link variablename="@emp_id" columnname="emp_id" type="char(6)"/> 14) <node> <!-- The node element for phones, under emps --> 15) <query> 16) select phone_no from emp_phones ep where ep.emp_id = @emp_id 17) </query> 18) <options> tablename=phones rowname=phone </options> 19) </node> <!-- End the node for phones --> 20) </node> <!-- End the node for emps -- 21) <node> <!-- The node element for projects, under dept --> 22) <query> 23) select project_id, budget from projects p 24) where p.dept_id = @dept_id order by project_id 25) </query> 26) <options> tablename=projects rowname=project </options> 27) </node> <!-- End the node for projects --> 28) </node> <!-- End the node for depts --> 29) </forxmltree>
