XML query in SQLSERVER: FORXML specifies AUTO preface. In SQLSERVER, RAW, AUTO, EXPLICIT, and PATH can be specified for XML query. This article uses some examples to introduce the XML query of AUTO in SQLSERVER. Basic example Segment 1: withTestXmlas (select1asid, NLeeWhoeeUniversityasnameunionall
XML query in SQL server: for xml specifies AUTO preface. in SQL SERVER, RAW, AUTO, EXPLICIT, and PATH can be specified FOR XML query. This article uses some examples to introduce the XML query of AUTO in SQL SERVER. Basic example Segment 1: with TestXml as (select 1 as id, N 'leewhoeeuniversity 'as name union all
XML query in SQL SERVER: FOR XML specifies AUTO Preface
In SQL SERVER, you can specify RAW, AUTO, EXPLICIT, and PATH for XML queries. This article uses some examples to introduce the XML query of AUTO in SQL SERVER.
Basic example
Part 1:
With TestXml
As
(
Select 1 as id, N 'leewhoeeuniversity 'as name
Union all
Select 2, N 'depop'
Union all
Select 3, null
)
Select id, name from testxml for xml auto
Result:
Use the table name as the element name, which replaces the "row" in raw mode ".
The following figure shows the multi-Table query (Segment 2 ):
With [order]
As
(
Select 122 as orderid, 1 as productid, 10 as quantity
Union all
Select 123,1 as productid, 100 as quantity
Union all
Select 124,2, 20
Union all
Select 125, 3, 5
),
Product
As
(
Select 1 as id, N 'leewhoeeuniversity 'as name
Union all
Select 2, N 'depop'
)
Select * from product, [order] where [order]. productid = product. id for xmlauto
Result:
Table Name sequence sensitivity
(See the bold Section in the above query)
If you change the product and order positions, Segment 3:
With [order]
As
(
Select 122 as orderid, 1 as productid, 10 as quantity
Union all
Select 123,1 as productid, 100 as quantity
Union all
Select 124,2, 20
Union all
Select 125, 3, 5
),
Product
As
(
Select 1 as id, N 'leewhoeeuniversity 'as name
Union all
Select 2, N 'depop'
)
Select * from [order], product where [order]. productid = product. id for xml auto
Result:
Of course, you can also specify ELEMENTS, BINARY BASE64, and RAW in AUTO mode. (XML query in SQL SERVER: FOR XML specifies RAW)
Testing Method of AUTO Mode in returned XML formatting Process
AUTO Mode determines the XML format returned Based on the query. When determining the nested element method, the AUTO mode test method compares the column values in adjacent rows. All types of columns except ntext, text, image, and xml are compared. (N) varchar (max) and varbinary (max) columns are also compared.
The result set of the first SQL statement (Segment 2) specified for AUTO is as follows:
Id name orderid productid quantity
1 LeeWhoeeUniversity 122 1 10
1 LeeWhoeeUniversity 123 1 100
2 depaster 124 2 20
The AUTO mode test method compares all values of the table product (Id column and Name column ). Because the Id column and Name column of the first two rows have the same value Sub-Element Element.
Special text Type
If you change the Name column to the text type. In AUTO mode, the test method does not compare the values of this type, but considers these values to be different.
See section 4 below:
Declare @ order table (orderid int, productid int, quantity int)
Declare @ product table (id int, name text)
Insert into @ order
Select 122 as orderid, 1 as productid, 10 as quantity
Union all
Select 123,1 as productid, 100 as quantity
Union all
Select 124,2, 20
Union all
Select 125, 3, 5
Insert into @ product
Select 1, N 'leewhoeeuniversity'
Union all
Select 2, N 'depop'
Select * from @ product as product, @ order as [order] where [order]. productid = product. id for xmlauto
Result:
In the above results, items with the same name as LeeWhoeeUniversity are divided into two products.
Influence of result set sorting on AUTO Testing
Let's look at the first SQL statement that specifies AUTO, but change orderid so that the items with the same id and name in the result set are not connected together: