Export (0) Print
Expand All
Expand Minimize
2 out of 8 rated this helpful - Rate this topic

FOR Clause (Transact-SQL)

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

Topic link icon Transact-SQL Syntax Conventions


[ FOR { BROWSE | <XML> } ]
<XML> ::=
XML 
{ 
    { RAW [ ( 'ElementName' ) ] | AUTO } 
    [ 
        <CommonDirectives> 
        [ , { XMLDATA | XMLSCHEMA [ ( 'TargetNameSpaceURI' ) ] } ] 
        [ , ELEMENTS [ XSINIL | ABSENT ] 
    ]
  | EXPLICIT 
    [ 
        <CommonDirectives> 
        [ , XMLDATA ] 
    ]
  | PATH [ ( 'ElementName' ) ] 
    [
        <CommonDirectives> 
        [ , ELEMENTS [ XSINIL | ABSENT ] ]
    ]
} 

<CommonDirectives> ::= 
[ , BINARY BASE64 ]
[ , TYPE ]
[ , ROOT [ ( 'RootName' ) ] ]

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 timestamp column, the table has a unique index, and the FOR BROWSE option is at the end of the SELECT statements sent to an instance of SQL Server.

ms173812.note(en-US,SQL.90).gifNote:
You cannot use the <lock_hint> HOLDLOCK in a SELECT statement that includes the FOR BROWSE option.

FOR BROWSE cannot appear in SELECT statements that are joined by the UNION operator.

ms173812.note(en-US,SQL.90).gifNote:
When the unique index key columns of a table are nullable, and the table is on the inner side of an outer join, the index is not supported by browse mode.

XML

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

RAW [ ('ElementName') ]

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. You can optionally specify a name for the row element. The resulting XML output uses the specified ElementName as the row element generated for each row. For more information, see Using RAW Mode and RAW Mode Enhancements.

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 and AUTO Mode Enhancements.

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 and EXPLICIT Mode Enhancements.

XMLDATA

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

XMLSCHEMA [ ('TargetNameSpaceURI') ]

Returns inline XSD schema. You can optionally specify a target namespace URI when you specify this directive, which returns the specified namespace in the schema. For more information, see Inline XSD Schema Generation.

ELEMENTS

Specifies that the columns are returned as subelements. Otherwise, they are mapped to XML attributes. This option is supported in RAW, AUTO and PATH modes only. For more information, see RAW Mode Enhancements.

XSINIL

Specifies that an element with xsi:nil attribute set to True be created for NULL column values. This option can only be specified with ELEMENTS directive. For more information, see Generating Elements for NULL Values Using the XSINIL Parameter.

ABSENT

Indicates that for null column values, corresponding XML elements will not be added in the XML result. Specify this option only with ELEMENTS.

PATH [ ('ElementName') ]

Generates a <row> element wrapper for each row in the result set. You can optionally specify an element name for the <row> element wrapper. If an empty string is provided, such as FOR XML PATH ('') ), a wrapper element is not generated. Using PATH may provide a simpler alternative to queries written using the EXPLICIT directive. For more information, see Using PATH Mode.

BINARY BASE64

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

TYPE

Specifies that the query returns results as xml type. For more information, see TYPE Directive in FOR XML Queries.

ROOT [ ('RootName') ]

Specifies that a single top-level element be added to the resulting XML. You can optionally specify the root element name to generate. If the optional root name is not specified, the default <root> element is added.

The following example specifies FOR XML AUTO with the TYPE and XMLSCHEMA options. Because of the TYPE option, the result set is returned to the client as an xml type. The XMLSCHEMA option specifies that the inline XSD schema is included in the XML data returned, and the ELEMENTS option specifies that the XML result is element-centric.

USE AdventureWorks;
GO
SELECT ContactID, FirstName, LastName, Phone
FROM Person.Contact
WHERE LastName LIKE 'G%'
ORDER BY LastName, FirstName 
FOR XML AUTO, TYPE, XMLSCHEMA, ELEMENTS XSINIL;

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.