select * from table name for xml
The following is a detailed introduction:
FOR clause
The FOR clause is used to specify BROWSE or XML options (BROWSE and XML are unrelated options).
Syntax
[ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT }
[ , XMLDATA ]
[ , ELEMENTS ]
[ , BINARY BASE64 ]
}
]
Parameters
BROWSE
Specifies that updates are allowed when viewing data in a DB-Library browse mode cursor. You can browse the table in your application if it contains a timestamp column (a column defined with the timestamp 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.
Explanation
The FOR BROWSE option cannot appear in a SELECT statement joined with the UNION operator.
XML
Specifies that query results will be returned as XML documents. One of the following XML modes must be specified: RAW, AUTO, EXPLICIT.
RAW
Obtains the query results and converts each row in the result set into an XML element, marked with the general identifier
Test:
select top 2 book number=BookNo, book name=BookName from sys_books as book information FOR XML raw
Result:
< p>AUTO
Return query results as a simple nested XML tree. Within the FROM clause, each table that has at least one column listed in the SELECT clause is represented as an XML element. The columns listed in the SELECT clause map to the appropriate element attributes.
Test:
select top 2 book number=BookNo, book name=BookName from sys_books as book information FOR XML auto
Result:
< p>EXPLICIT
Specifies an explicit definition of the shape of the resulting XML tree. Using this pattern requires that the query be written in a specific way to explicitly specify additional information about the desired nesting.
XMLDATA
Returns the schema without adding the root element to the result. If XMLDATA is specified, it will be appended to the document.
ELEMENTS
The specified columns are returned as child elements. Otherwise, the column is mapped to an XML attribute.
Test:
select top 2 Book number=BookNo, book name=BookName from sys_books as book information FOR XML AUTO,ELEMENTS
Result:
NARY BASE64
The specified query returns binary data in binary base64 encoding format. This option must be specified when using RAW and EXPLICIT modes to retrieve binary data.
This is the default value in AUTO mode.