Extending SQL Server with COM Objects

In the article titled Using COM Objects in SQL Server , we saw the basics of how to access COM objects (like DLL's) from SQL Server. Using this feature, we can extend the basic features of SQL Server as we will see in this article. If you check-out the XML processing capability of SQL Server, you can see that you have functions that can load an XML from a variable and then process it. What we lack is the ability to load an XML document from a file. In this article, we will see how to write such a procedure using the sp_OA* set of procedures.

In order to allow the loading of an XML from a file, we can use the MSXML parser, which has a method called load to load an XML from a file and a method called xml which retrieves the XML from the DOM document as a string. This string is then returned into a local T-SQL variable. Here is the code for the LoadXMLFromFile procedure.


IF (OBJECT_ID ('dbo.LoadXMLFromFile') IS NOT NULL)
	DROP PROCEDURE dbo.LoadXMLFromFile
GO
CREATE PROCEDURE dbo.LoadXMLFromFile
(
	@tcFileName		VARCHAR(255),
	@tcXMLString	VARCHAR(8000) OUTPUT
) AS
BEGIN
	-- Scratch variables used in the script
	DECLARE @retVal INT
	DECLARE @oXML INT
	DECLARE @errorSource VARCHAR(8000)
	DECLARE @errorDescription VARCHAR(8000)
	DECLARE @loadRetVal INT

	-- Initialize the XML document
	EXEC @retVal = sp_OACreate 'MSXML2.DOMDocument', @oXML OUTPUT
	IF (@retVal <> 0)
	BEGIN
		-- Trap errors if any
		EXEC sp_OAGetErrorInfo @oXML, @errorSource OUTPUT, @errorDescription OUTPUT
		RAISERROR (@errorDescription, 16, 1)

		-- Release the reference to the COM object
		EXEC sp_OADestroy @oXML
		RETURN
	END

	-- Load the XML into the document
	EXEC @retVal = sp_OAMethod @oXML, 'load', @loadRetVal OUTPUT, @tcFileName
	IF (@retVal <> 0)
	BEGIN
		-- Trap errors if any
		EXEC sp_OAGetErrorInfo @oXML, @errorSource OUTPUT, @errorDescription OUTPUT
		RAISERROR (@errorDescription, 16, 1)

		-- Release the reference to the COM object
		EXEC sp_OADestroy @oXML
		RETURN
	END

	-- Get the loaded XML
	EXEC @retVal = sp_OAMethod @oXML, 'xml', @tcXMLString OUTPUT
	IF (@retVal <> 0)
	BEGIN
		-- Trap errors if any
		EXEC sp_OAGetErrorInfo @oXML, @errorSource OUTPUT, @errorDescription OUTPUT
		RAISERROR (@errorDescription, 16, 1)

		-- Release the reference to the COM object
		EXEC sp_OADestroy @oXML
		RETURN
	END

	-- Release the reference to the COM object
	EXEC sp_OADestroy @oXML

END

This procedure loads an XML from a give file and then returns the XML as an output parameter. If any error is encountered in the code, we issue a RAISERROR from the procedure. One thing to remember is that we are bounded by the limit of 8000 characters for the XML string since TEXT variables are not allowed to be declared as variables . Functionally, the above procedure is equivalent to the following VB 6 code.


Public Function LoadXMLFromFile(ByVal tcFileName As String) As String
    Dim oXML As MSXML2.DOMDocument

    Set oXML = New MSXML2.DOMDocument
    oXML.Load (tcFileName)

    LoadXMLFromFile = oXML.xml
    Set oXML = Nothing
End Function

Actually, its very easy to write your COM component call in a rich development environment like VB and then translate that into a SQL Server sp_OA* call. This is useful because, we will not have access to any intellisense for COM objects from SQL Server. Thus, you should be very familiar with the methods and their parameters if you directly write code in SQL Server.

Ok, now having written the procedure, let's see how we can use this to load an XML file like the following:


<books>
	<book>
		<name>Code</name>
		<author>Charles Petzold</author>
	</book>
	<book>
		<name>The Elegant Universe</name>
		<author>Brian Creene</author>
	</book>
	<book>
		<name>A Brief History of Time</name>
		<author>Stephen Hawking</author>
	</book>
</books>

Here is the code for accessing our procedure to load this file and display its contents:


DECLARE @xmlString VARCHAR(8000)
DECLARE @xmlDoc INT, @error INT
DECLARE @startTime DATETIME, @endTime DATETIME
BEGIN
	-- Load the XML from a file
	SET @startTime = GETDATE()
	EXEC dbo.LoadXMLFromFile 'C:\Temp\Books.xml', @xmlString OUTPUT
	SET @error = @@ERROR
	SET @endTime = GETDATE()

	-- Check if there was any error
	IF (@error <> 0)
		RETURN

	-- Print some statistics of the XML loading
	SELECT 'XML Loading Time: ' + CAST (DATEDIFF (millisecond, @startTime, @endTime) AS VARCHAR(10)) + 'ms'

	-- Initialize the SQL Server XML stored procedures
	EXEC sp_xml_preparedocument @xmlDoc OUTPUT, @xmlString

	SELECT * FROM OPENXML (@xmlDoc, 'books/book') WITH
		(
			bookName	VARCHAR(100) 'name',
			author		VARCHAR(100) 'author'
		)

	EXEC sp_xml_removedocument @xmlDoc

END

The code is quite simple. We just call our procedure with the name of the file that we created above. We have also added some timing code to see how the code performs, since it is calling an external COM object. After the call is successful, we then use the standard SQL Server XML functions to load the XML into a DOM document and then display its contents using OPENXML. When you execute this code, the following output is shown:


------------------------------ 
XML Loading Time: 10ms

(1 row(s) affected)

bookName                     author                                                                                               
---------------------------- --------------------
Code                         Charles Petzold
The Elegant Universe         Brian Creene
A Brief History of Time      Stephen Hawking

(3 row(s) affected)

You can see that the loading time for a small XML like the above is negligible. You can work with various XML loads and see the performance yourself. You can also extend the above procedure by adding more error handling capabilities and other functions. Have fun!

I guess you will start appreciating the power that you have in your hands by extending SQL Server features. Take care that your COM component does not do something complex like opening a connection to SQL Server and then starting a transaction. In these cases, the behavior of your application will have performance issues and can result in deadlocks. Also, transaction behavior becomes very complex. If you limit yourself to writing small extensions like these, you can actually make SQL Server very powerful.

NB: Yukon promises to change all this by integrating the .NET Common Language Runtime directly into SQL Server. This opens a lot of doors to application programmers and database administrators. I've collected a set of Yukon public links here. Keep your fingers crossed till the product reaches its public beta :-)

Home