- Introduction
- Creating the Data
- XML Queries
- XML Document Modification
- Summary
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