Current location - Plastic Surgery and Aesthetics Network - Plastic surgery and beauty - sql server cannot find data type XML when creating table
sql server cannot find data type XML when creating table

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 HOLDLOCK cannot be used in a SELECT statement with the FOR BROWSE option.

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 as the element.

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:

B0011B002Sports News (Shanghai)

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.