Transact-SQL for Clause

For clause (TRANSACT-SQL)

The for clause is used to specify browse or XML options. Browse and XML are irrelevant options.

Transact-SQL syntax conventions

Syntax

 
[For {browse | <XML >}] <XML >::= XML {Raw [('elementname')] | auto} [<commondireves ves> [, {xmldata | XMLSCHEMA [('targetnamespaceuri ')]}] [, elements [xsinil | absent] | explicit [<commondireves ves> [, xmldata] | path [('elementname')] [<commondireves ves> [, elements [xsinil | absent]} <commondirectives >:= [, binary base64] [, type] [, root [('rootname')]
 

Parameters

Browse

Specifies that data can be updated when you view data in the DB-library browser mode cursor. If the table contains the timestamp column and the table has a unique index, and the for browse option is at the end of the SELECT statement sent to the SQL server instanceProgram.

Note:
<Lock_hint> holdlock cannot be used in select statements including for browse options.

 

For browse cannot appear in select statements joined by the Union operator.

Note:
If the unique index key column of a table can be empty and the table is on the inner side of an external join, indexes are not supported in browsing mode.

 

XML

Returns the query results in XML format. You must specify one of the following XML modes: raw, auto, and explicit. For more information about XML data and SQL Server, see using for XML to construct XML.

Raw [('elementname')]

Obtain the query results and convert each row in the result set to an XML Element marked with a general identifier <row/>. (Optional) You can specify a name for the row element. The obtained XML output uses the specified elementname as the Row Element generated for each row. For more information, see enhancements in raw and raw modes.

Auto

Return the query result using a simple Nested XML tree. Each table listed in the select clause at least once in the from clause is expressed as an XML element. Columns listed in the select clause map to the appropriate element attributes. For more information, see auto mode and auto mode enhancements.

Explicit

Explicitly defines the shape of the result XML tree. To use this mode, you must write a query in a specific way, that is, explicitly specifying other information related to the desired nesting. For more information, see enhanced functions using the explicit mode and the explicit mode.

Xmldata

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

XMLSCHEMA [('targetnamespaceuri ')]

Returns the inline XSD Schema. If you specify this command (used to return the specified namespace in the schema), you can specify the target namespace URI. For more information, see inline XSD Schema generation.

Elements

The specified column is returned as a child element. Otherwise, columns are mapped to XML attributes. This option is only supported in raw, auto, and path modes. For more information, see RAW mode enhancements.

Xsinil

Specify the null column value to create an element whose xsi: Nil attribute is set to true. This option can only be specified with the elements command. For more information, see generating elements corresponding to null values using the xsinil parameter.

Absent

Indicates that the corresponding XML element is not added to the XML result for null column values. This option can only be specified with elements.

Path [('elementname')]

Generates <row> element packaging for each row in the result set. (Optional) You can specify the element name for <row> element packaging. If an empty string (for example, XML Path ('') is provided, no packaging element is generated. The use of path may provide a simpler alternative for queries written using the explain it command. For more information, see use path mode.

Binary base64

Returns binary base64 encoded binary data. This option must be specified when binary data is retrieved in raw and explicit modes. This is the default value in auto mode.

Type

Specify that the query returns results in XML format. For more information, see type commands in for XML queries.

Root [('rootname')]

Add a top-level element to the result XML. (Optional) Name of the root element to be generated. If no optional root name is specified, the default <root> element is added.

Example

The following example specifiesTypeAndXMLSCHEMAOptionalFor XML auto. BecauseTypeTherefore, the result set is returned to the client as the XML type.XMLSCHEMAOption specifies to include the inline XSD Schema in the returned XML data, whileElementsOption specifies that the XML result is element-centric.

Copy code

 
Use adventureworks; goselect contactid, firstname, lastname, phonefrom person. contactwhere lastname like 'G % 'order by lastname, firstnamefor XML auto, type, XMLSCHEMA, elements xsinil;
 

 
Http://msdn2.microsoft.com/zh-cn/library/ms173812.aspx