perfectxml.com
 Basic Search  Advanced Search   
Topics Resources Free Library Software XML News About Us
home » focus » msxml » ask a question » past questions & answers Friday, 12 October 2007
 
NEWS
MSXML 4.0 SP2 now available!

 
MSXML
Basics
DOM
SAX
XPath
XSLT
Schemas
SOM
HTTP Access
.NET
Data Islands
Ask a Question
   Past Q&As
C++ Samples
DLL/Version Info
Reference Guide
Books
KB Articles
   HOW TO
   SAMPLE
   INFO
   BUG/PRB
   FIX
   Misc.
MSXML Tips
   August 2002
   September 2002
MSXML Tools

Microsoft XML Core Services


Go back to list of previously asked questions and answers

Question: 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?
Asked By: Guest
Viewed: 4258
Answer: 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

Be sure to check out following related links:


Go back to list of previously asked questions and answers
  Contact Us | E-mail Us | Site Guide | About PerfectXML | Advertise ©2004 perfectxml.com. All rights reserved. | Privacy