将 XML 格式的数据插入 SQL Server 2000
3.75/5 (10投票s)
2004年6月22日
4分钟阅读
179900
本文介绍了如何使用 SQL Server 2000 提供的 XML 支持和 Visual Studio.NET 将父子记录插入 SQL Server 2000。
引言
本文提供了有关如何使用单个存储过程插入一个父记录和多个子记录的基本信息。开发人员经常会遇到这种情况，为此开发了许多定制解决方案。人们往往忽略了 SQL Server 提供了强大的 XML 支持，可用于数据提取和序列化。
在本文中，创建了一个存储过程，该存储过程需要 nText SQL Server 数据格式的 XML 文档。 .NET 应用程序将自定义对象序列化为存储过程所需的格式。序列化的 XML 数据通过 ADO.NET 作为其他存储过程一样传递给存储过程。存储过程维护父子插入事务以保证数据库的完整性。
可以使用一个辅助类来获取自定义对象作为其属性，并为我们提供其 XML 表示形式。 System.Xml.Serialization 命名空间为任何类型的对象序列化为 XML 格式提供了强大的支持。表示父表的对象的属性将包含一个表示子表或详细信息表的另一个对象。在表示子表的 XML 序列化对象中，用于将对象转换为所需的 XML 格式文档。
背景
本文使用通常随 SQL Server 2000 一起提供的 Northwind 数据库。
为了使本教程保持简单，我将假设读者已理解以下内容。
- 熟悉 XML 文档和 XQuery 的基础知识。
- 父子或主详细信息表关系的基本概念。
- 熟悉 SQL Server 2000 存储过程。
- 熟悉与 XML 相关的存储过程，例如
sp_xml_preparedocument、sp_xml_removedocument和函数Openxml。这些信息可以从 SQL Server 安装附带的 Transact SQL 的在线帮助中提取。 System.Xml.Serialization命名空间提供将对象序列化为 XML 格式。
本文无法详细介绍上述主题，因为它们足以写成整本书，但会提供简要说明。
XML 文档格式
以下是我们存储过程期望的格式。
<?xml version="1.0"?>
<Order>
<CustomerID>ALFKI</CustomerID>
<EmployeeID>3</EmployeeID>
<OrderDate>07/3/2004</OrderDate>
<RequiredDate>07/4/2004</RequiredDate>
<ShippedDate>15/3/2004</ShippedDate>
<OrderDetails ProductID="2" UnitPrice="15"
Quantity="5" Discount="0.15"></OrderDetails>
<OrderDetails ProductID="4" UnitPrice="22"
Quantity="7" Discount="0.21"></OrderDetails>
<OrderDetails ProductID="10" UnitPrice="31"
Quantity="3" Discount="0.15"></OrderDetails>
</Order>
<CustomerID>、<EmployeeID>、<OrderDate> 和 <ShippedDate> 映射到父表 Orders，而 <OrderDetails> 元素中的 ProductID、UnitPrice、Quantity、Discount 属性映射到 northwind 数据库中的子表 [Order Details]。未包含其他字段是为了使 XML 文档保持简短和简单。
选择属性而不是元素完全取决于读者的选择和情况。
存储过程
CREATE PROCEDURE xmlOrderInsert @order ntext AS
DECLARE @docHandle int, @OID int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @order
BEGIN TRANSACTION
INSERT INTO Orders( CustomerID, EmployeeID, OrderDate, RequiredDate )
SELECT CustomerID, EmployeeID, OrderDate, RequiredDate
FROM Openxml( @docHandle, '/Order', 3) WITH ( CustomerID nchar(5),
EmployeeID int, OrderDate datetime, RequiredDate datetime )
IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -100 END
SET @OID = SCOPE_IDENTITY()
INSERT INTO [Order Details] ( OrderID, ProductID, UnitPrice, Quantity, Discount )
SELECT @OID AS [PO ID], ProductID, UnitPrice, Quantity, Discount
FROM OpenXml( @docHandle, '/Order/OrderDetails', 1) WITH
( ProductID int, UnitPrice money, Quantity smallint, Discount real )
IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -101 END
COMMIT TRANSACTION
EXEC sp_xml_removedocument @docHandle SELECT @OID AS [Order ID]
GO
语法
- sp_xml_preparedocument 返回一个句柄，用于访问使用第二个参数创建的 XML 文档的内存表示。此句柄稍后用于 OPENXML 函数。
- OPENXML 为 XML 文档提供行集视图。
- sp_xml_removedocument 删除由文档句柄指定的 XML 文档的内部表示，并使文档句柄失效。
首先，使用 Openxml( @docHandle, '/Order', 3) WITH ( CustomerID nchar(5), EmployeeID int, OrderDate datetime, RequiredDate datetime ) 从 XML 文档中提取父表 Order 的行集，并插入到数据库中。然后使用 SET @OID = SCOPE_IDENTITY() 从 SQL Server 中检索新插入订单的主键。然后使用 OpenXml( @docHandle, '/Order/OrderDetails', 1) WITH ( ProductID int, UnitPrice money, Quantity smallint, Discount real ) 从 XML 文档中提取子行集并插入到数据库中。Begin 和 End Transaction 用于数据完整性以及一些不言自明的错误处理代码。
类
/// <summary>
//Order.cs
/// Order class.
/// </summary>
using System;
using System.Xml.Serialization;
namespace XMLInsert
{
public class Order
{
public Order()
{
OrderDetails=null; //new OrderDetail[0];
}
private string CID="";
private int EID;
private string ODate;
private string RDate;
private string SDate;
[XmlElement("OrderDetails")]
public OrderDetail[] OrderDetails;
public string CustomerID
{
get
{
return this.CID;
}
set
{
CID=value;
}
}
public int EmployeeID
{
get
{
return this.EID;
}
set
{
EID=value;
}
}
public string OrderDate
{
get
{
return this.ODate;
}
set
{
ODate=value;
}
}
public string RequiredDate
{
get
{
return this.RDate;
}
set
{
RDate=value;
}
}
public string ShippedDate
{
get
{
return this.SDate;
}
set
{
SDate=value;
}
}
}
}
上述类代表 Northwind 数据库中的父 Order 表。该类有一个 OrderDetails 属性，该属性是 OrderDetail 类的数组，我们将在稍后定义它。[XmlElement("OrderDetails")] 将使数组中的每个项在执行对象序列化时转换为 <OrderDetails> 元素。
using System;
using System.Xml.Serialization;
namespace XMLInsert
{
/// <summary>
/// OrderDetails.cs
/// Summary description for OrderDetail.
/// </summary>
public class OrderDetail
{
public OrderDetail()
{
}
private int PID;
private Decimal UPrice;
private int Qty;
private Decimal disc;
[XmlAttribute]
public int ProductID
{
get
{
return this.PID;
}
set
{
PID=value;
}
}
[XmlAttribute]
public Decimal UnitPrice
{
get
{
return this.UPrice;
}
set
{
UPrice=value;
}
}
[XmlAttribute]
public int Quantity
{
get
{
return this.Qty;
}
set
{
Qty=value;
}
}
[XmlAttribute]
public Decimal Discount
{
get
{
return this.disc;
}
set
{
disc=value;
}
}
}
上述类代表 Northwind 数据库中的子表。该类具有 ProductID、UnitPrice、Quantity、Discount 四个属性，每个属性在声明前都带有 [XmlAttribute] 属性。这将使它们转换为 XML 属性。
使用代码
首先，在 Northwind 数据库中注册存储过程。为了测试存储过程，执行该存储过程并以简单文本格式提供本教程中提供的 XML 文档。为了保持代码简单，我们只创建 OrderDetail 数组的三个元素。通常使用网格作为详细信息的输入，但为了简单起见，我们只创建三个详细信息数组项。
请在您的应用程序中将数据值替换为合适的数据值。
启动一个 .Net 项目，将两个类复制到项目中，在将数据插入数据库时，使用以下代码片段
//
// Prepare a seriliazer object
XmlSerializer serlizer = new XmlSerializer(typeof(Order));
// Declare OrderDetail array
OrderDetail[] od = new OrderDetail[3];
for(int r=0;r<3;r++)
{
//od[r].ProductID=Convert.ToInt32(datavalues);
//od[r].UnitPrice=Convert.ToDecimal(datavalues);
//od[r].Quantity=Convert.ToInt32(datavalues);
//od[r].Discount=Convert.ToDecimal(datavalues);
}
// Declarin Order Object and setting its properties
Order o = new Order();
//o.CustomerID=datavalues;
//o.EmployeeID=Convert.ToInt32(datavalues);
//o.OrderDate=datavalues
//o.RequiredDate=datavalues;
//o.ShippedDate=datavalues;
//o.OrderDetails=od;
// Declaring memory stream
System.IO.MemoryStream mm=null;
try
{
mm=new System.IO.MemoryStream();
serlizer.Serialize(mm,o);
}
catch(Exception ee)
{
MessageBox.Show(ee.ToString());
}
finally
{
mm.Close();
}
string xml = System.Text.Encoding.UTF7.GetString(mm.ToArray());
//
现在，可以通过 ADO.NET 将 变量 xml 传递给存储过程。