XML Data type in ADO.Net v2.0 - Part I

  • Article

After a long hiatus, let me start with how the new XML data type in SQL Server 2005 is exposed via ADO.Net 2.0. I will discuss this new data type in following parts:

  1. XML data type in SQL Server 2005 – A brief Introduction : You can consider this as a crash course on XML data type in SQL Server 2005.
  2. ADO.Net 2.0 and XML DataType
    1. Getting XML values from SqlDataReader.
    2. Passing XML values as Parameters

The following is section 1 of the 2 part series:

XML data type in SQL Server 2005 – A brief Introduction

A new scalar data type is introduced in SQL Server 2005 for storage and retrieval of XML data. XML value that is stored in the column is basically an XML fragment like single root, multiple roots, text nodes, empty string, text nodes at the top. Consider you want to create a Customer table with the following Columns:

CustomerId int,
CustomerName varchar(40),
OrderXml xml

Here is an example showing how to create such a table and insert some values as literals using TSQL

Untyped XML
CREATE TABLE Customer (CustomerId int, CustomerName varchar(40), OrderXml xml)
go

INSERT INTO Customer VALUES (1,'Allison Gray',
'<order>
<item>
<id>20</id>
<name>Widgets</name>
<units>3</units>
</item>
</order>')
go

Fact: If the string character is Unicode then the XML values is always parsed in as UTF-16.

Typed XML
The above XML data type is untyped meaning it is not associated with any schema. If we know that OrderXML adheres to one specific XML Schema, we can associate it to a previously loaded XML schema with the following syntax:
-- Consider the Schema/namespace - OrderSchema is defined using the CREATE XML SCHEMA COLLECTION statement
CREATE TABLE Customer (CustomerId int, CustomerName varchar(40), OrderXml xml(OrderSchema))
go

XML Methods
In addition to the above, XML data type has some methods. Some of them are:

  1. Query method: Given a XQUERY as an argument, this method performs that query on the XML data type.
  2. Value method: Given a XQUERY as an argument, this method runs the query and returns a scalar value.
  3. Exist method: Given an XQUERY as an argument, this method returns 1 if the XQuery expression returns a non-empty result from the XML column else it returns 1.
  4. Modify method: Used to modify XML data. Basically this performs XML data manipulation as specified in the arguments.

Since our goal is to see the interaction of XML data type with ADO.Net, the above introduction would suffice. There are other awesome features on XML data type on the server and are not discussed here due to the scope of the article.

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights