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.
Next: Creating a stored function to call the .NET component and testing it >>
More XML Articles
More By Jagadish Chaterjee
