Figure 8-1 shows these facts. in VS .NET

Generation PDF417 in VS .NET Figure 8-1 shows these facts.

8

Figure 8-1

For more information about the different settings available to FOR XML AUTO, read the topic Using AUTO Mode in SQL Server 2005 Books Online.

FOR XML PATH is new to SQL Server 2005. With XML PATH, developers have full control over how the XML structure is generated, including having some columns as attributes and others as elements. Each column is configured independently. Each column is given a column alias that tells SQL Server where to locate this node in the XML hierarchy. If a column doesn t receive a column alias, the default node <row> is used (as in XML RAW). You declare column aliases by using pseudo-XPATH expressions. Table 8-1 describes some of the different options for configuring columns in FOR XML PATH.

Table 8-1

Option 'elementName' '@attributeName' 'elementName/nestedElement'

Description An XML element, <elementName>, is created with the content of that column on the context node. An XML attribute, attributeName, is created with the content of that column on the context node. An XML element, <elementName>, is created; beneath it, a <nestedElement> XML element is created with the content of that column. An XML element, <elementName>, is created, and an XML attribute, attributeName, is created with the content of that column. Inserts the content of that column as a text node in the XML structure. Inserts the content of that column as an XML comment in the XML structure. The content of that column is inserted as if no column name were specified. The content of that column is treated as an atomic value. A space character is added to the XML if the next item in the serialization is also an atomic value.

'elementName/@attributeName'

text() comment() node() data()

The following code example is based on the same query as the previous examples. The order of the column declarations in the SELECT statement has been changed a little to show the most important features of using XML PATH.

SELECT History.[StartDate] '@StartDate' ,Department.[DepartmentID] 'Department/@id' ,Department.[Name] 'comment()' ,History.[EmployeeID] 'Department/Employee/@id' ,'Years in role:' 'Department/Employee/data()' ,DATEDIFF(year, History.[StartDate], GetDate()) 'Department/Employee/data()' FROM HumanResources.Department, HumanResources.EmployeeDepartmentHistory History WHERE Department.DepartmentID = History.DepartmentID AND History.EndDate IS NULL ORDER BY Department.[DepartmentID], History.[StartDate] FOR XML PATH ('ForEachRow')

8

Here is a partial result of executing this query:

<ForEachRow StartDate="2001-02-18T00:00:00"> <Department id="1" /> <!--Engineering--> <Department> <Employee id="270">Years in role: 5</Employee> </Department> </ForEachRow> <ForEachRow StartDate="1998-01-11T00:00:00"> <Department id="2" /> <!--Tool Design--> <Department> <Employee id="5">Years in role: 8</Employee> </Department> </ForEachRow> <ForEachRow StartDate="2000-07-01T00:00:00"> <Department id="2" /> <!--Tool Design--> <Department> <Employee id="4">Years in role: 6</Employee> </Department> </ForEachRow>

The XML PATH instruction renames the default <row> element to <ForEachRow>. The StartDate column is formatted as the 'StartDate' attribute. Because it does not specify where to locate the attribute in the XML structure, it is created on the context node, the <ForEachRow> element. The DepartmentID column is formatted as the 'id' attribute for the <Department> element that is created beneath the <ForEachRow> element. The Name column is formatted as a comment. Because it does not specify where to locate the comment in the XML structure, it is created under the context node, the <Department> element. The EmployeeID column is formatted as the 'id' attribute for the <Employee> element that is created under the <Department> element. The <Department> element is created beneath the <ForEachRow> element. A constant value column is formatted as an atomic value for the <Employee> element that is created under the <Department> element. The <Department> element is created under the <ForEachRow> element. The computed column is formatted as an atomic value for the <Employee> element that is created under the <Department> element, which is created under the <ForEachRow> element. Because the previous column is also an atomic value in exactly the same location, SQL Server will add an extra space between the two values.