Extracting XML content from an XML file from within SQL Server 2000 using Managed Code
(Page 1 of 5 )
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).
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.
Next: Developing the .NET component >>
More XML Articles
More By Jagadish Chaterjee
