Home arrow XML arrow Extracting XML content from an XML file from within SQL Server 2000 using Managed Code

Extracting XML content from an XML file from within SQL Server 2000 using Managed Code

This article explains how to develop a .NET based component, which can extract XML content from an XML file and return the same to SQL Server 2000 with the help of T-SQL (Transact-SQL).

Author Info:
By: Jagadish Chaterjee
Rating: 5 stars5 stars5 stars5 stars5 stars / 6
November 29, 2005
  1. · Extracting XML content from an XML file from within SQL Server 2000 using Managed Code
  2. · Developing the .NET component
  3. · Deploying the .NET component
  4. · Testing the .NET component from within SQL Server 2000
  5. · Creating a stored function to call the .NET component and testing it

print this article

Extracting XML content from an XML file from within SQL Server 2000 using Managed Code
(Page 1 of 5 )

There are two downloadable files available for this article. You can find them here and here.

The sample downloadable solutions (zip) were entirely developed using Visual Studio.NET 2003 Enterprise Architect on Windows Server 2003 Standard Edition.  But, I am confident that it would work with other versions of Windows (which support .NET 1.1) versions as well.

To help you understand this article, I suggest you refer to my previous contribution “Developing Managed Code and executing it from within Microsoft SQL Server 2000.”

The problem and the solution

Microsoft SQL Server 2000 already supports XML.  The XML support has been greatly enhanced with the help of “Configuring IIS to support SQL Server XML.”  You can already find several articles on www.aspfree.com to help you work with Microsoft SQL Server 2000 and XML.

The most important issue to understand is that Microsoft SQL Server 2000 (unlike Microsoft SQL Server 2005) does not natively support XML.  The simplest example of this is that it does not have any “XML related data type” (but Microsoft SQL Server 2005 does).  We need to still use the “Varchar” data type to hold XML data in the table.

As long as storage is concerned, it may not be a big issue.  We can create a simple column of type “Varchar” data type and place it in XML (as if it is a huge string).  Inserting or modifying XML content with the “Varchar” data type is very simple and does not need much overhead.  But what if I need to upload an XML file into a column of type “Varchar”?  This would be an issue.  SQL Server 2000 natively will not be able to access any file system.

Microsoft really thought about this scenario (and of course, several others too) and developed a special SDK to work with XML along with SQL Server 2000.  The extension is called SQLXML.  It is really a great extension to SQL Server 2000, which gives all sorts of possibilities for integrating XML directly into the SQL Server 2000.  But, just to upload an XML file into a field (or column), it is completely unnecessary to work with SQLXML unless you have other issues tied to that.

Finally, I needed to develop my own component to read an external XML file and return the content to SQL Server 2000 with a simple stored function.  And to my amazement, it worked fine and I was very successful.  How did I develop it?

As usual, we develop our managed code (.NET code) using a “class library” project, sign it “strongly” and push it into GAC (Global Access Cache).  As Microsoft SQL Server 2000 doesn’t know anything about GAC, we need to register it as a COM component also (using “RegAsm” Utility).  Once all of the above steps are completed, we need to create a stored function to access the component using some of the built-in “extended stored procedures.”

Now, let us stop telling stories and implement the concept.

blog comments powered by Disqus

- Open XML Finally Supported by MS Office
- XML Features Added to Two Systems
- Using Regions with XSL Formatting Objects
- Using XSL Formatting Objects
- More Schematron Features
- Schematron Patterns and Validation
- Using Schematron
- Datatypes and More in RELAX NG
- Providing Options in RELAX NG
- An Introduction to RELAX NG
- Path, Predicates, and XQuery
- Using Predicates with XQuery
- Navigating Input Documents Using Paths
- XML Basics
- Introduction to XPath

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials