Using Adventure Works for illustration, catalog descriptions for bicycle product models are stored as XML in the CatalogDescription column. Each product model can contain several individual products. For example, Road 450 is a bicycle product model that includes several bicycles such as Road 450 red 25 and Road 450 Green 48. The primary difference among the bicycles in a product model is the color and the size.
You can query these product descriptions, apply XSL transformation to them, and generate online catalogs. The catalog information stored in the CatalogDescription column does not use an industry-standard schema. Therefore, if required, you can write queries to build an industry-standard XML product catalog.
This is a typed xml column. The schemas used for this column can be viewed at this Microsoft Web site. This column uses the AdventureWorks ProductModelDescription and ProductModelWarrAndMain XML schemas.
This is a sample XML instance stored in the ProductModel.CatalogDescription column:
<?xml-stylesheet href="ProductDescription.xsl" type="text/xsl"?>
<p1:ProductDescription xmlns:p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription" xmlns:wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" xmlns:wf="http://www.adventure-works.com/schemas/OtherFeatures" xmlns:html="http://www.w3.org/1999/xhtml" ProductModelID="19" ProductModelName="Mountain 100">
<p1:Summary>
<html:p>Our top-of-the-line competition mountain bike. Performance-enhancing options include the innovative HL Frame, super-smooth front suspension, and traction for all terrain.</html:p>
</p1:Summary>
<p1:Manufacturer>
<p1:Name>AdventureWorks</p1:Name>
<p1:Copyright>2002</p1:Copyright>
<p1:ProductURL>HTTP://www.Adventure-works.com</p1:ProductURL>
</p1:Manufacturer>
<p1:Features>These are the product highlights.
<wm:Warranty><wm:WarrantyPeriod>3 years</wm:WarrantyPeriod><wm:Description>parts and labor</wm:Description></wm:Warranty><wm:Maintenance><wm:NoOfYears>10 years</wm:NoOfYears><wm:Description>maintenance contract available through your dealer or any AdventureWorks retail store.</wm:Description></wm:Maintenance><wf:wheel>High performance wheels.</wf:wheel><wf:saddle><html:i>Anatomic design</html:i> and made from durable leather for a full-day of riding in comfort.</wf:saddle><wf:pedal><html:b>Top-of-the-line</html:b> clipless pedals with adjustable tension.</wf:pedal><wf:BikeFrame>Each frame is hand-crafted in our Bothell facility to the optimum diameter and wall-thickness required of a premium mountain frame. The heat-treated welded aluminum frame has a larger diameter tube that absorbs the bumps.</wf:BikeFrame><wf:crankset> Triple crankset; alumunim crank arm; flawless shifting. </wf:crankset></p1:Features>
<!-- add one or more of these elements... one for each specific product in this product model -->
<p1:Picture>
<p1:Angle>front</p1:Angle>
<p1:Size>small</p1:Size>
<p1:ProductPhotoID>118</p1:ProductPhotoID>
</p1:Picture>
<!-- add any tags in <specifications> -->
<p1:Specifications> These are the product specifications.
<Material>Almuminum Alloy</Material><Color>Available in most colors</Color><ProductLine>Mountain bike</ProductLine><Style>Unisex</Style><RiderExperience>Advanced to Professional riders</RiderExperience></p1:Specifications>
</p1:ProductDescription>
Run the following query to find more XML instances:
SELECT CatalogDescription
FROM Production.ProductModel
Concepts
Sample XML Applications
Help and Information
Getting SQL Server 2008 Assistance