How to Use the SELECT statement for XML in MSSQL 2000 to generate xml files

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.