XML query in SQLSERVER: FORXML specifies AUTO

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:

<Testxml id = "1" name = "LeeWhoeeUniversity"/>
<Testxml id = "2" name = "DePaul"/>
<Testxml id = "3"/>

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:

<Product id = "1" name = "LeeWhoeeUniversity">
<Order orderid = "122" productid = "1" quantity = "10"/>
<Order orderid = "123" productid = "1" quantity = "100"/>
</Product>
<Product id = "2" name = "DePaul">
<Order orderid = "124" productid = "2" quantity = "20"/>
</Product>

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:

<Order orderid = "122" productid = "1" quantity = "10">
<Product id = "1" name = "LeeWhoeeUniversity"/>
</Order>
<Order orderid = "123" productid = "1" quantity = "100">
<Product id = "1" name = "LeeWhoeeUniversity"/>
</Order>
<Order orderid = "124" productid = "2" quantity = "20">
<Product id = "2" name = "DePaul"/>
</Order>

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, a <product> element with two <order> sub-elements is added to the result.

<Product id = "1" name = "LeeWhoeeUniversity">
<Order orderid = "122" productid = "1" quantity = "10"/>
<Order orderid = "123" productid = "1" quantity = "100"/>
</Product>
<Product id = "2" name = "DePaul">
<Order orderid = "124" productid = "2" quantity = "20"/>
</Product>

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:

<Product id = "1" name = "LeeWhoeeUniversity">
<Order orderid = "122" productid = "1" quantity = "10"/>
</Product>
<Product id = "1" name = "LeeWhoeeUniversity">
<Order orderid = "123" productid = "1" quantity = "100"/>
</Product>
<Product id = "2" name = "DePaul">
<Order orderid = "124" productid = "2" quantity = "20"/>
</Product>

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:

With [order]
As
(
Select 122 as orderid, 1 as productid, 10 as quantity
Union all
Select 125, 1 as productid, 100 as quantity
Union all
Select 123,2, 20
Union all
Select 124,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
Order by orderid

Result:

Id name orderid productid quantity
1 LeeWhoeeUniversity 122 1 10
2 depaster 123 2 20
1 LeeWhoeeUniversity 125 1 100

Then perform the XML query of the specified AUTO (that is, add for xml auto to the Statement). The AUTO mode test will generate the following results:

<Product id = "1" name = "LeeWhoeeUniversity">
<Order orderid = "122" productid = "1" quantity = "10"/>
</Product>
<Product id = "2" name = "DePaul">
<Order orderid = "123" productid = "2" quantity = "20"/>
</Product>
<Product id = "1" name = "LeeWhoeeUniversity">
<Order orderid = "125" productid = "1" quantity = "100"/>
</Product>

In this way, products with the same id and name are not connected together.

Summary

The above describes the XML query of the specified AUTO. The next article will continue to introduce the XML query in SQL SERVER using an instance: specifying the EXPLICIT query.
XML query in SQL SERVER: FOR XML specifies XML query in RAWSQL SERVER: FOR XML specifies AUTO
XML query in SQL SERVER: FOR XML specifies EXPLICIT
XML query in SQL SERVER: FOR XML specifies the XML type of PATH