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