Ajax toolkit
Ajax tutorials
|
|
7.9. XML Results Using the FOR XML ClauseYou can return the result set of a SELECT statement as XML by specifying the FOR XML clause in the query. The FOR XML clause was introduced in SQL Server 2000. SQL Server 2005 enhances the functionality, as discussed in the "FOR XML Updates and Enhancements" section later in this chapter. The FOR XML clause syntax is:
[ FOR { BROWSE | <XML> } ]
<XML> ::=
XML
{
{ RAW [ ('ElementName') ] | AUTO }
[
<CommonDirectives>
[ , { XMLDATA | XMLSCHEMA [ ( TargetNameSpaceURI ) ]} ]
[ , ELEMENTS [ XSINIL | ABSENT ]
]
| EXPLICIT
[
<CommonDirectives>
[ , XMLDATA ]
]
| PATH [ ('ElementName') ]
[
<CommonDirectives>
[ , ELEMENTS [ XSINIL | ABSENT ] ]
]
}
<CommonDirectives> ::=
[ , BINARY BASE64 ]
[ , TYPE ]
[ , ROOT [ ('RootName') ] ]
where:
7.9.1. Some FOR XML ExamplesThe examples in this subsection show the effect of the FOR XML clause on the result set returned by the following SELECT statement:
USE AdventureWorks
SELECT TOP 2 DepartmentID, Name
FROM HumanResources.Department
The SELECT statement without the FOR XML clause returns the ID and name of the top two departments, as shown in Figure 7-20. Figure 7-20. Results for SELECT example Now add the FOR XML RAW clause to the statement:
SELECT TOP 2 DepartmentID, Name
FROM HumanResources.Department
FOR XML RAW
The result set is a single row with one xml data type column containing the XML fragment shown in Figure 7-21. Figure 7-21. Results for FOR XML example Add the ROOT directive to the FOR XML clause to add a root node Departments and turn the XML fragment into an XML document:
SELECT TOP 2 DepartmentID, Name
FROM HumanResources.Department
FOR XML RAW, ROOT ('Departments')
The results are shown in Figure 7-22. Specifying the XMLSCHEMA directive returns an inline XSD schema in the result set:
SELECT TOP 2 DepartmentID, Name
FROM HumanResources.Department
FOR XML RAW, XMLSCHEMA
Figure 7-22. Results for FOR XML with ROOT directive example The results are shown in Figure 7-23. Figure 7-23. Results for FOR XML with XMLSCHEMA directive The following example uses AUTO mode to return sales order information:
SELECT soh.SalesOrderID, soh.OrderDate, soh.CustomerID,
sod.ProductID, sod.OrderQty
FROM Sales.SalesOrderHeader soh, Sales.SalesOrderDetail sod
WHERE soh.SalesOrderID = sod.SalesOrderID
FOR XML AUTO
Partial results are shown in Figure 7-24. One element is created for each table specified in the FROM clause, with the table aliases specified in the FROM clause used as element names. AUTO mode uses the column order in the SELECT statement to nest elements in the XML document hierarchy. Values of selected columns are added to the elements as attributes. The ORDER BY clause is needed to ensure that all child elements are nested under a single parent element. The following example uses PATH mode to return contact information for vendors:
SELECT
v.VendorID "@ID",
v.Name "@Name",
c.FirstName "Contact/First",
c.LastName "Contact/Last"
FROM Purchasing.Vendor v, Purchasing.VendorContact vc, Person.Contact c
WHERE v.VendorID = vc.VendorID AND vc.ContactID = c.ContactID
ORDER BY v.Name
FOR XML PATH ('Vendor')
Figure 7-24. Results for FOR XML with AUTO mode example Partial results are shown in Figure 7-25. Figure 7-25. Results for FOR XML with PATH mode example The ampersand (@) preceding the VendorID and Name column names results in the output of attributes in the XML document. The slash (/) in the FirstName and LastName column names results in the output of XML subelements in the XML document. The following example uses EXPLICIT mode to return contact information for vendors:
SELECT DISTINCT
1 AS Tag,
NULL AS Parent,
v.VendorID AS [Vendor!1!ID],
v.Name AS [Vendor!1!Name],
NULL AS [Contact!2!FirstName],
NULL AS [Contact!2!LastName]
FROM Purchasing.Vendor v, Purchasing.VendorContact vc, Person.Contact c
WHERE v.VendorID = vc.VendorID AND vc.ContactID = c.ContactID
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
v.VendorID,
v.Name,
c.FirstName,
c.LastName
FROM Purchasing.Vendor v, Purchasing.VendorContact vc, Person.Contact c
WHERE v.VendorID = vc.VendorID AND vc.ContactID = c.ContactID
ORDER BY [Vendor!1!ID], [Contact!2!LastName]
FOR XML EXPLICIT
Partial results are shown in Figure 7-26. Figure 7-26. Results for FOR XML with EXPLICIT mode example The Tag and Parent metacolumns determine the XML document hierarchy. Columns are selected at each level of the hierarchy and combined into a nested XML document using the UNION ALL operator. The column name syntax is elementName! elementLevel!attributeName. 7.9.2. FOR XML Support for SQL Server Data TypesThe following SQL Server data types have limitations or special handling as described when used with the FOR XML clause:
7.9.3. FOR XML Updates and EnhancementsSQL Server 2005 updates and enhances FOR XML functionality in SQL Server 2000 as described in the following list:
 |
|
|
Ajax toolkit
Ajax tutorials