XML query in SQL Server: forxml specify auto

XML query in SQL Server: For XML specify Auto

Objective

In SQL Server, an XML query can specify Raw,auto,explicit,path. This article introduces some examples of XML queries that specify auto in SQL Server.

Basic example

Fragment 1:

With Testxml
As
(
Select 1 as Id,n ' leewhoeeuniversity ' as name
UNION ALL
Select 2,n ' DePaul '
UNION ALL
Select 3, NULL
)
Select Id,name from Testxml FOR XML auto

Results:

<testxml id= "1" name= "leewhoeeuniversity"/>
<testxml id= "2" name= "DePaul"/>
<testxml id= "3"/>

Use the table name as the element name, replacing row in raw mode.

Look at the multiple table query below (fragment 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 ' DePaul '
)
SELECT * from Product,[order] where [order].productid=product.id for Xmlauto

Results:

<product id= "1" name= "leewhoeeuniversity" >
<order orderid= "122" productid= "1" quantity= "ten"/>
<order orderid= "123" productid= "1" quantity= "a"/>
</product>
<product id= "2" name= "DePaul" >
<order orderid= "124" productid= "2" quantity= "/>"
</product>

Table name Order Sensitive

(See the bold part of the query above)

If you change the product and order position, fragment 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 ' DePaul '
)
SELECT * FROM [order],product where [order].productid=product.id FOR XML auto

Results:

<order orderid= "122" productid= "1" quantity= "ten" >
<product id= "1" name= "leewhoeeuniversity"/>
</order>
<order orderid= "123" productid= "1" quantity= ">"
<product id= "1" name= "leewhoeeuniversity"/>
</order>
<order orderid= "124" productid= "2" quantity= ">"
<product id= "2" name= "DePaul"/>
</order>

Of course, Auto mode can also specify Elements,binary BASE64, with Raw. (XML query in SQL Server: FOR XML specifies RAW)

A method of AUTO mode test in the returned XML forming process

The AUTO mode determines the form of the XML returned based on the query. When deciding how to nest elements, the AUTO mode heuristics compare column values in adjacent rows. All types of columns except the ntext, text, image, and XML types are compared. (n) columns of the varchar (max) and varbinary (max) types are also compared.

The first SQL statement that specifies auto (fragment 2) has the result set:

ID name OrderID ProductID quantity
1 leewhoeeuniversity 122 1 10
1 leewhoeeuniversity 123 1 100
2 DePaul 124 2 20

The AUTO mode heuristics compare all the values of table product (Id and Name columns). Because the IDs and Name columns of the first two rows have the same value, a <product> element with two <order> child elements is added to the result.

<product id= "1" name= "leewhoeeuniversity" >
<order orderid= "122" productid= "1" quantity= "ten"/>
<order orderid= "123" productid= "1" quantity= "a"/>
</product>
<product id= "2" name= "DePaul" >
<order orderid= "124" productid= "2" quantity= "/>"
</product>

Text Type of Special

If you change the Name column to the text type. The AUTO mode heuristics do not compare the values of this type, but consider them to be different.

See Code fragment 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 ' DePaul '
SELECT * from @product as product, @order as (order) where [order].productid=product.id for Xmlauto

Results:

<product id= "1" name= "leewhoeeuniversity" >
<order orderid= "122" productid= "1" quantity= "ten"/>
</product>
<product id= "1" name= "leewhoeeuniversity" >
<order orderid= "123" productid= "1" quantity= "a"/>
</product>
<product id= "2" name= "DePaul" >
<order orderid= "124" productid= "2" quantity= "/>"
</product>

The items named with leewhoeeuniversity in the above results are divided into two product.

Effect of result set ordering on auto test

Look at the first SQL statement that specifies auto, but change the OrderID to not connect items with the same ID and name in their result set:

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 ' DePaul '
)
SELECT * from Product,[order] where [order].productid=product.id
ORDER BY OrderID

Results:

ID name OrderID ProductID quantity
1 leewhoeeuniversity 122 1 10
2 DePaul 123 2 20
1 leewhoeeuniversity 125 1 100

Then the XML query for the specified auto (that is, add for XML auto on the statement), and Auto mode temptation produces the following results:

<product id= "1" name= "leewhoeeuniversity" >
<order orderid= "122" productid= "1" quantity= "ten"/>
</product>
<product id= "2" name= "DePaul" >
<order orderid= "123" productid= "2" quantity= "/>"
</product>
<product id= "1" name= "leewhoeeuniversity" >
<order orderid= "productid=" 1 "quantity="/>
</product>

This product with the same ID and name is not connected.

Summarize

The above XML query for the specified auto is done, and the next article will continue with an example of an XML query in SQL Server: Specify a explicit query.
XML query in SQL Server: For XML specify XML query in Rawsql server: For XML specify Auto
XML query in SQL Server: FOR XML specified explicit
XML query in SQL Server: For XML specify path about XML types