![]() The result set for the two SELECT statements is then ordered by the EmployeeID and FirstName columns. The remaining columns in the SELECT list are defined as you would normally define columns in your query. The second SELECT statement is much simpler, but it still includes the Tag and Parent columns in the SELECT list. This will provide the XML structure necessary to join the two SELECT statements. : Additional information for how to construct the XML.įor example, based on the alias name assigned to the EmployeeID column, you can see that the EmployeeID attribute will be associated with the element on the first level of the hierarchy.īecause the next three columns in the SELECT list are associated with the second level of the XML hierarchy, which is defined in the second SELECT statement, null values are assigned to the alias names for the column.For example, if the ELEMENT directive is specified, is the name of the child element. : The name of the attribute associated with the column value, unless an optional directive is specified. ![]() : The tag number associated with the hierarchy that the value should be assigned to, as defined in the Tag column.: The name of the element that the value should be assigned to.The following SELECT statements return results similar to what you’ve seen in previous examples however, the SELECT statements themselves are more detailed:Īs the syntax shows, the first three components are required, and the last is optional: Let’s look at an example to help understand how this all works. The columns must also include aliases that define that structure. Your first SELECT statement should also include a reference to all the columns that will make up the XML structure. In the first SELECT statement, the Parent value should be null to indicate that this is a top level hierarchy. Again, this is a numerical value that identifies the parent of the hierarchy based on the Tag values you’ve assigned. The second column that you should include in your SELECT statement is Parent. The second SELECT statement should include a Tag column with a value of 2, and so on. For instance, the first SELECT statement should include a Tag column with a value of 1. ![]() The first column, Tag, is assigned a numerical value for each level of the hierarchy. When constructing your SELECT statement, you must include two columns in your SELECT list that describe the XML hierarchy. ![]() In the meantime, let’s take a look at a few examples that help demonstrate some of the basic elements of the EXPLICIT mode. There are a number of rules that describe how to define your SELECT statements when using the EXPLICIT mode, and it is beyond the scope of this article to review all those rules, so be sure to refer to the topic “Using EXPLICIT Mode” in SQL Server Books Online for the details about how to construct your SELECT statements. In addition, you must create a SELECT statement for each level of that hierarchy and use UNION ALL clauses to join those statements. To use this mode, you must build your SELECT statements in such as way as to define the XML hierarchy and structure. The EXPLICIT mode provides very specific control over your XML, but this mode is much more complex to use than the RAW or AUTO modes. For greater control, you’ll want to use the EXPLICIT mode or the PATH mode. However, the AUTO mode (and the RAW mode, for that matter) provides little control over the XML returned by your query. And in the same way, you can override the default behavior by using the ELEMENTS option:Īs you can see, subqueries let you maintain some control over the output. This structure is similar to what you saw in the RAW mode examples. In addition, the columns and their values are added as attributes to the table-related elements. If an additional table and its columns were included in the SELECT list, after the other columns, they would appear as a child element of. And because the remaining columns, which are associated with the ContactInfo table, appear next in the SELECT list, they are added as a child element. In this case, because EmployeeID is the first column in the SELECT list and the Employee table is included in the FROM clause, the first element is. The structure of the elements is based on the order in which the columns are defined in the SELECT list and the tables that are specified in the FROM clause. Notice too that the element is a child element of. As you can see, the element has been named automatically based on the table alias name.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |