Home arrow XML arrow Page 4 - 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 - Testing the .NET component from within SQL Server 2000
(Page 4 of 5 )

After completing all of the above steps, we need to test the component.  To test the component, the first issue is that we need to create an XML file in any path.  As this is a demonstration, I created a simple file (“Sample.xml”) in my root C drive.  The content within that “Sample.xml” file would be as follows:

<?xml version="1.0"?>

<ROWSET>
 <ROW>
  <EMPNO>1001</EMPNO>
  <ENAME>aaaa</ENAME>
  <SAL>1300</SAL>
  <DEPTNO>10</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>1002</EMPNO>
  <ENAME>bbbb</ENAME>
  <SAL>3000</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
</ROWSET>

After saving the above XML, open up your query analyzer and type the following Transact-SQL code, to test the component:

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
      SELECT @errorSource, @errorDescription
END
 
EXEC @ExecutionResult = sp_OAMethod @hnd, 'getXMLContent', @result OUTPUT, 'c:\sample.xml'
IF (@ExecutionResult <> 0)
BEGIN
      EXEC sp_OAGetErrorInfo @hnd, @errorSource OUTPUT, @errorDescription OUTPUT
      SELECT @errorSource, @errorDescription
END
 
EXEC @ExecutionResult = sp_OADestroy @hnd
IF (@ExecutionResult <> 0)
BEGIN
      EXEC sp_OAGetErrorInfo @hnd, @errorSource OUTPUT, @errorDescription OUTPUT
 
      SELECT @errorSource, @errorDescription
END
 
SELECT @result

Just press F5 to execute the script and it should return to you the content available in the “c:\sample.xml” file.  It is not necessary to handle the errors as above, but it is a good practice to have it included.


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