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 XMLSET a= -存储过程示例CREATE PROCEDURE dbo.usp_InsertShoppingCartOrder ( xml XML ) AS BEGIN SET NOCOUNT ONDECLARE Pointer INT EXECUTE sp_xml_preparedocument Pointer OUTPUT,xml INSERT INTO WebSales(ProductID,SalePrice,SaleDate,SaleBatchID,CustomerID)SELECT ProductID,Price,SaleDate,SaleBatchID,CustomerIDFROM OPENXML (Pointer,/ShoppingCart/Purchase) WITH ( ProductID INT, Price MONEY,SaleDate SMALLDATETIME,SaleBatchID INT,CustomerID INT ) EXEC sp_xml_removedocument Pointer SET NOCOUNT OFFENDWe 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 with OPENXML because it may overload your servers memory.2. XQuery (可解决OPENXML的缺点)What is XQuery?XQuery, also known as XML Query, is a language designed to query XML data, allowing you to extract nodes and elements as needed. It is defined by the W3C, and is available for use in todays most popular database engines, such as Oracle, DB2, and SQL Server.xml.existThis method returns a Boolean value based upon a search expression on an XML nodeDECLARE x XMLSET x = SELECT x.exist(/christmaslist/persongift = socks)-返回 1SELECT x.exist(/christmaslist/persongift = Socks)-返回 0SELECT x. exist (/christmaslist/zach)-返回 0xml.valueThis method accepts an XQuery statement and returns a single value.SELECT x.value(/christmaslist1/person1/name, VARCHAR(20)-返回 bettySELECT x.value(/christmaslist1/person2/name, VARCHAR(20)-返回 zachxml.queryThis method accepts an XQuery statement and returns an instance of the XML data type. These queries can be as simple or as complex as necessary.SELECT x.query(/christmaslist/person)-返回 xml.nodesThis method is very useful when you need to shred the data from an XML data type variable into relational data. This method accepts an XQuery statement as a parameter and returns a rowset that contains logical scalar data from the XML variable.SELECT x.query(/christmaslist/person) SELECT Table1.Column1.value(name,VARCHAR(20)FROM x.nodes(/christmaslist/person) as Table1(Column1)-返回 betty zach brad-存储过程示例CREATE PROCEDURE dbo.usp_InsertShoppingCartOrder ( xml XML ) AS BEGINSET NOCOUNT ON INSERT INTO WebSales(ProductID,SalePrice,SaleDate,SaleBatchID,CustomerID) SELECT Table1.Column1.value(ProductID, INT),Table1.Column1.value(Price, FLOAT),Table1.Colum