Using the SQL Server OPENXML() Function

Chia sẻ: Qweqwdasd Qweqdasda | Ngày: | Loại File: PDF | Số trang:4

Thêm vào BST

Báo xấu

232
lượt xem 11
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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à...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Using the SQL Server OPENXML() Function

Using the SQL Server OPENXML() Function SQL Server contains a function named OPENXML() that allows you to read XML data as 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, you'll explore the syntax of OPENXML(). You'll also see an example that reads XML data containing details of two customers using OPENXML(), and then you'll insert two new rows into the Customers table using the values from that XML data. OPENXML() Syntax The OPENXML() function uses the following syntax OPENXML(XmlDocumentHandle int [IN], RowPattern nvarchar [IN], [Flags byte[IN]]) [WITH (SchemaDeclaration | TableName)] where XmlDocumentHandle specifies an int handle to your XML document. You use this handle as a reference to your XML document. RowPattern specifies an XPath expression to select the data you require from your XML document. Flags specifies an optional byte value that you use to indicate the mapping between your XML data and the database column values. A value of 1 indicates that your XML data being read stores column values in embedded attributes of the nodes (Listing 16.8, shown earlier, illustrates embedded attributes); this is the default. A value of 2 indicates that your XML data stores column values as separate nested elements (Listing 16.7, shown earlier, illustrates nested elements). The values from your XML file are then used as column values in the rows returned by OPENXML(). SchemaDeclaration specifies the definition of the database schema you want to use to return rows as. An example definition is CustomerID nvarchar(5), CompanyName nvarchar(40). You use either SchemaDeclaration or TableName. TableName specifies the name of the database table you want to use. You'll typically use TableName rather than SchemaDeclaration when you're working with a table that already exists 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 that document in memory. You then use that copy of the XML document with OPENXML(). Once you've completed your call to OPENXML() you call the sp_xml_removedocument() procedure to remove the XML document from memory. The example in this section uses a stored procedure named AddCustomersXml() to read the XML data containing details of two customers using OPENXML() and to insert two new rows into the Customers table using the values from that XML data. Listing 16.15 shows a script named AddCustomersXml.sql that creates the AddCustomersXml() stored procedure. 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 @XmlDocumentId
OPENXML() reads the XML from the document specified by the handle @XmlDocumentId and returns the rows to the INSERT statement. These rows are then added to the Customers table by the INSERT statement. Listing 16.16 shows a script named RunAddCustomers.sql that runs the AddCustomersXml() procedure. Listing 16.16: RUNADDCUSTOMERS.SQL /* RunAddCustomersXml.sql runs the AddCustomersXml() procedure */ - define the XML document DECLARE @NewCustomers nvarchar(4000) SET @NewCustomers = N' T1COM Test 1 Company T2COM Test 2 Company ' - run the AddCustomersXml() procedure EXECUTE AddCustomersXml @MyCustomersXmlDoc=@NewCustomers - display the new rows SELECT CustomerID, CompanyName FROM Customers WHERE CustomerID IN ('T1COM', 'T2COM') - delete the new rows DELETE FROM Customers WHERE CustomerID IN ('T1COM', 'T2COM') Figure 16.17 shows the result of running the RunAddCustomers.sql script in Query Analyzer.
Figure 16.17: Running the RunAddCustomers .sql script