Use MSXML inside T-SQL script

Q.Is it possible to use MSXML inside T-SQL script? I am writing a SQL Server 2000 stored procedure and need to process some XML. Basically, I am storing XML data in a table column, and in the stored procedure, I need to read that XML column value, update it and save it back to the table column. Can I use MSXML inside a stored procedure to do this?

A.SQL Server 2000 offers system stored procedures that can be used to work with external COM objects. These stored procedure names begin with sp_OA. Note that only members of the sysadmin fixed server role can execute these stored procedures. As MSXML is an automation-enabled COM object, it can be used from inside T-SQL code. The following example illustrates using MSXML in a T-SQL script to load the XML and update it.

The following script:

Creates a sample database table
Inserts a record into this new table with one of the column value as a well-formed XML text
Creates MSXML 4.0 DOMDocument object, reads the column data (that is well-formed XML text) from the table, loads that into the DOMDocument using loadXML, creates a new node, set's its value and appends this new node under the root element.
Saves the updated XML back into the table column.

SET NOCOUNT ON

--Create sample table
CREATE TABLE testData 
	(RowID int IDENTITY(1, 1) PRIMARY KEY,
	SomeData nvarchar(4000)
	)
GO 

--Insert a record, with SomeData containing well-formed XML text
INSERT INTO testData (SomeData) VALUES 
	('<Sites><Tech>PerfectXML.com</Tech></Sites>')
GO

--Create MSXML 4.0 DOMDocument object
DECLARE @xmlDOMObj int
DECLARE @hr int
DECLARE @strErrSource varchar(255), @strErrDesc varchar(255)

EXEC @hr = sp_OACreate 'MSXML2.DOMDocument.4.0', @xmlDOMObj OUT

--If failed to create MSXML DOMDocument COM object
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @xmlDOMObj, @strErrSource OUT, @strErrDesc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@strErrSource, 
                   Description='While creating DOMDocument: ' + @strErrDesc
   RETURN
END


--Succeeded creating MSXML DOMDocument COM object
--Get the XML Data from the database record
DECLARE @xmlData nvarchar(4000)
SELECT @xmlData = SomeData FROM testData WHERE RowID = 1
--PRINT @xmlData

--Load the XML data into the DOMDocument object
DECLARE @loadResult int
EXEC @hr = sp_OAMethod @xmlDOMObj, 'loadXML', @loadResult OUTPUT, @xmlData
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @xmlDOMObj, @strErrSource OUT, @strErrDesc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@strErrSource, 
               Description='While loading XML using loadXML: ' + @strErrDesc
  
   GOTO endScript
END
--PRINT @loadResult

IF @loadResult = 0 
BEGIN
	PRINT 'ERROR: Failed to load the XML document! '
	GOTO endScript
END

--Load succeeded, update the XML document, create a new node
DECLARE @newNode int
EXEC @hr = sp_OAMethod @xmlDOMObj, 'createNode', @newNode OUTPUT, 1, 'News', ''
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @xmlDOMObj, @strErrSource OUT, @strErrDesc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@strErrSource, 
               Description='While creating a new node: ' + @strErrDesc
  
   GOTO endScript
END

--Set the new node's value to CNN.com
EXEC @hr = sp_OASetProperty @newNode, 'Text', 'CNN.com'
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @xmlDOMObj, @strErrSource OUT, @strErrDesc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@strErrSource, 
               Description='While setting the new node value: ' + @strErrDesc
  
   GOTO endScript
END


--Append the newly created "News" node under the Sites root node
--First get the root element node

DECLARE @rootNode int
EXEC @hr = sp_OAGetProperty @xmlDOMObj, 'documentElement', @rootNode OUTPUT
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @xmlDOMObj, @strErrSource OUT, @strErrDesc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@strErrSource, 
               Description='While getting the root element: ' + @strErrDesc
  
   GOTO endScript
END

--now Append the child
DECLARE @addedNode int
EXEC @hr = sp_OAMethod @rootNode, 'appendChild', @addedNode OUTPUT, @newNode
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @xmlDOMObj, @strErrSource OUT, @strErrDesc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@strErrSource, 
               Description='While adding the new node: ' + @strErrDesc
  
   GOTO endScript
END

--Get the XML text (using the xml property) from the DOMDocument
DECLARE @strXMLText nvarchar(4000)
EXEC @hr = sp_OAGetProperty @xmlDOMObj, 'xml', @strXMLText OUTPUT
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @xmlDOMObj, @strErrSource OUT, @strErrDesc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@strErrSource, 
               Description='While getting the xml property value: ' + @strErrDesc
  
   GOTO endScript
END

PRINT @strXMLText

--Update the database column
UPDATE testData SET SomeData = @strXMLText WHERE RowID = 1

SELECT * FROM testData

endScript:
EXEC @hr = sp_OADestroy @xmlDOMObj
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @xmlDOMObj, @strErrSource OUT, @strErrDesc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@strErrSource, 
               Description='While destroying the DOMDocument object: ' + @strErrDesc
END

SET NOCOUNT OFF

RETURN

asp xml msxml sql t-sql