Use RAW Mode with FOR XML

RAW mode transforms each row in the query result set into an XML element that has the generic identifier <row>, or the optionally provided element name. By default, each column value in the rowset that is not NULL is mapped to an attribute of the <row> element. If the ELEMENTS directive is added to the FOR XML clause, each column value is mapped to a subelement of the <row> element. Together with the ELEMENTS directive, you can optionally specify the XSINIL option to map NULL column values in the result set to an element that has the attribute, xsi:nil="true".

You can request a schema for the resulting XML. Specifying the XMLDATA option returns an in-line XDR schema. Specifying the XMLSCHEMA option returns an in-line XSD schema. The schema appears at the start of the data. In the result, the schema namespace reference is repeated for every top-level element.

The BINARY BASE64 option must be specified in the FOR XML clause to return the binary data in base64-encoded format. In RAW mode, retrieving binary data without specifying the BINARY BASE64 option will result in an error.

In This Section

This section contains the following examples:

• Example: Retrieving Product Model Information as XML

• Example: Specifying XSINIL with the ELEMENTS Directive

• Example: Requesting Schemas as Results with the XMLDATA and XMLSCHEMA Options

• Example: Retrieving Binary Data

• Example: Renaming the <row> Element

• Example: Specifying a Root Element for the XML Generated by FOR XML

• Example: Querying XMLType Columns

See Also

Add Namespaces to Queries with WITH XMLNAMESPACES
Use AUTO Mode with FOR XML
Use EXPLICIT Mode with FOR XML
Use PATH Mode with FOR XML
SELECT (Transact-SQL)
FOR XML (SQL Server)