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?

ASQL 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: 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
My3C's
perrychicker
It's easy... it's a PSACAKE!
Back | Tell A Friend | Search this Site
1998 - 2014 psacake.com
Version 3.23

Send me One Million FREE Guaranteed Visitors