XML DATATYPE (series 1)

New to SQL Server 2005, is The XML data type, which lets you store XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that is missing a single top-level element. You can create columns and variables of the XML type and store XML instances in them. Note that the stored representation of XML data type instances cannot exceed 2 GB.

Where can you use the XML data type

a. Table
b. Variable
c. Parameters
d. Return of a function  

 

To do: Store XML to a table

a. Create a Table with XML data type

CREATE TABLE ProductDocs (ID INT IDENTITY PRIMARY KEY,
ProductDoc XML NOT NULL)
GO

 

b. Store XML data  Into the table

INSERT INTO ProductDocs VALUES('
<Product>
<ProductID>1</ProductID>
<ProductName>Chai</ProductName>
<SupplierID>1</SupplierID>
<CategoryID>1</CategoryID>
<QuantityPerUnit>10 boxes x 20   bags</QuantityPerUnit>
<UnitPrice>18.0000</UnitPrice>
<UnitsInStock>39</UnitsInStock>
<UnitsOnOrder>0</UnitsOnOrder>
<ReorderLevel>10</ReorderLevel>
<Discontinued>0</Discontinued>
</Product>
')

c. Retrieve XMLdoc

Select product from productdocs

D. create an XML Schema

CREATE XML SCHEMA COLLECTION ProductSchema AS '
<xs:schema xmlns:xs="

http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.microsoft.com/schemas/adventureworks/
products"
xmlns:prod="http://www.microsoft.com/schemas/adventureworks/
products">
<xs:element name="Product">
<xs:complexType>
<xs:sequence>
<xs:element ref="prod:ProductID" />
<xs:element ref="prod:ProductName" />
<xs:element ref="prod:SupplierID" />
<xs:element ref="prod:CategoryID" />
<xs:element ref="prod:QuantityPerUnit" />
<xs:element ref="prod:UnitPrice" />
<xs:element ref="prod:UnitsInStock" />
<xs:element ref="prod:UnitsOnOrder" />
<xs:element ref="prod:ReorderLevel" />
<xs:element ref="prod:Discontinued" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="ProductID" type="xs:integer" />
<xs:element name="ProductName" type="xs:string" />
<xs:element name="SupplierID" type="xs:integer" />
<xs:element name="CategoryID" type="xs:integer" />
<xs:element name="QuantityPerUnit" type="xs:string" />
<xs:element name="UnitPrice" type="xs:double" />
<xs:element name="UnitsInStock" type="xs:integer" />
<xs:element name="UnitsOnOrder" type="xs:integer" />
<xs:element name="ReorderLevel" type="xs:integer" />
<xs:element name="Discontinued" type="xs:boolean" />
</xs:schema> '

 

E. retrieve Schemas

SELECT * FROM sys.xml_schema_collections

Schemas are stored in Sys.xml_schema_collections