** 使用 Microsoft SQL Server 2000 ** ** 的 XML ** ** 查询 **
郑佐 2005-6-28
由于 XML 本身的诸多优点, XML 技术已被广泛的使用,目前的好多软件技术同 XML 紧密相关,比如微软的 .net 平台对 xml 提供了强大的支持,提供 System.Xml 以及其子命名空间下的类型来操作 xml 。 Ado.net 通过核心类型 DataSet 出色的把关系型数据库同 xml 进行了紧密集成。由于平常许多开发人员使用 .net 来操作 Sql server 的到数据集后再转换成 xml ,所以往往忽略 Transact-SQL 查询生成 XML 数据的强大功能。对于一些项目使用 XML 查询直接通过 SQL 生成 xml 会来的更为简便,所以我通过在实际项目中的使用和查阅一些资料写成一个知识点,一是温故而知新,二是对于一些开发者刚好需要这方面的技术而还没有找到比较快捷的学习方式提供一条途径。
在 SQL SERVER 2000 中查询生成 XML 的语法表达式比较简洁,整个语法如下:
SELECT
1<select_list>
2
3FROM <table_source>
4
5WHERE <search_condition>
6
7FOR XML AUTO | RAW | EXPLICIT [,XMLDATA ] [,ELEMENTS] [,BINARY BASE64]
8
9下面我将以 Northwind 数据库来演示上面的表达式中所包含的各项功能,下面的查询语句和返回结果都通过 SQL SERVER 2000 查询分析器来执行和得到。
10
11** 一. ** ** 使用 ** ** AUTO ** ** 模式 ** **
12
13**
14
15该模式我认为在生成单表 xml 数据方面是用得最多的,能满足一般的需要。先来看他的简单查询。
16
171 .简单查询
18
19查询语句:
20
21SELECT CategoryID,
22
23CategoryName
24
25FROM Categories
26
27WHERE CategoryID < 3 FOR XML AUTO
28
29返回结果:
30
31<categories categoryid="1" categoryname="Beverages"></categories>
32<categories categoryid="2" categoryname="Condiments"></categories>
33
34也可以使用别名,
35
36查询语句:
37
38SELECT CategoryID AS ID,
39
40CategoryName,
41
42GetDate() as CurrDate
43
44FROM Categories MyTable
45
46WHERE CategoryID < 3 FOR XML AUTO
47
48返回结果:
49
50<mytable categoryname="Beverages" currdate=" 2005-06-24 T11:09:52.937" id="1"></mytable>
51<mytable categoryname="Condiments" currdate=" 2005-06-24 T11:09:52.937" id="2"></mytable>
52
532 .连接查询
54
55以两个表为例,
56
57查询语句:
58
59SELECT Categories.CategoryID,
60
61Categories.CategoryName,
62
63ProductID,
64
65ProductName
66
67FROM Categories
68
69JOIN Products ON Categories.CategoryID = Products.CategoryID AND ProductID <5
70
71WHERE Categories.CategoryID < 3 FOR XML AUTO
72
73返回结果:
74
75<categories categoryid="1" categoryname="Beverages">
76<products productid="1" productname="Chai"></products>
77</categories>
78<categories categoryid="2" categoryname="Condiments">
79<products productid="2" productname="Chang"></products>
80</categories>
81
82可以看到表连接查询可以生成分层次的 Xml ,不过需要注意的是 SELECT 子句中的父表的列要排在子表的列的前面,否则会出现你不想看到的结果,如:
83
84查询语句:
85
86SELECT ProductID,Categories.CategoryID,Categories.CategoryName,ProductName
87
88FROM Categories
89
90JOIN Products ON Categories.CategoryID = Products.CategoryID and ProductID <5
91
92WHERE Categories.CategoryID <3 FOR XML AUTO
93
94返回结果:
95
96<products productid="1" productname="Chai">
97<categories categoryid="1" categoryname="Beverages"></categories>
98</products>
99<products productid="2" productname="Chang">
100<categories categoryid="1" categoryname="Beverages"></categories>
101</products>
102<products productid="3" productname="Aniseed Syrup">
103<categories categoryid="2" categoryname="Condiments"></categories>
104</products>
105<products productid="4" productname="Chef Anton's Cajun Seasoning">
106<categories categoryid="2" categoryname="Condiments"></categories>
107</products>
108
1093 .使用 ELEMENTS 选项
110
111使用该选项可以生成以元素为中心的 Xml 表示,默认为属性方式,不过属性方式节省空间。需要注意的是使用 ELEMENTS 选项是一种全是或全否的形式,不能得到一部分是以元素表示而另一部分以属性表示的 Xml 数据。
112
113查询语句:
114
115SELECT CategoryID,
116
117CategoryName
118
119FROM Categories
120
121WHERE CategoryID < 3
122
123FOR XML AUTO, ELEMENTS
124
125返回结果:
126
127<categories>
128<categoryid>1</categoryid>
129<categoryname>Beverages</categoryname>
130</categories>
131<categories>
132<categoryid>2</categoryid>
133<categoryname>Condiments</categoryname>
134</categories>
135
136在连接查询时,
137
138查询语句:
139
140SELECT Categories.CategoryID,
141
142ProductID,
143
144ProductName
145
146FROM Categories
147
148JOIN Products ON Categories.CategoryID = Products.CategoryID and ProductID <4
149
150WHERE Categories.CategoryID <3 FOR XML AUTO, ELEMENTS
151
152返回结果:
153
154<categories>
155<categoryid>1</categoryid>
156<products>
157<productid>1</productid>
158<productname>Chai</productname>
159</products>
160<products>
161<productid>2</productid>
162<productname>Chang</productname>
163</products>
164</categories>
165<categories>
166<categoryid>2</categoryid>
167<products>
168<productid>3</productid>
169<productname>Aniseed Syrup</productname>
170</products>
171</categories>
172
1734 .检索对二进制数据的 XPath 引用
174
175这是对二进制数据的操作,
176
177查询语句:
178
179SELECT CategoryID,
180
181Picture
182
183FROM Categories
184
185WHERE CategoryID = 1
186
187FOR XML AUTO
188
189返回结果:
190
191<categories categoryid="1" picture="dbobject/Categories[@CategoryID='1']/@Picture"></categories>
192
193使用 ELEMENTS 方式,
194
195查询语句:
196
197SELECT CategoryID,
198
199Picture
200
201FROM Categories
202
203WHERE CategoryID = 1
204
205FOR XML AUTO,ELEMENTS
206
207返回结果:
208
209<categories>
210<categoryid>1</categoryid>
211<picture>dbobject/Categories[@CategoryID='1']/@Picture</picture>
212</categories>
213
214二. **使用** ** RAW ** ** 模式 **
215
216使用 RAW 模式不能使用 ELEMENTS 选项。
217
2181 .简单查询
219
220查询语句:
221
222SELECT CategoryID,
223
224CategoryName AS Nanme
225
226FROM Categories
227
228WHERE CategoryID < 3
229
230ORDER BY CategoryID DESC
231
232FOR XML RAW
233
234返回结果:
235
236<row categoryid="2" nanme="Condiments"></row>
237<row categoryid="1" nanme="Beverages"></row>
238
2392 .连接查询
240
241查询语句:
242
243SELECT Categories.CategoryID,
244
245Categories.CategoryName,
246
247ProductID,
248
249ProductName
250
251FROM Categories
252
253JOIN Products ON Categories.CategoryID = Products.CategoryID and ProductID <4
254
255WHERE Categories.CategoryID <= 2 FOR XML RAW
256
257返回结果:
258
259<row categoryid="1" categoryname="Beverages" productid="1" productname="Chai"></row>
260<row categoryid="1" categoryname="Beverages" productid="2" productname="Chang"></row>
261<row categoryid="2" categoryname="Condiments" productid="3" productname="Aniseed Syrup"></row>
262
263** 三.使用 ** ** EXPLICIT ** ** 模式 ** **
264
265**
266
267该模式使用起来相对比较复杂,不过它可以很灵活的控制返回的 xml 数据结构。在该查询中定义了两个表示元数据的额外列。 Tag 列唯一的确定用来在结果中表示每一行的 xml 标记, Parent 列用来控制元素之间的嵌套关系。
268
2691 .使用通用表
270
271EXPLICIT 模式下有一个通用表的概念,使用数据列的名称来定义 xml 文档中的数据。结构如下:
272
273ElementName!TagNumber!AttributeName!Directive
274
275下面的说明来自联机丛书,
276
277ElementName
278
279是所得到的元素类属标识符(例如,如果将 ** Customers ** 指定为 ElementName ,则 <customers> 是元素标记)。
280
281TagNumber
282
283是元素的标记号。借助于通用表中的两个元数据列( ** Tag ** 和 ** Parent ** ), TagNumber 用于表示 XML 树中的 XML 元素嵌套。每个 TagNumber 都准确对应于一个 ElementName 。
284
285AttributeName
286
287是 XML 特性的名称(如果没有指定 Directive )或包含的元素名(如果 Directive 是 ** xml ** 、 ** cdata ** 或 ** element ** )。如果指定 Directive ,则 AttributeName 可以为空。这种情况下,列中包含的值直接由具有指定 ElementName 的元素所包含。
288
289Directive
290
291是可选命令。如果没有指定 Directive ,则必须指定 AttributeName 。如果没有指定 AttributeName 且没有指定 Directive <span style="FONT-SIZE: 9pt; FONT-FAMILY: 宋体; mso-ascii-font-family: "Times</customers></search_condition></table_source></select_list>