Using RAW Mode

Using RAW Mode

RAW mode transforms each row in the query result set into an XML element with the generic identifier row. Each column value that is not NULL is mapped to an attribute of the XML element in which the attribute name is the same as the column name.

The BINARY BASE64 option must be specified in the query to return the binary data in base64-encoded format. In RAW mode, retrieving binary data without specifying the BINARY BASE64 option results in an error.

When an XML-Data schema is requested, the schema, declared as a namespace, appears at the beginning of the data. In the result, the schema namespace reference is repeated for every top-level element.

Examples

The queries in these examples can be executed using SQL Query Analyzer. To execute these queries using HTTP, see Accessing SQL Server Using HTTP.

A. Retrieve customer and order information using the RAW mode

This query returns customer and order information. RAW mode is specified in the FOR XML clause.

SELECT Customers.CustomerID, Orders.OrderID, Orders.OrderDate 
FROM Customers, Orders 
WHERE Customers.CustomerID = Orders.CustomerID 
ORDER BY Customers.CustomerID 
FOR XML RAW

This is the partial result:

<row CustomerID="ALFKI" OrderID="10643" OrderDate="1997-08-25T00:00:00"/>
<row CustomerID="ANATR" OrderID="10308" OrderDate="1996-09-18T00:00:00"/>
<row CustomerID="ANATR" OrderID="10625" OrderDate="1997-08-08T00:00:00"/>
<row CustomerID="AROUT" OrderID="10355" OrderDate="1996-11-15T00:00:00"/>

The same query can be specified using an outer join to return all customers in the result set, regardless of whether there are any orders for those customers.

SELECT C.CustomerID, O.OrderID, O.OrderDate
FROM Customers C LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID
ORDER BY C.CustomerID
FOR XML RAW

This is the partial result:

<row CustomerID="BONAP" OrderID="11076" OrderDate="1998-05-06T00:00:00"/>
<row CustomerID="FISSA"/>
<row CustomerID="PARIS"/>
<row CustomerID="RICSU" OrderID="11075" OrderDate="1998-05-06T00:00:00"/>

B. Specify the XMLDATA option to request XML-Data schema

This query returns the XML-DATA schema that describes the document structure:

SELECT TOP 2 Customers.CustomerID, Orders.OrderID, Orders.OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerID
FOR XML RAW , XMLDATA

This is the partial result:

<Schema name="Schema3" xmlns="urn:schemas-microsoft-com:xml-data" 
     xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="row" content="empty" model="closed">
    <AttributeType name="CustomerID" dt:type="string"/>
    <AttributeType name="OrderID" dt:type="i4"/>
    <AttributeType name="OrderDate" dt:type="dateTime"/>
    <attribute type="CustomerID"/><attribute type="OrderID"/>
    <attribute type="OrderDate"/>
  </ElementType>
</Schema>
<row xmlns="x-schema:#Schema3" CustomerID="ALFKI" OrderID="10643" 
     OrderDate="1997-08-25T00:00:00"/>
<row xmlns="x-schema:#Schema3" CustomerID="ALFKI" OrderID="10692" 
     OrderDate="1997-10-03T00:00:00"/>

Note  The <Schema> is declared as a namespace. To avoid namespace collisions when multiple XML-Data schemas are requested in different FOR XML queries, the namespace identifier (Schema3 in this example) changes with every query execution. The namespace identifier is made up of Schema followed by an integer.

C. Retrieve binary data

This query returns an employee photo from Employees table. Photo is an image column in the Employees table. The BINARY BASE64 option is specified in the query to return the binary data in base64-encoded format.

SELECT TOP 1 Photo
FROM Employees
WHERE EmployeeID=1
FOR XML RAW, BINARY BASE64

This is the result:

<row Photo="Binary data in base64 format"/>

D. Directly specify a URL to retrieve binary data

Because the RAW mode does not support addressing the binary data as URLs, this example creates a URL directly, using the DBOBJECT/TABLE[@PK1="v1"]/@COLUMN syntax. This returns a reference to an image data that can be used in subsequent operations.

SELECT TOP 1 EmployeeID,
   'dbobject/Employees[@EmployeeID='+CAST(EmployeeID as 
            nvarchar(4000))+']/@Photo' Photo
FROM Employees
FOR XML RAW

This is the result:

<row EmployeeID="3" 
     Photo="dbobject/Employees[@EmployeeID3]/@Photo"/>
 

See Also

SELECT