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

start example
 /*   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 
end example

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

start example
 /*   RunAddCustomersXml.sql runs the AddCustomersXml() procedure */ - define the XML document DECLARE @NewCustomers nvarchar(4000) SET @NewCustomers = N' <Northwind>   <Customers>     <CustomerID>T1COM</CustomerID>     <CompanyName>Test 1 Company</CompanyName>   </Customers>   <Customers>     <CustomerID>T2COM</CustomerID>     <CompanyName>Test 2 Company</CompanyName> </Customers> </Northwind>' - 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') 
end example

Figure 16.17 shows the result of running the RunAddCustomers.sql script in Query Analyzer.

click to expand
Figure 16.17: Running the RunAddCustomers .sql script




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net