T-SQL之操作XML
- 综述
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,'')
