小议sql查询返回xml数据之应用

 今天查看邮件，看到一标题Using the FOR XML Clause to Return Query Results as XML，点进去看了看，以前也是知道sql server 查询可以返回xml格式，但具体一到应用中比较少，读过文章后，想到自己的项目有调用远程web服务，传xml格式串方法，这个xml串是拼接而成的。想到此，决定做个测试，看程序中如何接收sql查询返回xml格式，为以后做个笔记，方便自己查找。

测试步骤：

1.       创建测试数据，同上一篇(在sql server中，实现列数据转换成逗号隔开字符串 )，还是以用户表为例，创建语句和添加数据语句如下：

Code

CREATE TABLE [dbo].[Users](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [UserID] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,

    [UserName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,

    [Address] [varchar](260) COLLATE Chinese_PRC_CI_AS NULL,

 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 

(

    [ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

-----------------插入测试数据

INSERT [Users] ( [UserID],[UserName],[Address]) VALUES ('su20090812001','kevin','beijing')

INSERT [Users] ( [UserID],[UserName],[Address]) VALUES ( 'su20090912002','zhangsan','tianjin')

INSERT [Users] ( [UserID],[UserName],[Address]) VALUES ( 'su20090701234','wangwu','shanghai')

INSERT [Users] ( [UserID],[UserName],[Address]) VALUES ( 'su20090912004','lisi','beijing')

INSERT [Users] ( [UserID],[UserName],[Address]) VALUES ( 'su20090912005','jialiu','hebei')

INSERT [Users] ( [UserID],[UserName],[Address]) VALUES ( 'su20090912007','qianlong','zhejiang')

2.       在sql server management studio 中温习下 sql 查询加for xml后的结果集，主要是参考Using the FOR XML Clause to Return Query Results as XML文内容，在sql 语句结束处加for xml auto root(‘xxx’) elements等等。

运行一sql 语句：SELECT * FROM Users u FOR XML raw

执行结果如下：

Code

<row ID="1" UserID="su20090812001" UserName="kevin" Address="beijing" />

<row ID="2" UserID="su20090912002" UserName="zhangsan" Address="tianjin" />

<row ID="3" UserID="su20090701234" UserName="wangwu" Address="shanghai" />

<row ID="4" UserID="su20090912004" UserName="lisi" Address="beijing" />

<row ID="5" UserID="su20090912005" UserName="jialiu" Address="hebei" />

<row ID="6" UserID="su20090912007" UserName="qianlong" Address="zhejiang" />

Sql语句：SELECT * FROM Users u FOR XML RAW('User')

执行结果如下：

Code

<User ID="1" UserID="su20090812001" UserName="kevin" Address="beijing" />

<User ID="2" UserID="su20090912002" UserName="zhangsan" Address="tianjin" />

<User ID="3" UserID="su20090701234" UserName="wangwu" Address="shanghai" />

<User ID="4" UserID="su20090912004" UserName="lisi" Address="beijing" />

<User ID="5" UserID="su20090912005" UserName="jialiu" Address="hebei" />

<User ID="6" UserID="su20090912007" UserName="qianlong" Address="zhejiang" />

 语句SELECT * FROM Users u FOR XML RAW('User'),ROOT('Users'),elements 不在copy执行结果了，大家自己执行下就可以看到效果了。

3.       在ASP.NET中如何获取这种形式的数据集呢。主要用到xmlreader(xmlTextReader)，这里以xmlreader为例，

在程序代码中，主要用是用SqlCommand对象ExecuteXmlReader()方法，

下面是具体的实现代码：

Code

using System.Web.UI.WebControls;

using System.Xml;

using System.Data;

using System.Data.SqlClient;

using System.Text;

public partial class xmlUser : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

{

        if (!Page.IsPostBack)

{

            GetUserXml();

        }

    }

    private void GetUserXml()

{

        SqlConnection conn = new SqlConnection("server=(local);uid=sa;pwd=kevin;database=demo;");

        SqlCommand cmd = new SqlCommand("SELECT * FROM Users u FOR XML RAW('User'),ROOT('Users'),elements",conn);

        XmlReader myreader = null;

        try

{

            conn.Open();

            myreader = cmd.ExecuteXmlReader();

            StringBuilder sb = new StringBuilder();

            myreader.MoveToElement();

            while (myreader.IsStartElement())

{   /**读取xml部分

                sb.Append(myreader.ReadOuterXml());

            }

            /**显示xml的内容

            Response.Write(Server.HtmlEncode(sb.ToString()).Replace("\n", "<br>"));

        }

        catch(SqlException err)

{

            //写日志略

        }

        finally

{

            conn.Close();

        }

    }

}

运行结果：

Code

<Users><User><ID>1</ID><UserID>su20090812001</UserID><UserName>kevin</UserName><Address>beijing</Address></User><User><ID>2</ID><UserID>su20090912002</UserID><UserName>zhangsan</UserName><Address>tianjin</Address></User><User><ID>3</ID><UserID>su20090701234</UserID><UserName>wangwu</UserName><Address>shanghai</Address></User><User><ID>4</ID><UserID>su20090912004</UserID><UserName>lisi</UserName><Address>beijing</Address></User><User><ID>5</ID><UserID>su20090912005</UserID><UserName>jialiu</UserName><Address>hebei</Address></User><User><ID>6</ID><UserID>su20090912007</UserID><UserName>qianlong</UserName><Address>zhejiang</Address></User></Users> 

得到sql 返回的xml格式的数据，以前我的那个项目拼接成的xml格式的字符串就可以省去一大部分拼接代码。没有测试过是在程序这边拼接效率好还是数据库返回xml数据效率好，但编写的程序代码是减少很多。有兴趣的同学帮忙测测这两种方式哪种效率更好些呢。欢迎大家扔砖头。