YOMEDIA
Using the SQL Server OPENXML() Function
Chia sẻ: Qweqwdasd Qweqdasda
| Ngày:
| Loại File: PDF
| Số trang:4
247
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à...
AMBIENT/
Chủ đề:
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
Thêm tài liệu vào bộ sưu tập có sẵn:
Báo xấu
LAVA
ERROR:connection to 10.20.1.98:9315 failed (errno=111, msg=Connection refused)
ERROR:connection to 10.20.1.98:9315 failed (errno=111, msg=Connection refused)
Đang xử lý...
Thêm vào BST
Báo xấu
