如何使用SQL Server 2000中的XML功能

使用FOR XML时，还必须说明计算列。尽管FOR XML支持计算列，但仍需确保对这些列进行命名。当FOR XML试图创建每一列的属性时，就会出现问题。属性名是通过使用关联的列名创建的。因此如果该列是一个计算列且没有别名，则SQL Server将停滞不前。简单的办法是确保给计算列取个别名，如下面的SQL语句及其XML结果所示：

SELECT TOP 1 LastName + ', ' + FirstName AS FullName 

FROM Employees 

FOR XML AUTO 
<Employees FullName="Davolio, Nancy"/> 

使用FOR XML时值得注意的最后一个问题是任何专用的XML字符都将通过使用XML编码来转换。正如HTML可以转换URL中的专用字符一样，适当形式的XML可以编码专用字符。例如，如果<字符包含在数据中，则它将转换成“&lt;”。

OPENXML

   

到此为止我们已经探讨了从SQL Server获取XML的方法。下一步将是在数据库中使用XML来修改数据。输入OPENXML函数。T-SQL OPENXML函数能够从XML流构建关系行集合。该行集合可以执行像表一样的操作，因此可用于可能与其他表联接的其他SQL语句中，甚至可用于插入或更新数据。这可以提供很大方便，因为这样可以将包含需要在数据库中修改的新的或更新的记录XML流传递给某一应用程序。OPENXML函数使用某种形式的Xpath，以便用户能够告诉它在何处搜索想要取出的数据。

为此，首先取一个包含客户数据的XML文档，并将其插入到Northwind数据库的Customers表中。该XML文档可能使用元素或属性来表示该客户的数据，如下所示：

<root>

<customer>

<custid>77777</custid>

<custname>fake customer</custname>

<city>Somewhere</city>

<country>USA</country>

</customer>

</root> 

OPENXML语句允许用户使用XPath表达式来深入了解XML文档，这意味着XML的格式可以相当灵活。

下面创建了一个存储过程（如图 6所示），用于接受该XML文档，为关系行集合准备该XML文档，读取客户数据，将其插入到Customers表，然后从内存删除该XML文档。现在我们来看这一切是如何实现的。首先，可以在XML中作为任意字符串类型（如VARCHAR（8000）或大对象类型如TEXT或NTEXT）的变量传递。我们使用NTEXT，以便不受VARCHAR的8,000字符限制。NTEXT的最大长度为230 - 1（1,073,741,823）个字符。（当然，在SQL Server 2005中还可以使用XML数据类型。）

图 6 通过OPENXML插入Customer

CREATE PROCEDURE prInsertCustomerFromXML (

@sXML NTEXT

)

AS

DECLARE @iDoc INT

EXEC sp_xml_preparedocument@iDoc OUTPUT, @sXML
INSERT INTO Customer(CustomerID, CompanyName, City, Country)

SELECT CustomerID, CompanyName, City, Country

FROM OPENXML(@iDoc,'/root/customer',2)

WITH (CustomerID NCHAR(5) 'custid',

CompanyName NVARCHAR(40) 'custname',

City NVARCHAR(15) 'city',

Country NVARCHAR(15) 'country')
EXEC sp_xml_removedocument@iDoc
RETURN 

对XML所做的第一件事是将其传递给sp_xml_preparedocument系统存储过程。该过程取用该XML并将其转换成内部DOM，以便OPENXML能够将它作为行集合来处理。该过程还在内存中创建一个DOM引用（图 6中的@iDoc变量）。通过将@iDoc变量传递给OPENXML函数，可以在SELECT语句的FROM子句中将XML作为行集合来访问。

使用完XML后，应使用sp_xml_removedocument系统存储过程从内存中删除之。

