Home > Articles > Data > SQL Server

  • Print
  • + Share This

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
  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.