Using OpenXML to Insert Data

The most practical application of the OpenXML function is to insert data from an XML document into tables in the database. Which Transact-SQL statement you use to do this depends on whether the table you want to store the data in already exists.

Inserting Data into an Existing Table

To insert data into an existing table, you use the Transact-SQL INSERT statement this way:

 INSERT Orders SELECT * FROM OPENXML(@iTree, ‘Order’, 1) WITH Orders 

This statement inserts the data from the XML purchase order document into a matching Orders table. Of course, the Northwind database’s Orders table, like those in many production scenarios, contains columns that can’t be mapped to the attributes or elements in the XML document. Here’s a simplified definition of the Northwind Orders table:

 CREATE TABLE Orders  (OrderID INTEGER IDENTITY (1, 1) NOT NULL ,  CustomerID nCHAR (5) NULL ,  EmployeeID INTEGER NULL ,  OrderDate DATETIME NULL ,  RequiredDate DATETIME NULL ,  ShippedDate DATETIME NULL ,  ShipVia INTEGER NULL ,  Freight MONEY NULL ,  ShipName nVARCHAR (40) NULL ,  ShipAddress nVARCHAR (60) NULL ,  ShipCity nVARCHAR (15) NULL ,  ShipRegion nVARCHAR (15) NULL ,  ShipPostalCode nVARCHAR (10) NULL ,  ShipCountry nVARCHAR (15) NULL) 

All columns in the Orders table (except the OrderID column) allow NULL values to be inserted. This means that even though the information isn’t supplied in the XML document, we can still insert either a NULL or an explicit value for the missing columns. Another issue is that the OrderID column is an IDENTITY column, which means that by default SQL Server automatically generates a value for each new record. This action can be overridden by setting the IDENTITY_INSERT property for the table to ON.

To incorporate these requirements in our Transact-SQL statement, we need to make a few changes. First we need to set the IDENTITY_INSERT option ON for the Orders table. Then we need to change the INSERT and SELECT clauses in the preceding Transact-SQL statement so that they include all the columns required for the Orders table. The columns must be listed in the INSERT clause when SQL Server explicitly inserts an IDENTITY column value, and the actual values to be inserted for the missing columns must be specified in the SELECT clause. Next we need to change the WITH clause in the OpenXML function to explicitly specify the columns retrieved from the XML document. Finally we need to return the IDENTITY_INSERT option to the OFF setting. The following Transact-SQL statement shows how a new row composed of data from the XML document and values explicitly assigned in the SELECT clause can be inserted into the Orders table.

 SET IDENTITY_INSERT Orders ON INSERT Orders (OrderID, CustomerID, EmployeeID, OrderDate,                 RequiredDate, ShippedDate, ShipVia, Freight,                 ShipName, ShipAddress, ShipCity, ShipRegion,                 ShipPostalCode, ShipCountry) SELECT OrderID, CustomerID, EmployeeID, OrderDate,        NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL  FROM OPENXML(@iTree, ‘Order’, 1) WITH (OrderID INTEGER, EmployeeID INTEGER, OrderDate DATETIME,       CustomerID nCHAR(5)) SET IDENTITY_INSERT Orders OFF 

This code can be used to insert a new row in the Northwind Orders table. The OrderID, CustomerID, EmployeeID, and OrderDate values are retrieved from the XML document, and NULL is explicitly assigned for the rest of the columns apart from Freight, which is assigned a default value of 0.

Of course, the purchase order also contains data for the Order Details table, so the stored procedure to insert all of the data in the XML document would look something like this:

 CREATE PROCEDURE InsertOrder @xmlOrder VARCHAR(2000) AS DECLARE @iTree INTEGER EXEC sp_xml_preparedocument @iTree OUTPUT, @xmlOrder SET IDENTITY_INSERT Orders ON INSERT Orders (OrderID, CustomerID, EmployeeID, OrderDate,                 RequiredDate, ShippedDate, ShipVia, Freight,                 ShipName, ShipAddress, ShipCity, ShipRegion,                 ShipPostalCode, ShipCountry) SELECT OrderID, CustomerID, EmployeeID, OrderDate,        NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL  FROM OPENXML(@iTree, ‘Order’, 1) WITH (OrderID INTEGER, EmployeeID INTEGER, OrderDate DATETIME,       CustomerID nCHAR(5)) SET IDENTITY_INSERT Orders OFF INSERT [Order Details] SELECT * FROM OPENXML(@iTree, ‘Order/Items/Item’, 1) WITH (OrderID INTEGER ‘../../@OrderID’, ProductID INTEGER,       Qty INTEGER, UnitPrice MONEY, Discount REAL ‘Discount’) EXEC sp_xml_removedocument @iTree 

You can view code similar to this in the InsertOrder.sql script in the Demos\Chapter7 folder on the companion CD. This procedure receives the XML purchase order and creates an internal tree representation of the purchase order document. The procedure then inserts the data from the Order element into the Orders table and the data from the Item element into the Order Details table. Finally it reclaims the memory used by the node tree.

Because the procedure inserts data into two tables, you should use a transaction to ensure data integrity isn’t broken in the event of an error. You could do this by adding BEGIN TRAN and COMMIT TRAN statements to the stored procedure, using the BeginTrans and CommitTrans methods of an ADO Connection object; or you could ensure integrity by calling the stored procedure from a transactional COM+ or MTS component.

Creating a New Table

You might occasionally want to use the data in an XML document to create and populate a new table. This strategy might be a suitable approach were you revising a product catalog, say, for which the XML catalog document contained an updated version of the entire catalog. The most efficient way to update the database might be to simply drop the existing catalog table and re-create it with the new data.

To insert data into a new table, you use the SELECT…INTO syntax, as shown in the following example:

 SELECT * INTO Products FROM OPENXML(@iTree, ‘Product’, 1) WITH (ProductID INTEGER,       ProductName VARCHAR(20),       UnitPrice MONEY) 

This code creates a new table named Products, which uses the schema defined in the WITH clause, and populates it with the data from the XML document referenced by @iTree.

Inprevious versions of SQL Server, the SELECT…INTO syntax could be used with permanent tables only when the SELECT INTO/BULKCOPY option was set to ON. In SQL Server 2000, this requirement is eliminated.

If the Products table already exists, the preceding Transact-SQL statement will fail, so any stored procedure using this approach would need to use the DROP TABLE statement to delete the existing table first. Of course, if an existing table is dropped, any constraints, such as primary keys or foreign keys, and any indexes will be dropped with it. Re-creating the necessary constraints and indexes after the table has been re-created and populated is so much work that you might not want to take this approach. A more suitable tactic might be to use a TRUNCATE TABLE statement to delete the existing records and then use an INSERT statement to insert the new data.



Programming Microsoft SQL Server 2000 With Xml
Programming Microsoft SQL Server(TM) 2000 with XML (Pro-Developer)
ISBN: 0735613699
EAN: 2147483647
Year: 2005
Pages: 89

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