FOR Clause

FOR Clause

FOR clause is used to specify either the BROWSE or the XML option (BROWSE and XML are unrelated options).

Syntax

[ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT }
            [ , XMLDATA ]
            [ , ELEMENTS ]
            [ , BINARY BASE64 ]
        }
]

Arguments

BROWSE

Specifies that updates be allowed while viewing the data in a DB-Library browse mode cursor. A table can be browsed in an application if the table includes a time-stamped column (defined with the timestamp data type), the table has a unique index, and the FOR BROWSE option is at the end of the SELECT statement(s) sent to SQL Server. For more information, see Browse Mode.

Note  It is not possible to use the <lock_hint> HOLDLOCK in a SELECT statement that includes the FOR BROWSE option.

The FOR BROWSE option cannot appear in SELECT statements joined by the UNION operator.

XML

Specifies that the results of a query are to be returned as an XML document. One of these XML modes must be specified: RAW, AUTO, EXPLICIT. For more information about XML data and SQL Server, see Retrieving XML Documents Using FOR XML.

RAW

Takes the query result and transforms each row in the result set into an XML element with a generic identifier <row /> as the element tag. For more information, see Using RAW Mode.

AUTO

Returns query results in a simple, nested XML tree. Each table in the FROM clause, for which at least one column is listed in the SELECT clause, is represented as an XML element. The columns listed in the SELECT clause are mapped to the appropriate element attributes. For more information, see Using AUTO Mode.

EXPLICIT

Specifies that the shape of the resulting XML tree is defined explicitly. Using this mode, queries must be written in a particular way so that additional information about the desired nesting is specified explicitly. For more information, see Using EXPLICIT Mode.

XMLDATA

Returns the schema, but does not add the root element to the result. If XMLDATA is specified, it is appended to the document.

ELEMENTS

Specifies that the columns are returned as subelements. Otherwise, they are mapped to XML attributes.

BINARY BASE64

Specifies that the query returns the binary data in binary base64-encoded format. In retrieving binary data using RAW and EXPLICIT mode, this option must be specified. This is the default in AUTO mode.