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

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
TABLE OF CONTENTS:
  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
SEARCH DEVARTICLES

Extracting XML content from an XML file from within SQL Server 2000 using Managed Code - Creating a stored function to call the .NET component and testing it
(Page 5 of 5 )

The previous section only tested whether the component is really working.  After successfully testing it, we need to wrap all of the above code (replacing the hard coded ones to parameters) into a single stored function, so that we can use it very flexibly any number of times.  At the moment, I named the stored function  “getXMLData”.

Open up your query analyzer and type the following Transact-SQL code, to create the stored function:

create function getXMLData

(
      @FileName varchar(200)
)
returns varchar(800)
as
begin
 
DECLARE  @ExecutionResult INT, @errorSource VARCHAR(100), @errorDescription VARCHAR(100), @result VARCHAR(800),@hnd INT
 
EXEC @ExecutionResult = sp_OACreate 'Utility.CUtility', @hnd OUTPUT
IF (@ExecutionResult <> 0)
BEGIN
      EXEC sp_OAGetErrorInfo @hnd, @errorSource OUTPUT, @errorDescription OUTPUT
      return @errorDescription
END
 
EXEC @ExecutionResult = sp_OAMethod @hnd, 'getXMLContent', @result OUTPUT, @FileName
IF (@ExecutionResult <> 0)
BEGIN
      EXEC sp_OAGetErrorInfo @hnd, @errorSource OUTPUT, @errorDescription OUTPUT
      return @errorDescription
END
 
EXEC @ExecutionResult = sp_OADestroy @hnd
IF (@ExecutionResult <> 0)
BEGIN
      EXEC sp_OAGetErrorInfo @hnd, @errorSource OUTPUT, @errorDescription OUTPUT
 
      return @errorDescription
END
 
return @result
end

Just press F5 to execute the script and it should give you a message saying “Script executed successfully”.  After creating the stored function, we need to test it (which is the final testing).  The following simple T-SQL command will test the functionality of the stored function that we created above:

select dbo.getXMLData('c:\sample.xml') as XMLContent

Once the above command is executed, it should return to you all of the XML content available in “c:\sample.xml” file.  Now it is very easy to use it a number of times throughout your project life cycle!

Remarks

This article is mainly focused on SQL Server 2000 and not at all on SQL Server 2005 (or SQL Server Yukon).  It is not at all suggested to use this type of implementation in SQL Server 2005 as it has its own CLR implementation within the SQL Server engine itself.  You can directly use .NET programming within SQL Server 2005 without referring to (or working with) any COM related issues. 

I am trying to contribute few more real scenarios using the same concept.  Keep watching for new articles.  Any comments, suggestions, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.


DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

blog comments powered by Disqus
XML ARTICLES

- 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 
Support 

Developer Shed Affiliates

 




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