When you specify FOR XML RAW in a query, each row is represented as a <row> element, and each column is an attribute of the <row> element.
FOR XML RAW[, ELEMENTS ]
ELEMENTS tells FOR XML RAW to generate an XML element, instead of an attribute, for each column in the result. If there are NULL values, the element is omitted from the generated XML document. The following query generates <EmployeeID> and <DepartmentName> elements:
SELECT Employees.EmployeeID, Departments.DepartmentName FROM Employees JOIN Departments ON Employees.DepartmentID=Departments.DepartmentID FOR XML RAW, ELEMENTS; |
This query gives the following result:
<row> <EmployeeID>102</EmployeeID> <DepartmentName>R & D</DepartmentName> </row> <row> <EmployeeID>105</EmployeeID> <DepartmentName>R & D</DepartmentName> </row> <row> <EmployeeID>160</EmployeeID> <DepartmentName>R & D</DepartmentName> </row> <row> <EmployeeID>243</EmployeeID> <DepartmentName>R & D</DepartmentName> </row> ... |
Data in BINARY, LONG BINARY, IMAGE, and VARBINARY columns is automatically returned in base64-encoded format when you execute a query that contains FOR XML RAW.
By default, NULL values are omitted from the result. This behavior is controlled by the for_xml_null_treatment option.
For information about how NULL values are returned in queries that contain a FOR XML clause, see FOR XML and NULL values.
FOR XML RAW does not return a well-formed XML document because the document does not have a single root node. If a <root> element is required, one way to insert one is to use the XMLELEMENT function. For example,
SELECT XMLELEMENT( NAME root, (SELECT EmployeeID AS id, GivenName AS name FROM Employees FOR XML RAW)); |
For more information about the XMLELEMENT function, see XMLELEMENT function [String].
The attribute or element names used in the XML document can be changed by specifying aliases. The following query renames the ID attribute to product_ID:
SELECT ID AS product_ID FROM Products WHERE Color='black' FOR XML RAW; |
This query gives the following result:
<row product_ID="302"/> <row product_ID="400"/> <row product_ID="501"/> <row product_ID="700"/> |
The order of the results depend on the plan chosen by the optimizer, unless you request otherwise. If you want the results to appear in a particular order, you must include an ORDER BY clause in the query, for example:
SELECT Employees.EmployeeID, Departments.DepartmentName FROM Employees JOIN Departments ON Employees.DepartmentID=Departments.DepartmentID ORDER BY EmployeeID FOR XML RAW; |
Suppose you want to retrieve information about which department an employee belongs to, as follows:
SELECT Employees.EmployeeID, Departments.DepartmentName FROM Employees JOIN Departments ON Employees.DepartmentID=Departments.DepartmentID FOR XML RAW; |
The following XML document is returned:
<row EmployeeID="102" DepartmentName="R & D"/> <row EmployeeID="105" DepartmentName="R & D"/> <row EmployeeID="160" DepartmentName="R & D"/> <row EmployeeID="243" DepartmentName="R & D"/> ... |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |