SQL FOR XML子句的各种用法

来源：互联网发布：u盘安装mac系统教程编辑：程序博客网时间：2024/05/20 15:59

FOR XML子句有四种最基本的模式
1、AUTO模式：返回数据表为起表名的元素，每一列的值返回为属性；
2、RAW模式：返回数据行为元素，每一列的值作为元素的属性；
3、PATH模式：通过简单的XPath语法来允许用户自定义嵌套的XML结构、元素、属性值
4、EXPLICIT模式：通过SELECT语法定义输出XML的结构
第1步：构造测试数据表及填充数据：
--1.建表语句
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL
)
--2.产生随机数SQL函数

[sql] view plain copy
 print?
CREATE FUNCTION [GenerateRandomItem] ( @LENGTH INT )  
RETURNS NVARCHAR(255)  
AS   
    BEGIN    
    --申明变量  
        DECLARE @RandomNumber NVARCHAR(255)  
        DECLARE @I SMALLINT  
        DECLARE @RandNumber FLOAT  
        DECLARE @Position TINYINT  
        DECLARE @ExtractedCharacter VARCHAR(1)  
        DECLARE @ValidCharacters VARCHAR(255)  
        DECLARE @VCLength INT      
    --给变量赋值  
        SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'      
        SET @VCLength = LEN(@ValidCharacters)  
        SET @ExtractedCharacter = ''  
        SET @RandNumber = 0  
        SET @Position = 0  
        SET @RandomNumber = ''   
          
        SET @I = 1   
        WHILE @I < ( @Length + 1 )   
            BEGIN   
                SET @RandNumber = (SELECT RandNumber FROM [RandNumberView])  
                SET @Position = CONVERT(TINYINT, ( ( @VCLength - 1 )* @RandNumber + 1 ))  
                SELECT  @ExtractedCharacter = SUBSTRING(@ValidCharacters,@Position, 1)  
                SET @I = @I + 1  
                SET @RandomNumber = @RandomNumber + @ExtractedCharacter  
            END  
        RETURN @RandomNumber  
    END  
GO  
CREATE VIEW [RandNumberView]  
AS    
SELECT  RAND() AS [RandNumber]  

--3.添加测试数据

[sql] view plain copy
 print?
INSERT INTO Users    
        (     
          FirstName ,    
          LastName     
        )    
SELECT    
dbo.GenerateRandomItem(10),    
dbo.GenerateRandomItem(10)       
GO 10  

--4.测试查询

1.AUTO模式：

语句： SELECT UserID, FirstName,LastName FROM users FOR XML AUTO, XMLSCHEMA

说明：加上XMLSCHEMA，输出xml架构，不加则只输出数据。

输出：

2.RAW模式：

语句：SELECT UserID, FirstName,LastName FROM users FOR XML RAW ('MyUsers')

说明：将元素命名为自定义的名称

输出：

3.PATH模式：

语句： SELECT UserID "@ID", FirstName "Name/FirstName",LastName "Name/LastName" FROM usersFOR XML PATH ('MyUsers')

说明：可以指定xml结构

输出：

4.EXPLICIT模式：

SELECT TOP 5 1 AS Tag,0 AS Parent, OrderID AS [Order!1!ID],OrderDate AS [Order!1!Date],CustomerID AS [Order!1!Customer],NULL AS [OrderDetail!2!ProductID],NULL AS [OrderDetail!2!UnitPrice],NULL AS [OrderDetail!2!Quantity] FROM dbo.Orders WHERE dbo.Orders.OrderID='10248'UNION ALLSELECT TOP 5 2 AS Tag,1 AS Parent, NULL,NULL ,NULL,ProductID,UnitPrice,Quantity FROM dbo.[Order Details] WHERE OrderID='10248' FOR XML EXPLICIT

查询结果

<Order ID="10248" Date="1996-07-04T00:00:00" Customer="VINET">  <OrderDetail ProductID="11" UnitPrice="14.0000" Quantity="12" />  <OrderDetail ProductID="42" UnitPrice="9.8000" Quantity="10" />  <OrderDetail ProductID="72" UnitPrice="34.8000" Quantity="5" /></Order>

结果和上面的是完全一样的，也就是说这里不用再加上Elements属性也是一样的，也是我们最常用的一种方式
结果就不再贴了，相信简单的知道这些以后已经能帮助不少对这块不怎么理解的朋友解决一部分问题了，因为基本的查询也就是这么多，我们在使用的时候只要返回结果集的第一行第一列就行了，然后怎么处理就是c#代码怎么处理Xml文档的问题了，

总结一下这几个东东吧，

Path 是以数据基本结构也就是以字段为结点的形式返回Xml文档（这样说不专业可能容易理解）

Raw 是把数据里所有的字段以属性的方式显示出Xml文档

Auto 自动关连到相应的项目，和Raw一样在不加Elements属性时以这种方式返回数据

注：用了for xml path().
但是，这样显示出来的链接时会将路径中的<,>,&符号转义为< > & 这样显示的路径在邮件中是不能进行链接的。
在网上查了很多资料后终于找到了一种方法：...FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')
如下图比较

0 0