在SQL中运用For XML将数据集以XML形式展现，以下是备用数据
CREATE
TABLE
#tab(ID
INT
, Name
NVARCHAR
(
20
))
INSERT
INTO
#tab ( ID, Name )
SELECT
1
,N
'
chris
'
UNION
SELECT
2
,N
'
mark
'
UNION
SELECT
3
,N
'
andy
'
1、AUTO模式，将查询结果的每一行转为元素,元素名以表名命名，每一列（非NULL）转换成row元素的属性。
SELECT
*
FROM
#tab
FOR
XML AUTO
<
_x0023_tab
ID
="1"
Name
="chris"
/>
<
_x0023_tab
ID
="2"
Name
="mark"
/>
<
_x0023_tab
ID
="3"
Name
="andy"
/>
2、RAW模式，和AUTO类似，只是元素以row命名。
SELECT
*
FROM
#tab
FOR
XML
RAW
<
row
ID
="1"
Name
="chris"
/>
<
row
ID
="2"
Name
="mark"
/>
<
row
ID
="3"
Name
="andy"
/>
3、ELEMENTS，将每一列作为一个元素。
SELECT
*
FROM
#tab
FOR
XML AUTO,ELEMENTS
<
_x0023_tab
>
<
ID
>
1
</
ID
>
<
Name
>
chris
</
Name
>
</
_x0023_tab
>
<
_x0023_tab
>
<
ID
>
2
</
ID
>
<
Name
>
mark
</
Name
>
</
_x0023_tab
>
<
_x0023_tab
>
<
ID
>
3
</
ID
>
<
Name
>
andy
</
Name
>
</
_x0023_tab
>
4、PATH，用法更加灵活，可以自定义节点、元素、属性等。ROOT，增加根节点。
SELECT
*
FROM
#tab
FOR
XML PATH(
'
employee
'
),ELEMENTS
<
employee
>
<
ID
>
1
</
ID
>
<
Name
>
chris
</
Name
>
</
employee
>
<
employee
>
<
ID
>
2
</
ID
>
<
Name
>
mark
</
Name
>
</
employee
>
<
employee
>
<
ID
>
3
</
ID
>
<
Name
>
andy
</
Name
>
</
employee
>
SELECT
ID
AS
'
staff/@ID
'
,NAME
AS
'
staff/@Name
'
,
''
,
ID
AS
'
ID
'
,
''
, NAME
AS
'
Name
'
FROM
#tab
FOR
XML PATH(
'
employee
'
),ROOT(
'
all
'
)
<
all
>
<
employee
>
<
staff
ID
="1"
Name
="chris"
/>
<
ID
>
1
</
ID
>
<
Name
>
chris
</
Name
>
</
employee
>
<
employee
>
<
staff
ID
="2"
Name
="mark"
/>
<
ID
>
2
</
ID
>
<
Name
>
mark
</
Name
>
</
employee
>
<
employee
>
<
staff
ID
="3"
Name
="andy"
/>
<
ID
>
3
</
ID
>
<
Name
>
andy
</
Name
>
</
employee
>
</
all
>
