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

Poor Best

Rating:

/ 2

November 29, 2005

TABLE OF CONTENTS:

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"?>

<ROW>

</ROW>

<ROW>

</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.

Next: Creating a stored function to call the .NET component and testing it >>