SQL query for XML [raw | auto | explicit]

Basic syntax for XML clauses
In the for clause, specify the basic syntax of the XML mode as follows:
For XML mode [, xmldata] [, elements] [, binary base64]

Parameters
XML Mode
Specify the XML mode. The XML Schema determines the XML format.
The mode can be raw, auto, or explicit.

Xmldata
Specify the XML-data architecture to be returned. The document architecture is pre-designed as an embedded architecture.

Elements
If the elements option is specified, the column is returned as a child element. Otherwise, columns are mapped to the XML feature. This option is only supported in auto mode.

Binary base64
If the binary base64 option is specified, any binary data returned by the query is encoded in base64 format. This option must be specified when binary data is retrieved in raw and explicit modes. In auto mode, binary data is returned as a reference by default.

========================================================== ===

1. for XML raw
2. for XML auto
3. for XML explicit
4. Change the display tag to Chinese
5. multiple layers of the same table
6. multiple layers of different tables
7. null values for processing data and dates
============================
The data in the person table of the database is
Personname personage
Lisi 30
Zhangsan 30

1. ---------- [raw] ---------
Select [personname], [personage]
From [testdb]. [DBO]. [person]
For XML raw

Result:
<Row personname = "Lisi" Personage = "30"/>
<Row personname = "zhangsan" Personage = "30"/>

2. ---------- [auto] --------
Select [personname], [personage]
From [testdb]. [DBO]. [person]
For XML auto

Result:
<Testdb. DBO. Person personname = "Lisi" Personage = "30"/>
<Testdb. DBO. Person personname = "zhangsan" Personage = "30"/>

3. ----------- [explicit] --------
Select 1 as tag, null as parent
, Rtrim (personname) as [personbasic! 1! Personname]
, Rtrim (personage) as [personbasic! 1! Personage! XML]
From [testdb]. [DBO]. [person]
For XML explicit

Result:

<Personbasic personname = "Lisi">
<Personage> 30 </personage>
</Personbasic>
<Personbasic personname = "zhangsan">
<Personage> 30 </personage>
</Personbasic>

4. ----- change the display tag to Chinese -----
Select
1 As tag,
Null as parent,
Personname as [personnel! 1! Name! XML],
Personage as [personnel! 1! Age! XML]
From person
For XML explicit

Result:

<Personnel>
<Name> Lisi </Name>
<Age> 30 </age>
</Personnel>
<Personnel>
<Name> zhangsan </Name>
<Age> 30 </age>
</Personnel>

5. ----- multiple layers in the same table ----
Select
1 As tag,
Null as parent,
Rtrim (A. personname) as [personnel! 1! Name],
Null as [personnel information! 2! Age! XML]
From person

Union all

Select
2 As tag,
1 As parent,
Rtrim (A. personname ),
B. personage
From person B, person
Where a. personname = B. personname

Order by [personnel! 1! Name], tag

For XML explicit

Result:

<Personnel name = "Lisi">
<Personnel information>
<Age> 30 </age>
</Personnel Information>
</Personnel>
<Personnel name = "zhangsan">
<Personnel information>
<Age> 30 </age>
</Personnel Information>
</Personnel>

6. -------- multi-layer table -------
Select
1 As tag,
Null as parent,
Rtrim (A. personname) as [personnel! 1! Name],
Null as [personnel information! 2! Age! XML],
Null as [personnel information! 2! Occupation! XML]
From person

Union all

Select
2 As tag,
1 As parent,
Rtrim (A. personname ),
B. personage,
Rtrim (B. personjob)
From personinfo B, person
Where B. personname = A. personname
Order by [personnel! 1! Name], tag
For XML explicit

Result:

<Personnel name = "Lisi">
<Personnel information>
<Age> 30 </age>
<Occupation> teacher </occupation>
</Personnel Information>
</Personnel>
<Personnel name = "zhangsan">
<Personnel information>
<Age> 30 </age>
<Occupation> worker </occupation>
</Personnel Information>
</Personnel>

7. ------- null values for processing data and dates -------
The data in the person table of the database is
Personname personage personbirth (can be empty)
Lisi 30 1987-06-06
Zhangsan 30

When the field in the data table is null
The generated XML document does not contain this node.
To solve this problem
When necessary, you can set the number and date type
Convert to string type
In this way, you can receive empty strings.
(But I don't know how it works)

Select
1 As tag,
Null as parent,
Rtrim (personname) as [personnel! 1! Name! XML],
Rtrim (isnull (convert (char, personage), '') as [personnel! 1! Age! XML],
Rtrim (isnull (convert (char (10), personbirth, 120), '') as [personnel! 1! Date of birth! XML]
From person
For XML explicit

Result:

<Personnel>
<Name> Lisi </Name>
<Age> 30 </age>
<Date of Birth> </Date of Birth>
</Personnel>
<Personnel>
<Name> zhangsan </Name>
<Age> </age>
<Date of Birth> </Date of Birth>
</Personnel>

 

From: http://www.cnblogs.com/freeliver54/archive/2007/03/22/683563.html

 

==========================================

Taking the general database northwind as an example, the following T-SQL statement can output the list of IDs and names in categories in XML format and add the root node root to it:

select top 1
1 as tag,
null as parent,
null as [root! 1],
categoryid as [Categories! 2! Categoryid],
categoryname as [Categories! 2! Categoryname]
from categories
Union all
select
2,
1,
null,
categoryid,
categoryname
from categories
for XML explicit

output result: