How to Use the SELECT statement for XML in MSSQL 2000 to generate xml files For clause
The for clause is used to specify browse or XML options (browse and XML are irrelevant options ).
Syntax
[For {browse | XML {raw | auto | explicit}
[,Xmldata]
[,Elements]
[,Binary base64]
}
]
Parameters
Browse
Specifies that data in the DB-library browser mode cursor can be updated. If the table contains a timestamp column (TimestampColumns defined by the data type). The table has a unique index and the for browse option is sent to SQL server at the end of the SELECT statement.Program.
Description<Lock_hint> holdlock cannot be used in select statements containing the for browse option.
The for browse option cannot appear in the SELECT statement joined by the Union operator.
XML
The specified query result is returned as an XML document. You must specify one of the following XML modes: raw, auto, and explicit.
Raw
Obtain the query results and convert the rows in the result set to XML elements. Use the General identifier <row/> as the element tag.
Test:
Select top 2 Book No. = Bookno, book name = Bookname from sys_books as library information for XML raw
Result:
< Row library number = " B001 " Book name = " 1 " /> < Row library number = " B002 " Book name = " Sports News (Shanghai) " />
Auto
Return the query result using a simple Nested XML tree. In the from clause, each table listed in at least one column in the select clause is represented as an XML element. Columns listed in the select clause map to appropriate element features.
Test:
Select top 2 Book No. = Bookno, book name = Bookname from sys_books As Library Information for XML auto
Result:
< Library Information Library no. = " B001 " Book name = " 1 " /> < Library Information Library no. = " B002 " Book name = " Sports News (Shanghai) " />
Explicit
Specify the shape of the XML tree explicitly defined. In this mode, you need to write a query in a specific way to explicitly specify additional information about the expected nesting.
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
The specified column is returned as a child element. Otherwise, columns are mapped to the XML feature.
Test:
Select top 2 Book No. = Bookno, book name = Bookname from sys_books As Library Information for XML auto, Elements
Result:
< Book Information > < Book No. > B001 </ Book No. > < Book name > 1 </ Book name > </ Book Information > < Book Information > < Book No. > B002 </ Book No. > < Book name > Sports News (Shanghai) </ Book name > </ Book Information >
Nary 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.