Using the SQL Server OPENXML() Function

Sử dụng SQL Server OpenXML () Chức năng SQL Server có chứa một hàm tên là OpenXML () cho phép bạn đọc dữ liệu XML như thể nó là một kết quả thiết lập các hàng. Một sử dụng OpenXML () là để đọc dữ liệu XML như là các hàng, và sau đó chèn thêm các dòng vào một bảng. Trong phần này, bạn sẽ khám phá những cú pháp của OpenXML (). Bạn cũng sẽ thấy một ví dụ mà đọc dữ liệu XML có chứa các chi tiết của hai khách hàng sử dụng OpenXML (), và...
Nội dung trích xuất từ tài liệu:
Using the SQL Server OPENXML() FunctionUsing the SQL Server OPENXML() FunctionSQL Server contains a function named OPENXML() that allows you to read XML dataas if it were a result set of rows. One use of OPENXML() is to read XML data as rows,and then insert those rows into a table.In this section, youll explore the syntax of OPENXML(). Youll also see an example thatreads XML data containing details of two customers using OPENXML(), and then youllinsert two new rows into the Customers table using the values from that XML data.OPENXML() SyntaxThe OPENXML() function uses the following syntaxOPENXML(XmlDocumentHandle int [IN], RowPattern nvarchar [IN], [Flags byte[IN]])[WITH (SchemaDeclaration | TableName)]whereXmlDocumentHandle specifies an int handle to your XML document. You use thishandle as a reference to your XML document.RowPattern specifies an XPath expression to select the data you require from your XMLdocument.Flags specifies an optional byte value that you use to indicate the mapping between yourXML data and the database column values. A value of 1 indicates that your XML databeing read stores column values in embedded attributes of the nodes (Listing 16.8, shownearlier, illustrates embedded attributes); this is the default. A value of 2 indicates thatyour XML data stores column values as separate nested elements (Listing 16.7, shownearlier, illustrates nested elements). The values from your XML file are then used ascolumn values in the rows returned by OPENXML().SchemaDeclaration specifies the definition of the database schema you want to use toreturn rows as. An example definition is CustomerID nvarchar(5), CompanyNamenvarchar(40). You use either SchemaDeclaration or TableName.TableName specifies the name of the database table you want to use. Youll typically useTableName rather than SchemaDeclaration when youre working with a table that alreadyexists in the database.Using OPENXML()Before calling OPENXML(), you must first call the sp_xml_preparedocument()procedure. This procedure parses your XML document and prepares a copy of thatdocument in memory. You then use that copy of the XML document with OPENXML().Once youve completed your call to OPENXML() you call thesp_xml_removedocument() procedure to remove the XML document from memory.The example in this section uses a stored procedure named AddCustomersXml() to readthe XML data containing details of two customers using OPENXML() and to insert twonew rows into the Customers table using the values from that XML data. Listing 16.15shows a script named AddCustomersXml.sql that creates the AddCustomersXml() storedprocedure.Listing 16.15: ADDCUSTOMERSXML.SQL/* AddCustomersXml.sql creates a procedure that uses OPENXML() to read customers from an XML document and then inserts them into the Customers table*/CREATE PROCEDURE AddCustomersXml @MyCustomersXmlDoc nvarchar(4000)AS - declare the XmlDocumentId handle DECLARE @XmlDocumentId int - prepare the XML document EXECUTE sp_xml_preparedocument @XmlDocumentId OUTPUT,@MyCustomersXmlDoc - read the customers from the XML document using OPENXML() - and insert them into the Customers table INSERT INTO Customers SELECT * FROM OPENXML(@XmlDocumentId, N/Northwind/Customers, 2) WITH Customers - remove the XML document from memory EXECUTE sp_xml_removedocument @XmlDocumentIdOPENXML() reads the XML from the document specified by the handle@XmlDocumentId and returns the rows to the INSERT statement. These rows are thenadded to the Customers table by the INSERT statement.Listing 16.16 shows a script named RunAddCustomers.sql that runs theAddCustomersXml() procedure.Listing 16.16: RUNADDCUSTOMERS.SQL/* RunAddCustomersXml.sql runs the AddCustomersXml() procedure*/- define the XML documentDECLARE @NewCustomers nvarchar(4000)SET @NewCustomers = N T1COM Test 1 Company T2COM Test 2 Company- run the AddCustomersXml() procedureEXECUTE AddCustomersXml @MyCustomersXmlDoc=@NewCustomers- display the new rowsSELECT CustomerID, CompanyNameFROM CustomersWHERE CustomerID IN (T1COM, T2COM)- delete the new rowsDELETE FROM CustomersWHERE CustomerID IN (T1COM, T2COM)Figure 16.17 shows the result of running the RunAddCustomers.sql script in QueryAnalyzer.Figure 16.17: Running the RunAddCustomers .sql script