SQL2005XML存储过程应用.doc

SQL2005 XML存储过程应用 1 OPENXML (memory intensive) OPENXML is a SQL Server function, which accepts a stream of XML data and provides an in-memory relational rowset view of the XML data. --参数示例 类型可以是XML或者字符类型，如VARCHAR(8000),VARCHAR(MAX)等 DECLARE @a XML SET @a= ShoppingCart Purchase ProductID=7 Price=10.00 SaleDate=10/11/2006 SaleBatchID = 4523 CustomerID = 2398/ Purchase ProductID=99 Price=25.00 SaleDate=10/11/2006 SaleBatchID = 4523 CustomerID = 2398/ /ShoppingCart --存储过程示例 CREATE PROCEDURE dbo.usp_InsertShoppingCartOrder ( @xml XML ) AS BEGIN SET NOCOUNT ON DECLARE @Pointer INT EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@xml INSERT INTO WebSales(ProductID,SalePrice,SaleDate,SaleBatchID,CustomerID) SELECT ProductID,Price,SaleDate,SaleBatchID,CustomerID FROM OPENXML (@Pointer,/ShoppingCart/Purchase) WITH ( ProductID INT, Price MONEY,SaleDate SMALLDATETIME,SaleBatchID INT, CustomerID INT ) EXEC sp_xml_removedocument @Pointer SET NOCOUNT OFF END We then must call the system stored procedure sp_xml_preparedocument, which creates an in-memory representation of the xml document, which it also accepts as a parameter. Once we have the memory pointer to the XML variable, we can call OPENXML, which you may do with several different parameters that give you very granular control over the result set of the XML data. We are providing the pointer to the XML document, along with an XPATH query that identifies the nodes in the XML data we wish to return. The WITH clause of the OPENXML function allows you to specify the rowset format that you wish to be returned. Once we insert into our WebSales table, we call the sp_xml_removedocument system stored procedure to clean our XML data from SQL Servers memory. 或者可以把输入的XML数据插入不同的表 OPENXML的缺点： OPENXML is very memory intensive. The pointer returned by the system stored procedure sp_xml_preparedocument is a memory pointer to a COM XML document object model. So, you must be careful not to load large XML documents into memory wit