图 6中的代码核心是OPENXML函数，它对该XML文档的内存表达式执行操作。OPENXML方法将该XML文档的引用作为其第一个参数来接受。其第二个参数用于告诉OPENXML用户想映射到行的XML DOM中的节点。在这个例子中，我们想识别Customers节点以获取该客户的数据值，因此我们将“/root/customer”指定为第二个参数。OPENXML函数的第三个参数是用于指示要使用的映射类型。取值为1时，OPENXML映射到属性，取值为2时则映射到元素。

WITH子句可用于指定要从该XML文档获取的字段以及要转换成的数据类型。WITH子句也可用于在XML中用XPath表达式来映射属性或元素，或用于为要用于某一查询的XML字段取别名。图 6中的OPENXML代码主要是从XML数据中取4个客户字段，并将其转换成行集合。然后可以从Customers表选择该行集合，或插入到其中。

通过OPENXML插入



现在我们将用下面的XML示例从中插入一个定单和两个定单细节行：

<Customer CustomerID='ALFKI'>

<Order OrderDate='1/1/1972' Freight='3'>

<Detail ProductID='1' Price='4' Quantity='10' Discount='0'/>

<Detail ProductID='2' Price='5' Quantity='2' Discount='0'/>

</Order>

</Customer> 

首先，将该XML传递给图 7所示的存储过程，并使用sp_xml_preparedocument系统存储过程准备之。然后启动一个事务来包装INSERT语句，以便插入一个定单及其子记录。这样便可以在部分事务失败时回滚事务。接下来，使用在Customer\Order节点开始的OPENXML打开XML文档。利用WITH子句，将XML文档返回到Order元素（Customer 元素）的父节点，然后查看Customer元素的CustomerID属性值，从而获得CustomerID。这是OPENXML函数的一个重要功能，因为它让用户使用受限的XPath表达式来遍历XML文档，从而获得属性和元素值。

图 7 插入父定单及其子定单

CREATE PROCEDURE prInsertOrderAndOrderDetailsFromXML @sXML NTEXT

AS

DECLARE @iDoc int, @OrderID int
EXEC sp_xml_preparedocument @iDoc output, @sXML

IF @@Error<>0

BEGIN

RETURN

END
BEGIN TRANSACTION

INSERT INTO Orders (CustomerID , OrderDate, Freight)

SELECT CustomerID, CAST(OrderDate As datetime), CAST(Freight AS money)

FROM OpenXML(@iDoc,'/Customer/Order', 1)

WITH (CustomerID nchar(5) '../@CustomerID' ,

OrderDate varchar(10) '@OrderDate',

Freight varchar(12) '@Freight')
IF @@Error<>0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN

END
SET @OrderID=SCOPE_IDENTITY()
INSERT INTO [Order Details]

(OrderID, ProductID, UnitPrice, Quantity, Discount)

SELECT @OrderID, ProductID, ProductPrice, ProductQTY, Discount

FROM OpenXML(@iDoc,'/Customer/Order/Detail')

WITH (ProductID int '@ProductID', ProductPrice money '@Price',

ProductQTY smallint '@Quantity', Discount real '@Discount')
IF @@Error<>0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN

END
EXEC sp_xml_removedocument @iDoc

COMMIT TRANSACTION

SELECT @OrderID

GO

插入Order后，我们获取由内置的SQL Server SCOPE_IDENTITY函数刚刚生成的OrderID值。然后我们使用另一个INSERT语句（使用OPENXML函数从XML数据中获取Order Details）继续插入Order Details行。只要不出现错误，定单及其子定单细节行就被插入到其各自的数据库表中。

SCOPE_IDENTITY方法使用单个Order及其子定单。不过，以一个XML批插入多个Order及其子定单的情况比较复杂。问题就是在具有多个Order记录的情况下，仍要能够将适当的定单映射到其子定单。由于不知道要关联哪些行，因此必须添加一些代码来处理这一问题。可以在WITH子句中使用@mp:id/@mp:parentid元属性来提供一种获取父定单的新OrderID并将其映射到其子定单的OrderID字段的方法。

[1] [2] [3] [4] [5]