FOR (Transact‑SQL) Syntax

On this page

Overview
Syntax
Examples
Remarks
Related topics

Overview

The FOR clause in Transact‑SQL is used to specify how a SELECT statement returns its result set. It supports three options: XML, JSON, and BROWSE. This page provides the syntax, usage notes, and code samples.

Syntax

SELECT ... 
    FROM ...
    [WHERE ...]
    [FOR { XML | JSON | BROWSE } 
        [ , { XML | JSON | BROWSE } ] ... 
        [ , { RAW | AUTO | PATH } ] 
        [ , { ELEMENTS | ELEMENTS XSINIL } ] 
        [ , ROOT('root_name') ] 
        [ , TYPE ] 
        [ , XMLSCHEMA ] 
        [ , INCLUDE_NULL_VALUES ] 
        [ , WITHOUT_ARRAY_WRAPPER ] ];

Only one of XML, JSON, or BROWSE can be used per SELECT statement.

Examples

1. Generating XML

SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice > 1000
FOR XML PATH('Product'), ROOT('Products');

2. Generating JSON

SELECT TOP (5) 
    BusinessEntityID, 
    FirstName, 
    LastName 
FROM Person.Person
FOR JSON AUTO, ROOT('People');

3. Using BROWSE

SELECT * 
FROM AdventureWorks2019.HumanResources.Employee 
WHERE OrganizationLevel < 3
FOR BROWSE;

Remarks

FOR XML creates an XML document from the result set. Use modifiers like RAW, AUTO, or PATH to control shape.
FOR JSON returns JSON text. Modifiers AUTO and PATH affect nesting.
FOR BROWSE returns a cursor‑compatible result set used by OLE DB consumers.
When using FOR JSON, the INCLUDE_NULL_VALUES option retains null properties in the output.
These clauses cannot be combined with FOR UPDATE or FOR READ ONLY.