chenjing957
流浪的牙膏皮
-
个人空间
- 组别:版主
- 性别:
- 生日:1983-2-15
- 来自:
- 积分:154
- 帖子:162
- 注册:
2007-05-25
|
高级XML处理
--chenjing957翻译整理，转贴请注明出自微软BI开拓者www.windbi.com--原帖地址在前面的文章里，我看到一些例子用来阐述在SQL Server2005中关于XML处理能力。我见过许多例子都是教我们如何从XML变量或域中读出其中的值。 在这一部分，我将看到不同的方式由查询结果来生成一个XML缓存或变量。大多数的时间你可能需要用到，如果你有必要在调用的函数或存储过程中采用XML作为参数。 使用TSQL中的FOR XML关键词将查询的结果转换成XML格式。FOR XML通常总是后面跟着关键词: AUTO,RAW,PATH或EXPLICIT，在这篇文章里我们将看到AUTO和RAW的用法. 例子: 第一步 1 /*
2 让我们创建一个实例表并初始化它.
3 */
4
5 CREATE TABLE [dbo].[OrderDetails](
6 [OrderDetailID] [int] IDENTITY(1,1) NOT NULL,
7 [OrderNumber] VARCHAR(10) NOT NULL,
8 [ItemNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
9 [Qty] [int] NULL,
10 [Rate] FLOAT NULL,
11 [QtyPicked] INT NULL
12 ) ON [PRIMARY]
13
14 INSERT INTO OrderDetails (OrderNumber, ItemNumber, Qty, Rate, QtyPicked)
15 SELECT '00001', 'A001', 10, 11.25, 0
16 UNION SELECT '00001', 'A002', 20, 15, 0
17 UNION SELECT '00001', 'A003', 30, 23.75, 0
第二步 1 /*
2 用带AUTO参数的FOR XML返回一个XML格式的数据
3 是最简单的事情.
4 */
5
6 SELECT OrderNumber, ItemNumber, Qty FROM OrderDetails FOR XML AUTO
7
8 /*
9 OUTPUT:
10
11 <OrderDetails OrderNumber="00001" ItemNumber="A001" Qty="10" />
12 <OrderDetails OrderNumber="00001" ItemNumber="A002" Qty="20" />
13 <OrderDetails OrderNumber="00001" ItemNumber="A003" Qty="30" />
14 */
第三步 1 /*
2 尽管返回的结果具有XML格式,
3 但它并不是XML数据类型. 结果是nvarchar类型.
4 为了返回XML数据类型, 就用
5 关键词TYPE.
6 */
7
8 SELECT OrderNumber, ItemNumber, Qty FROM OrderDetails FOR XML AUTO, TYPE
9
10 /*
11 OUTPUT:
12
13 <OrderDetails OrderNumber="00001" ItemNumber="A001" Qty="10" />
14 <OrderDetails OrderNumber="00001" ItemNumber="A002" Qty="20" />
15 <OrderDetails OrderNumber="00001" ItemNumber="A003" Qty="30" />
16 */
第四步 1 /*
2 默认情况下, SQL SERVER 返回的是一个特征值.
3 有时候你可能需要返回一个节点的值. 用
4 ELEMENTS 关键词来实现吧.
5 */
6
7 SELECT OrderNumber, ItemNumber, Qty FROM OrderDetails FOR XML AUTO, TYPE, ELEMENTS
8
9 /*
10 OUTPUT:
11
12 <OrderDetails>
13 <OrderNumber>00001</OrderNumber>
14 <ItemNumber>A001</ItemNumber>
15 <Qty>10</Qty>
16 </OrderDetails>
17 <OrderDetails>
18 <OrderNumber>00001</OrderNumber>
19 <ItemNumber>A002</ItemNumber>
20 <Qty>20</Qty>
21 </OrderDetails>
22 <OrderDetails>
23 <OrderNumber>00001</OrderNumber>
24 <ItemNumber>A003</ItemNumber>
25 <Qty>30</Qty>
26 </OrderDetails>
27 */
第五步 1 /*
2 你将会注意到这里的结果并没有 ROOT 元素.
3 一个正确的 XML 文档/片断 都应该总会有一个 ROOT 元素.
4 让我们增加一个 ROOT 关键词来搞定它.
5 */
6
7 SELECT OrderNumber, ItemNumber, Qty FROM OrderDetails FOR XML AUTO, TYPE, ELEMENTS, ROOT
8
9 /*
10 OUTPUT:
11
12 <root>
13 <OrderDetails>
14 <OrderNumber>00001</OrderNumber>
15 <ItemNumber>A001</ItemNumber>
16 <Qty>10</Qty>
17 </OrderDetails>
18 <OrderDetails>
19 <OrderNumber>00001</OrderNumber>
20 <ItemNumber>A002</ItemNumber>
21 <Qty>20</Qty>
22 </OrderDetails>
23 <OrderDetails>
24 <OrderNumber>00001</OrderNumber>
25 <ItemNumber>A003</ItemNumber>
26 <Qty>30</Qty>
27 </OrderDetails>
28 </root>
29 */
第六步 1 /*
2 于是, 我现在也有 <root> 元素了. 但是名字好像不太合适.
3 让我们改变 root 元素的名字吧.
4 */
5
6 SELECT OrderNumber, ItemNumber, Qty FROM OrderDetails FOR XML AUTO, TYPE, ELEMENTS, ROOT('orderInfo')
7
8 /*
9 OUTPUT:
10
11 <orderInfo>
12 <OrderDetails>
13 <OrderNumber>00001</OrderNumber>
14 <ItemNumber>A001</ItemNumber>
15 <Qty>10</Qty>
16 </OrderDetails>
17 <OrderDetails>
18 <OrderNumber>00001</OrderNumber>
19 <ItemNumber>A002</ItemNumber>
20 <Qty>20</Qty>
21 </OrderDetails>
22 <OrderDetails>
23 <OrderNumber>00001</OrderNumber>
24 <ItemNumber>A003</ItemNumber>
25 <Qty>30</Qty>
26 </OrderDetails>
27 </orderInfo>
28 */
第七步 1 /*
2 直到现在, 我们看到了如何分配一个普通的名字给 <root> 元素就像
3 每一行都拥有的名字一样. 它也可以给每一个拥有普通名字
4 的元素起一个别名. 从下面的例子就可以看到.
5 */
6
7 SELECT
8 OrderNumber as 'OrderNum',
9 ItemNumber as ItemCode,
10 Qty as Quantity
11 FROM OrderDetails FOR XML AUTO, TYPE, ELEMENTS, ROOT('itemInfo')
12
13 /*
14 OUTPUT:
15
16 <itemInfo>
17 <OrderDetails>
18 <OrderNum>00001</OrderNum>
19 <ItemCode>A001</ItemCode>
20 <Quantity>10</Quantity>
21 </OrderDetails>
22 <OrderDetails>
23 <OrderNum>00001</OrderNum>
24 <ItemCode>A002</ItemCode>
25 <Quantity>20</Quantity>
26 </OrderDetails>
27 <OrderDetails>
28 <OrderNum>00001</OrderNum>
29 <ItemCode>A003</ItemCode>
30 <Quantity>30</Quantity>
31 </OrderDetails>
32 </itemInfo>
33 */
第八步 1 /*
2 到现在, 我们看到了就像给列一样重命名 <root> 元素
3 现在让我们看看如何给行重命名.
4 默认的 AUTO 关键词生成的行
5 所带的名字都是：表名/别名.
6 */
7
8 SELECT
9 OrderNumber,
10 ItemNumber,
11 Qty
12 FROM OrderDetails itemInfo FOR XML AUTO, TYPE, ELEMENTS, ROOT('orderInfo')
13
14 /*
15 OUTPUT:
16
17 <orderInfo>
18 <itemInfo>
19 <OrderNumber>00001</OrderNumber>
20 <ItemNumber>A001</ItemNumber>
21 <Qty>10</Qty>
22 </itemInfo>
23 <itemInfo>
24 <OrderNumber>00001</OrderNumber>
25 <ItemNumber>A002</ItemNumber>
26 <Qty>20</Qty>
27 </itemInfo>
28 <itemInfo>
29 <OrderNumber>00001</OrderNumber>
30 <ItemNumber>A003</ItemNumber>
31 <Qty>30</Qty>
32 </itemInfo>
33 </orderInfo>
34 */
第九步 1 /*
2 在前面的例子里, 我们给表分配了别名
3 为了推广每一行元素的名字. 这个方法工作的不错.
4 然而, 如果是复合式查询，有时候它将非常困惑于
5 在XML格式中采用别名.
6
7 AUTO关键词并没有采用一种方式来定制行的名字. 通过采用
8 RAW关键词, 而不是AUTO, 我们能够很容易的定制行. 在接下来的
9 例子将详细阐述如果使用RAW 关键词.
10 */
11
12 SELECT
13 OrderNumber,
14 ItemNumber,
15 Qty
16 FROM OrderDetails FOR XML RAW('itemInfo'), TYPE, ELEMENTS, ROOT('orderInfo')
17
18 /*
19 OUTPUT:
20
21 <orderInfo>
22 <itemInfo>
23 <OrderNumber>00001</OrderNumber>
24 <ItemNumber>A001</ItemNumber>
25 <Qty>10</Qty>
26 </itemInfo>
27 <itemInfo>
28 <OrderNumber>00001</OrderNumber>
29 <ItemNumber>A002</ItemNumber>
30 <Qty>20</Qty>
31 </itemInfo>
32 <itemInfo>
33 <OrderNumber>00001</OrderNumber>
34 <ItemNumber>A003</ItemNumber>
35 <Qty>30</Qty>
36 </itemInfo>
37 </orderInfo>
38 */
第十步 1 /*
2 到目前为止, 我们能够用我们自己的方式格式化XML的结果.
3 我们能够重命名root节点,行和元素名称.
4
5 现在让我看一个不同的例子. 在SQL Server生成
6 XML 结果的时候, 它将遍历列中每一个null值.
7 让我们看这个例子.
8
9 下面的代码, 更新列为null. 然后看看结果.
10 第三行并没有 <Qty> 元素.
11 */
12
13 UPDATE OrderDetails SET Qty = NULL WHERE OrderDetailID = 3
14
15 SELECT
16 OrderNumber,
17 ItemNumber,
18 Qty
19 FROM OrderDetails FOR XML RAW('itemInfo'), TYPE, ELEMENTS, ROOT('orderInfo')
20
21 /*
22 OUTPUT:
23
24 <orderInfo>
25 <itemInfo>
26 <OrderNumber>00001</OrderNumber>
27 <ItemNumber>A001</ItemNumber>
28 <Qty>10</Qty>
29 </itemInfo>
30 <itemInfo>
31 <OrderNumber>00001</OrderNumber>
32 <ItemNumber>A002</ItemNumber>
33 <Qty>20</Qty>
34 </itemInfo>
35 <itemInfo>
36 <OrderNumber>00001</OrderNumber>
37 <ItemNumber>A003</ItemNumber>
38 </itemInfo>
39 </orderInfo>
40 */
第十一步 1 /*
2 在前面的例子中,我们可以看到如果列是NULL,
3 则元素将在生成XML的时候不被现实或者叫做不存在.
4
5 你在大多数情况下都能发现这个问题. 例如, 比如我们
6 需要传递一个XML片断到另一个函数或者存储过程中并且如果
7 这个函数或者存储过程希望元素中存在所有行
8 那个这个函数或者存储过程将通知失败.
9
10 所以, 我们需要用一种方式将值为空的元素显现出来.
11 关键词XSINIL就能做到这一点.
12 */
13
14 SELECT
15 OrderNumber,
16 ItemNumber,
17 Qty
18 FROM OrderDetails FOR XML RAW('itemInfo'), TYPE, ELEMENTS XSINIL, ROOT('orderInfo')
19
20 /*
21 OUTPUT:
22
23 <orderInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";>
24 <itemInfo>
25 <OrderNumber>00001</OrderNumber>
26 <ItemNumber>A001</ItemNumber>
27 <Qty>10</Qty>
28 </itemInfo>
29 <itemInfo>
30 <OrderNumber>00001</OrderNumber>
31 <ItemNumber>A002</ItemNumber>
32 <Qty>20</Qty>
33 </itemInfo>
34 <itemInfo>
35 <OrderNumber>00001</OrderNumber>
36 <ItemNumber>A003</ItemNumber>
37 <Qty xsi:nil="true" />
38 </itemInfo>
39 </orderInfo>
40 */
 chenjing957 最后编辑于 2007-06-08 17:26:44
SQL Server技术交流群：71791281（有些问题在线解决或许会更快)
本群将从即日起严厉打击：刷屏、打广告、粘贴各种垃圾信息的ID，一经发现，马上删除。
希望通过我们的努力，能为大家打造一个健康有序的交流平台。
|
