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,'')

 

 

 

•  

转载于:https://www.cnblogs.com/wuhong/archive/2011/04/15/2017281.html