探讨Sqlserver 2005里面的XML特性，XPath和XQuery查询

前几天看ADO.NET 相关书籍，了解了下Sql2005 中如何插入带相关架构的XML数据类型 ,，以及如何使用Xpath查询和Xquery 查询。数据库还是那个无处不在的北风贸易。这次就拿Orders和[Order Details] 表开涮了。

下面列出了其中的代码：

1、首先在Sql2005的ＳＳＭＳ中编写Ｔ—Ｓｑｌ　语句，编写ＸＭＬ架构

如下：

Create XML Schema Collection OrderDetailsSchema as

N’<schema xmlns= ”http://www.w3.org/2001/XMLSchema”>

</complexType>

</element>

</schema>

’

1.1 然后再在Northwind数据库的Orders表中插入一列

Alter table Orders add OrderDetailsAsXml xml (Content OrderDetailsSchema)

2、建一个控制台应用程序

[STAThread]

Static void Main(string[] args)

{

SqlServer2005_CreateOrderDetailsAsXmlColumn();

}

static void SqlServer2005_CreateOrderDetailsAsXmlColumn()

{

using (SqlConnection conn=new SqlConnection(Connstr))

{

conn.Open();

SqlServer2005_CreateOrderDetailsAsXmlColumn_GenerateXmlValue(conn);

}

Static void SqlServer2005_CreateOrderDetailsAsXmlColumn_GenerateXmlValue(Sqlconnection conn)

{

DataSet ds=new DataSet();

String sql=”select OrderID from Orders;select OrderId,ProductId,UnitPrice,Quantity,Discount from ”+

“ [Order Details]”;

SqlDataAdapter adapter=new SqlDataAdapter(sql,conn);

adapter.TableMappings.Add(“Table”,”Orders”);

adapter.TableMappings.Add(“Table1”,”Order Details”);

adapter.Fill(ds);

DataTable tableOrders=ds.Tables[“Orders”];

DataRelation rel=ds.Relations.Add(“FK_Order_Details”,tableOrders.Columns[“OrderID”],ds.Tables[“Order Details”].Columns[“OrderID”]);

tableOrders.Columns.Add(“OrderDetailsAsXml”,typeof(SqlXml));//SqlXml位于System.Data.SqlTypes 下

foreach(DataRow orderRow in tableOrders.Rows)

{

orderRow[“OrderDetailsAsXml”]=SqlServer2005_CreateOrderDetailsAsXmlColumn_GenerateXmlValueForOrder(orderRow,rel);

}

sql=”Update Orders set OrderDetailsAsXml=@OrderDetailsAsXml where OrderId=@OrderId ”;

adapter.UpdateCommand=new SqlCommand(sql,conn);

adapter.UpdateCommand.Parameters.Add(“@OrderDetailsAsXml”,SqlDbTypes.Xml,0,”OrderDetailsAsXml”);

adapter.UpdateCommand.Parameters.Add(“@OrderID”,SqlDbType.Int,4,”OrderID”);

adapter.UpdateCommand.UpdateRowSource=UpdateRowSource.None;//忽略任何返回的参数或行。

try

{

adapter.Update(tableOrders);

Console.WriteLine(“Succeed!”);

}

Catch(Exception E)

{

Console.WriteLine(E.Message);

}

Finally

{

Conn.Colose();

}

Static SqlXml SqlServer2005_CreateOrderDetailsAsXmlColumn_GenerateXmlValueForOrder(DataRow orderRow,DataRelation rel)

{

//这里用的方法和类将要您导入System.XML和System.IO 命名空间

MemoryStream stream=new MemoryStream();

XmlWriterSettings settings=new XmlWritterSettings();

setting.ConformanceLevel=ConformanceLevel.Fragment;//说明 XML 数据是格式良好的 XML 片段

XmlWriter writer=new XmlWriter(stream,settings);

foreach(DataRow detailsRow in orderRow.GetChildRows(rel)

{

SqlServer2005_CreateOrderDetailsAsXmlColumn_WriteXmlValue(writer,detailsRow[“OrderId”],

detailsRow[“ProductId”], detailsRow[“UnitPrice”], detailsRow[“Quantity”], detailsRow[“Discount”]);

}

writer.Flush();

stream.Position=0;

return new SqlXml(stream);

}

Static void SqlServer2005_CreateOrderDetailsAsXmlColumn_WriteXmlValue(XmlWriter writer,object OrderId,object ProductId,object UnitPrice,object Quantity,object Discount)

{

writer.WriteStartElement(“OrderDetails”);//这里的字符串参数必须跟数据中架构定义的元素名称相同

writer.WriteStartAttribute(“OrderId”);

writer.WriteValue(OrderID);

writer.WriteEndAttribute();

writer.WriteStartAttribute(“ProductId”);

writer.WriteValue(ProductID);

writer.WriteEndAttribute();

writer.WriteStartAttribute(“UnitPrice”);

writer.WriteValue(UnitPrice);

writer.WriteEndAttribute();

writer.WriteStartAttribute(“Quantity”);

writer.WriteValue(Quantity);

writer.WriteEndAttribute();

writer.WriteStartAttribute(“DisCount”);

writer.WriteValue(Discount);

writer.WriteEndAttribute();

writer.WriteEndElement();

}

3、最后在数据库Northwind的Orders表中的新建列 OrderDetailsAsXml 上面见一个索引

Create primary xml index IMX_OrderDetails on Orders(OrderDetailsAsXml)

这样您的Orders表中就会有XML数据类型了

二、使用Xpath和Xquery查询来查询XML数据类型列的数据

1、在Main函数里面调用下面两个函数

SqlServer2005Xml_GetOrderContainingProduct_XPath("Chocolade");

SqlServer2005Xml_GetOrderContainingProduct_XQuery("Chocolade");

1.1、

static void SqlServer2005Xml_GetOrderContainingProduct_XPath(string productName)

{

Console.WriteLine("开始在XML列执行产品名称为""{0}""XPath查询",productName);

string strXPathValue, strXPathExist, sql;

//[1]表示它只返回一个值

strXPathValue = "(./OrderDetails[@ProductID=sql:column(""P.ProductID"")]/@Quantity)[1]";

strXPathExist = "/OrderDetails[@ProductID=sql:column(""P.ProductID"")]";

//exist方法如果满足Xpath查询，则该方法返回，否则返回0，如果XML数据为NUll，则返回Null

//执行Sql语句之前一定要讲Set ALTER DATABASE Northwind SET ARITHABORT ON

//否则会有异常。小菜偶的机子是这样的，各自看情况吧！

sql = string.Format("select O.OrderID,C.CompanyName,O.OrderDetailsAsXml.value('{0}','smallint')"

+" as Quantity from Orders as O inner join Customers as C on C.CustomerID=O.CustomerID " +

" cross join Products P where P.ProductName='{1}' and O.OrderDetailsAsXml.exist('{2}')=1", strXPathValue,

productName, strXPathExist);

using (SqlConnection conn=new SqlConnection(Connstr))

{

SqlCommand cmd = new SqlCommand(sql, conn);

conn.Open();

SqlDataReader reader = cmd.ExecuteReader();

if (reader.HasRows)

{

Console.WriteLine("{0,7},{1,-30},{2,8}", "OrderID", "CompanyName", "Quantity");

Console.WriteLine("{0,7},{1,-30},{2,8}", new string('=', 7), new string('=', 30), new string('=', 8));

while (reader.Read())

{

Console.WriteLine("{0,7},{1,-30},{2,8}", reader["OrderID"],reader["CompanyName"],reader["Quantity"]);

}

reader.Close();

}

cmd.Dispose();

conn.Close();

}

2.2、

执行Qpath查询之前，先在Sql2005里面把包含Xpath的sql查询语句跑了一遍试了试，才敢在.net里面执行。要不老是会有些莫名的异常，下面是sql查询

select OrderDetailsAsXml.query('

element Order

{

attribute CompanyName

{

if(string-length(sql:column("C.CompanyName"))<=20)

then sql:column("C.CompanyName")

else

concat(substring(sql:column("C.CompanyName"),0,20),"...")

attribute OrderID

{

sql:column("O.OrderID")

attribute Quantity

{

/OrderDetails[@ProductID=sql:column("P.ProductID")]/@Quantity

}

')as OrderDetails from Orders O inner join Customers C on

C.CustomerID=O.CustomerID cross join Products P where P.ProductName='Chocolade' and OrderDetailsAsXml.exist(

'/OrderDetails[@ProductID=sql:column("P.ProductID")]')=1

结果如下：

static void SqlServer2005Xml_GetOrderContainingProduct_XQuery(string productName)

{

Console.WriteLine("对Orders表中的OrderDetailsAsXml列进行XQuery查询");

string XQuery, XPath, sql;

XPath = "/OrderDetails[@ProductID=sql:column(""P.ProductID"")]";

XQuery = "element Orders {attribute CompanyName " +

"{ if(string-length(sql:column(""C.CompanyName""))<=20)" +

" then sql:column(""C.CompanyName"")" +

"else concat(substring(sql:column(""C.CompanyName""),0,20),""..."")" +

"}," +

"attribute OrderID{sql:column(""O.OrderID"")}," +

/*XQuery [Orders.OrderDetailsAsXml.query()]: 应为表达式这个错误来自我没有把最后一个逗号去掉*/

"attribute Quantity{/OrderDetails[@ProductID=sql:column(""P.ProductID"")]/@Quantity}"

+ "}";

sql = string.Format("select OrderDetailsAsXml.query('{0}') as OrderDetails from Orders O inner " +

"join Customers C on C.CustomerID=O.CustomerID cross join Products P where ProductName=@ProductName " +

"and O.OrderDetailsAsXml.exist('{1}')=1", XQuery, XPath);

/*结果如下：

* exist (XQuery)

返回“位”，表示下列条件之一：

1，表示True（如果查询中的XQuery 表达式返回一个非空结果）。即，它至少返回一个XML 节点。

0，表示False（如果它返回一个空结果）。

NULL（如果执行查询的xml 数据类型实例包含NULL）。

using (SqlConnection conn=new SqlConnection(Connstr))

{

SqlCommand cmd = new SqlCommand(sql, conn);

try

{

cmd.Parameters.AddWithValue("@ProductName", productName);

conn.Open();

SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())

{

Console.WriteLine("{0}",reader.GetString(0));

}

catch (Exception E)

{

Console.WriteLine(E.Message);

}

finally

{

if (conn.State== ConnectionState.Open)

{

conn.Close();

}

posted on 2008-04-25 11:33 shiboys 阅读(1137) 评论(0) 收藏举报

刷新页面返回顶部

探讨Sqlserver 2005里面的XML特性，XPath和XQuery查询

公告