- 综述
MSSQL2005引入了XML的数据类型，相应的T-SQL也提供了大量的语句和函数用于支持对XML的操作，以及维护关系型数据和XML之间的转换。
- 对XML查询
T-SQL提供了以下几个针对
XML类型的查询函数：
query(XQuery)：利用
Xquery查询符合条件的元素集合。
value(XQuery,Type)：利用
Xquery查询具体元素的值，
Type定义值的类型。
exist(XQuery)：利用
Xquery查询是否存在符合条件的元素。
nodes(XQuery)：利用
Xquery获得一个结果集。
下面是详细的示例：
查询XML
1
DECLARE
@Doc
xml
2
SET
@Doc
=
'
<Books><Book ID="10001"><Name>CLR VIA C#</Name></Book><Book ID="10002"><Name>.net高级调试</Name></Book></Books>
'
3
SELECT
@Doc
4
5
--
获取有ID属性的Book元素
6
SELECT
@Doc
.query(
'
Books/Book[@ID]
'
)
7
8
--
获取第个Name元素的值
9
SELECT
@Doc
.value(
'
(Books/Book/Name)[2]
'
,
'
nvarchar(128)
'
)
10
11
--
是否存在ID=10003的Book元素
12
SELECT
@Doc
.exist(
'
Books/Book[@ID=10003]
'
)
13
14
--
从一个由Book元素组成的结果集中获取所有Name元素
15
SELECT
T.x.query(
'
Name
'
)
FROM
@Doc
.nodes(
'
Books/Book
'
)
as
T(x)
16
17
GO
- 修改XML XML的修改操作包括对元素及其属性和值的增加、修改、删除。 T-SQL提供 modify函数以实现对 XML的修改操作。
-
SET modify(
'
insert
""
before XQuery
'
)：在符合
Xquery条件的元素之前插入元素。
-
SET modify(
'
insert attribute
""
into XQuery
'
)： 在符合
Xquery条件的元素中插入属性。
-
SET modify(
'
replace value of XQuery with
""'
)：修改符合
Xquery条件的元素内容。
-
SET modify(
'
delete XQuery
'
)：删除符合
Xquery条件的元素内容。
-
下面是详细的示例：
修改XML
1
DECLARE
@Doc
xml
2
SET
@Doc
=
'
<Books><Book ID="10001"><Name>CLR VIA C#</Name></Book><Book ID="10002"><Name>.net高级调试</Name></Book></Books>
'
3
SELECT
@Doc
4
5
--
增加个Book元素
6
SET
@Doc
.modify(
'
insert <Book ID="10003"><Name>WF本质论</Name></Book> before (Books/Book[@ID])[2]
'
)
7
SELECT
@Doc
8
9
--
修改ID=10003的Book元素下Name元素的值
10
SET
@Doc
.modify(
'
replace value of (Books/Book[@ID=10003]/Name/text())[1] with "WCF揭秘"
'
)
11
SELECT
@Doc
12
13
--
删除ID=10003的Book元素
14
SET
@Doc
.modify(
'
delete Books/Book[@ID=10003]
'
)
15
SELECT
@Doc
16
17
--
第个ID=10001的Book元素增加个属性
18
SET
@Doc
.modify(
'
insert attribute Author{"Mario Hewardt"} into (Books/Book[@ID=10001])[1]
'
)
19
SELECT
@Doc
20
21
--
修改第个ID=10001的Book元素的Author属性
22
SET
@Doc
.modify(
'
replace value of (Books/Book[@ID=10001]/@Author)[1] with "Jeffrey Richter"
'
)
23
SELECT
@Doc
24
25
--
删除第个ID=10001的Book元素的Author属性
26
SET
@Doc
.modify(
'
delete Books/Book[@ID=10001]/@Author
'
)
27
SELECT
@Doc
28
29
GO
- 返回XML
T-SQL利用
FOR XML语句将
SELECT语句返回的行聚合成
XML。
MSSQL支持以下四种模式：
RAW、
AUTO、
PATH 、
EXPLICIT，下面只讨论前三个模式。
FOR XML AUTO [,ELEMENTS] [,ROOT(
''
)]：表名定义成每条记录的元素名，字段名默认定义成属性，也可以设置成元素。
FOR XML RAW(
''
) [,ELEMENTS] [,ROOT(
''
)]：按
RAW定义每条记录的元素名，字段名默认定义成属性，也可以设置成元素。
FOR XML PATH(
''
) [,ROOT(
''
)]：按
PATH定义具体的
XML结构。
FOR XML语句可以配合字段别名一起定义生成的
XML结构。具体可以参考下面的示例。
返回XML
1
--
测试表和测试数据
2
CREATE
TABLE
[
dbo
]
.
[
Books
]
(
3
[
ID
]
[
bigint
]
NOT
NULL
,
4
[
Name
]
[
nvarchar
]
(
128
)
NOT
NULL
5
)
ON
[
PRIMARY
]
6
GO
7
8
INSERT
INTO
[
dbo
]
.
[
Books
]
9
VALUES
10
(
10001
,
'
CLR VIA C#
'
),
11
(
10002
,
'
.net高级调试
'
)
12
GO
13
14
--
示例
15
SELECT
ID,NAME
FROM
[
dbo
]
.
[
Books
]
16
17
SELECT
ID,NAME
FROM
[
dbo
]
.
[
Books
]
18
FOR
XML AUTO
19
20
SELECT
ID,NAME
FROM
[
dbo
]
.
[
Books
]
21
FOR
XML AUTO ,ELEMENTS ,ROOT(
'
Books
'
)
22
23
SELECT
ID
as
'
BookID
'
,NAME
as
'
BookName
'
FROM
[
dbo
]
.
[
Books
]
24
FOR
XML
RAW
25
26
SELECT
ID,NAME
FROM
[
dbo
]
.
[
Books
]
27
FOR
XML
RAW
(
'
Book
'
) ,ELEMENTS ,ROOT(
'
Books
'
)
28
29
SELECT
ID,NAME
FROM
[
dbo
]
.
[
Books
]
30
FOR
XML PATH(
''
)
31
32
SELECT
ID
as
'
Detail/@ID
'
,NAME
as
'
Detail/Name
'
FROM
[
dbo
]
.
[
Books
]
33
FOR
XML PATH(
'
Book
'
), ROOT(
'
Books
'
)
34
35
GO
需要特别声明一下，FOR XML PATH语句可以用来方便的实现列合并。
比如要对上面的Book表合并Name字段，以“;”分隔每项：
1
SELECT
STUFF
((
SELECT
'
;
'
+
Name
FROM
[
dbo
]
.
[
Books
]
FOR
XML PATH(
''
))
2
,
1
,
1
,
''
)
