Some Opinions on SQL Server 2000's XML support

Some Opinions on SQL Server 2000's XML support
This article mainly discusses similar statements of "select Field 1, Field 2,..., field N from table or multi-table joint query where condition for XML auto ".

When I used the preceding statements to generate XML data, I found some rules for SQL Server 2000 to generate XML data. I felt that the requirements were too harsh, we can also see that Microsoft's early support for XML is insufficient. now I will write my experiences to you. I hope you can point out the shortcomings.
XML allows you to easily describe tree-type data, which is not enough for relational data. Therefore, converting relational data to tree-structured data is troublesome. Fortunately, SQL Server 2000 provides the conversion interface in this regard. However, if you want to convert it, it is also possible.

Prerequisites: SQL Server 2000 already has a basic table (View) that describes the tree structure data. [for example, the tree structure information of the faculty and class in the university, the school information is a table, a department information table, a class information table, and a primary-foreign key relationship has been established between the tables.]

1. The positions of fields to be displayed are ordered. To correctly display the tree structure of the Department and Department classes, write the SQL statement for conversion:
Select School, department, class from school, department, class where condition for XML auto
2. do not perform any operations or processing on the displayed fields as far as possible, because this will damage the data of the basic table (View) as a certain level of data conventions.
For example, after performing some operations and processing on the "System" Field of the "select courtyard, department, class from courtyard, department, and class where condition for XML auto" statement,
SQL Server 2000 cannot correctly describe the layer of data generated by the "System" field after the XML data is generated. Generally,
SQL Server 2000 automatically adds the "System" field to the "school" or "class" layer. In this way, we cannot get the expected results.
3. When performing multi-table joint queries, do not have subqueries, because even if you use subqueries, they are useless,
The conversion statement "select Field 1, Field 2,..., field N from table or multi-table joint query where condition for XML auto" only recognizes basic tables and views. Don't think about using subqueries to implement some complex functions.

The preceding example uses "select Field 1, Field 2 ,..., when the field N from table or multi-table joint query where condition for XML auto "statement, some of the experiences are gibberish.