다음을 통해 공유

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

  • 아티클

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

Comments

  • Anonymous
    July 30, 2004
    Sushil, I truly enjoyed your post and find it pretty impressive. I'm installing 2005 beta right now so hope to play with it in a few. As far as the insert statement goes, how does this integrate with ADO.NET? Is there a command paramter type that i could set to a datarow for instance, or would I create the XML as a string from the client side ? I guess I can figure it out on my own in a few minutes, but was just interested in the ADO.NET 2.0 integration. Even if it's really manual, this is still really kick a33.
  • Anonymous
    July 30, 2004
    Thanks for the comments Bill. XML values are can be passed as Strings to XML columns via ADO.Net. In addition to this, there are other options(passing as XMLReader, Stream). I plan to write Part II based on how this is integrated with ADO.Net. I will update this soon.
  • Anonymous
    August 01, 2004
    Sushil,

    wtc
  • Anonymous
    June 08, 2009
    PingBack from http://insomniacuresite.info/story.php?id=7525