SQL Server Magazine

The format of the SchemaDeclaration parameter is similar to a column declaration. You define a rowset column name along with a data type and, optionally, a column pattern. If you use a column pattern, it overrides the mappings specified in the flags parameter, as I show later in the article. You can use the TableName parameter in place of SchemaDeclaration if a SQL Server table with a desired schema already exists, in which case you don't need column patterns.

The following example shows how to use an XML document as the data source for a call to OpenXML. First, you must prepare the XML by loading it into an in-memory tree representation, then store a handle to this DOM document in a local integer variable:

EXEC Master..sp_xml_preparedocument @idoc OUTPUT, @XML

Let's suppose that you've stored the XML in Listing 1 in the @XML variable. With the XML in Listing 1 loaded into a DOM structure, you can use the @idoc variable to access the DOM. The following SELECT statement shows you how to use OpenXML to turn this XML data into a relational rowset:

SELECT    *
FROM      OPENXML (@idoc, '/ROOT/Customer',1)
          WITH (CustomerID  varchar(10),
                ContactName varchar(20))

Here is the resulting rowset:

CustomerID ContactName
---------- -----------
VINET      Paul Henriot
LILAS      Carlos Gonzlez

You can use OpenXML in a variety of ways to convert XML documents into relational rowsets. For a full explanation and examples of OpenXML's different uses, see SQL Server 2000 Books Online (BOL).

Combining OpenXML and Commerce Server
Now that you understand how to manage e-commerce orders within the Commerce Server framework and how to use OpenXML to treat XML data as a relational rowset, you're ready to combine these two technologies to transmit and process e-commerce orders in one network round-trip.

First, let's consider some sample purchase data. Let's say that a sample shopping cart contains two unique product items and the customer has selected a quantity of two for the first item. The total price for the order is $87.00. In addition to product, SKU, and price data, the sample order contains customer and credit card data, which was gathered from a Web address information page populated with customer and order-header information.

The OrderForm component, which stores and manages order data for a particular order for a particular customer, can persist itself by streaming its data as an XML document. Web Listing 1 shows the XML produced by a Commerce Server OrderForm component containing the customer and order data gathered from the customer order. (For download instructions, see the More on the Web box.) Note a few things about the XML that the OrderForm component produces. First, OrderForm contains a very flexible data structure, capable of storing data for almost any type of order. Remember that OrderForm consists of several Dictionary and SimpleList components; the number depends on how many individual order items the order contains. Because of this flexibility, the XML that describes and contains the data held in an OrderForm component is verbose and hierarchical.

Note also that the XML includes two primary sections, corresponding to the header and detail portions of the order. The header section also contains the customer data. The XML document starts with a <DICTIONARY> element, followed by a <DICTITEM key="Items"> child element. The DICTITEM child elements contain all the order-header data. Listing 2 shows sample header data at the DICTITEM level, with each field name that the key attribute specifies and its value contained in the <VALUE> child element. At the top of the XML document in Web Listing 1, the two order-detail items are stored as children of the <DICTITEM key="Items"> element.

One last note about the XML that represents the OrderForm component: You can't pass it as is to SQL Server 2000's OpenXML function. The XML namespace value dt is never defined in the XML document. So, if the data tier passes the XML to SQL Server 2000 as is, you'll get the error message XML parsing error: Reference to undeclared namespace prefix: 'dt'. To make the XML document acceptable to SQL Server 2000, I simply declare the dt namespace in an element called <ROOT>, which I added to the document as follows:

<ROOT xmlns:dt="urn:www.teleport.com/~joet">

The Uniform Resource Name (URN) value can be any unique value. In this example, I used my personal home page URL as the URN value.

So, before you pass the XML that OrderForm produced to the data-tier component that calls your SQL Server 2000 stored procedure, you prepend to the document a <ROOT> element, in which you define the dt namespace. You then append a matching closing element at the end of the document. The Visual Basic (VB) code in Listing 3 shows how to add the namespace information to the XML document. (Note that showing how to create and populate the OrderForm component is beyond the scope of this article.) You then create a stored procedure called spOpenXML_PlaceOrder, to which you pass the OrderForm component's XML. Web Listing 2 shows the VB code that you use in the data-tier component to call this stored procedure and pass in the OrderForm XML.

Prev. page     1 2 [3] 4     next page