Learn Xml: Untyped XML Columns

Untyped XML is useful when the schema is not known prior so that a mapping-based solution is not

possible. It is also useful when the schema is known but mapping to relational data model is very com-

plex and hard to maintain, or multiple schemas exist and are late bound to the data based on external

requirements. The following statement creates a table called Employee in the AdventureWorks with an

integer primary key id and an untyped XML column xml_data:

Use AdventureWorks

CREATE TABLE Employee( id int primary key, xml_data xml)

The Employee table that is created in the AdventureWorks database will be used throughout this chap-

ter. To insert values into the previous table, use the following T-SQL statement.

INSERT INTO Employee values(2, ‘<employee id=”2”><name>Joe</name></employee>’)

Note that you can also create a table with more than one XML or relational columns with or without a

primary key.

Typed XML Columns

If you have XML schemas in an XML schema collection describing your XML data, you can associate the

XML schema collection with the XML column to yield typed XML. The XML schemas are used to vali-

date the data, perform more precise type checks than untyped XML during compilation of query and

data modification statements, and optimize storage and query processing.

XML Schema Collections

Support for typed XML columns is enabled by using XML schema collections in SQL Server. XML

schema collections are defined like any other SQL Server object, and they are stored in SQL Server. An

XML schema collection is created using CREATE XML SCHEMA COLLECTION T-SQL statement by providing

one or more XML schemas. More XML schema components can be added to an existing XML schema,

and more schemas can be added to an XML schema collection using ALTER XML SCHEMA COLLECTION

syntax. XML schema collections can be secured like any SQL object using SQL Server 2005’s security

model. The syntax for the T-SQL DDL CREATE XML SCHEMA COLLECTION statement is as follows:

lanka sri