April 13, 2011, 7:10 p.m.
posted by pumi
Working with SQL Server 2005 XML Data
XML data isn't only sourced from documents, especially now that SQL Server 2005, which not only increases its general support for XML but also supports XML data as a native data type, allows you to do the following:
There are two types of XML columns in SQL Server 2005: untyped and typed. Untyped columns have no schema, and data is stored in character format, complete with elements and attributes. Untyped columns are not validated, although they are checked for well-formed data. A typed column has a schema and therefore has validation applied when data is added or modified.
Creating XML Columns
Since the XML type is a standard type in SQL Server 2005, you simply use xml as a data type.
CREATE TABLE MyTable(MyIDColumn int, MyXmlColumn xml)
For a typed column, you must specify the schema when the table is created.
CREATE TABLE MyTable(MyIDColumn int, MyXmlColumn xml(MySchema))
The advantage of typed columns is that the data within them is converted to native types (int, char, etc.), allowing not only validation as data is inserted and modified but also querying within the XML.
Before you can create a typed column, you need to create a schema to define the structure of the XML that is allowable for the column. Schemas are stored within a schema collection, which can hold one or more schemas. To use a schema to define an xml data column, you must first create and register a schema collection that includes the schema with SQL Server using the CREATE XML SCHEMA COLLECTION statement. This statement takes a name that will be used to identify the schema collection after registration and a string-type parameter that contains or references the schema or schemas to be registered in that collection. In the simplest case, the code looks like this:
CREATE XML SCHEMA COLLECTION MySchema AS '<xsd:schema xmlns="..."> ... schema content ... </xsd:schema>'
After registration, the schema is identified by the name you specify (in this example, MyNewSchema). An alternative way to register a schema is to assign it to a variable and then use the variable in the CREATE XML SCHEMA COLLECTION statement. This approach is also useful when uploading schemas from the client because the schema itself can be sent as a parameter.
DECLARE @myschema nvarchar(1000) SET @myschema = N'<xsd:schema xmlns="..."> ... schema content ... </xsd:schema>' CREATE XML SCHEMA COLLECTION MyNewSchema @myschema
You can register more than one schema in a collection and have them all referenced with a single name by simply placing them in the string value you pass to the CREATE XML SCHEMA COLLECTION statement.
CREATE XML SCHEMA COLLECTION MyNewSchema '<xsd:schema xmlns="..."> ... schema content ... </xsd:schema> <xsd:schema xmlns="..."> ... schema content ... </xsd:schema> <xsd:schema xmlns="..."> ... schema content ... </xsd:schema>'
Within SQL Server Management Studio, you can apply a schema to an XML column in the table design, as seen in Figure.
15. Specifying the schema for an XML column
Inserting Data into XML Columns
INSERT INTO MyTable(MyIDColumn, MyXmlColumn) VALUES(1, '<cars>... etc ...</cars>')
You can also cast the data as you insert it.
INSERT INTO MyTable(MyIDColumn, MyXmlColumn) VALUES(1, CAST('<cars>... etc ...</cars>' AS xml)
Casting is also useful for enforcing the schema.
INSERT INTO MyTable(MyIDColumn, MyXmlColumn) VALUES(1, CAST('<cars>... etc ...</cars>' AS xml(MySchema))
Accessing XML Columns from ADO.NET
For any particular data type to be of real use in applications, the client-side data access technology you use must be able to read, manipulate, and write instances of that data type. This applies to the new xml data type supported by SQL Server 2005. You would expect to be able to work with it in ADO.NET just like you do with any other built-in data types. So it's no surprise to find that version 2.0 of ADO.NET supports the xml data type as a native type.
Support for the xml data type is implemented by several extensions to the ADO.NET classes, including the following :
These additions are used when accessing data in an xml column of SQL Server 2005 through a DataSet and a DataReader. For example, consider Listing 7.18, where a simple SELECT is performed over a table containing an XML column (Instructions). The column can be accessed like any other column, but untyped access like this returns the column data as a string.
Compare this with Listing 7.19, where the column is not accessed directly but instead the GetSqlXml method is used to return the column as a SqlXml type. The SqlXml type has a CreateReader method which returns an XmlReader, which can be used to navigate over the XML.
Streaming an XML Column
If you are dealing with DataSets or DataTables, you can load the data as normal, but you must set the ReturnProviderSpecificTypes property to true, as seen in Listing 7.20, before loading the data, if you wish XML columns to be strongly typed.
This example loads the data twice, first with weak typing and then with strong typing. The output of this is:
Column type=System.String Column type=System.Data.SqlTypes.SqlXml
The use of ReturnProviderSpecificTypes is an important consideration if you are building data layers that return XML columns. There is, of course, nothing stopping you from returning the XML as a string and then loading this into an XmlDocument using the LoadXml method, but it's an extra step you don't need to do with strongly typed data.
Loading Data into a DataTable