using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Xml;
public partial class WebPage_XML_Test : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection())
{
//将dataSet的数据写入XML文档
string strConn = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=";
conn.ConnectionString = strConn;
SqlCommand com = new SqlCommand();
com.Connection = conn;
string strSQL = "select [name] from HumanResources.Department";
com.CommandText = strSQL;
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
string fileName = @"d:/test.xml";
ds.WriteXml(fileName);
//从XML文件抓取数据(也可以直接利用已经存在的DataSet),用GetXml()方法将整个xml数据传入SQL Server 2005
DataSet dsGetDataFromXML = new DataSet();
dsGetDataFromXML.ReadXml(fileName);
com.Parameters.Clear();
com.CommandText = "SELECT * FROM udf_XML2Table (@myXML)";
//com.CommandText = "select * from HumanResources.Department where exists(SELECT propAuthor FROM udf_XML2Table (@myXML) where propAuthor=[name])";
com.Parameters.Add("@myXML", SqlDbType.Xml);
com.Parameters["@myXML"].Value = dsGetDataFromXML.GetXml().ToString();//用GetXml()方法将整个xml数据传入SQL Server 2005
//验证是否真正成功
DataSet ds2 = new DataSet();
SqlDataAdapter da2 = new SqlDataAdapter(com);
da2.Fill(ds2);
this.GridView1.DataSource = ds2;
this.GridView1.DataBind();
}
//下面的代码在SQL Server 2005中创建
//--创建输出行集的用户自定义函数
//create function udf_XML2Table (@xCol xml)
//returns @ret_Table table (propPK int, propAuthor varchar(max))
//with schemabinding
//as
//begin
// insert into @ret_Table
// select ROW_NUMBER() OVER(ORDER BY nref.value('.', 'varchar(max)')), nref.value('.', 'varchar(max)')
// from @xCol.nodes('NewDataSet//Table') AS R(nref)
// return
//end
//go
}
}