Creating the Data
To store the optimized XML markup in the SQL Server 2005 table, you have to define a table column with the xml data type. You might decide that the XML column could contain document fragments (valid XML markup without a single root element) or just valid XML documents (which can have only a single root element). For XML markup with a well-defined structure, you could also attach an XML schema to the XML column.
For example, to create a table that will store product data sold in an online department store, you might use the SQL statement shown in Listing 1.
Listing 1 Product table using XML columns to store product properties and description.
CREATE TABLE Products ( ProductID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ProductName VARCHAR(50) NOT NULL, SupplierID INT NOT NULL CONSTRAINT Supplier_FK FOREIGN KEY REFERENCES Suppliers(SupplierID), Price DECIMAL(10,2) NOT NULL, Attributes XML, Description XML(DOCUMENT) NOT NULL)
The Products table contains two XML columns: the Attributes column stores loosely structured product attributes (for an in-depth discussion, see "Store XML Data in Relational Databases"), and the Description column contains the product description. Because the product description should always be present and should contain a valid XML document, these two requirements are expressed in the table definition.
***Please link the highlighted text above to the Pepelnjak article with that title.
To create a new product, you might use the VBScript code in Listing 2.
Listing 2 Create a new product in VBScript.
Set RS = Server.CreateObject("ADODB.Recordset") RS.Open "Products",db,adOpenKeyset,adLockOptimistic RS.AddNew RS("ProductName") = "Test" RS("SupplierID") = 112 RS("Price") = 10.35 Set XML = NewXMLObject("root",False) NewXMLTextElement XML,"Color","Red",Null NewXMLTextElement XML,"Size","XL",Null NewXMLTextElement XML,"Fabric","Cotton",Null RS("Attributes").Value = XML.xml RS("Description") = "<div><p>Sample product description</p></div>" RS.Update RS.Close