|
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:
- 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
My3C's
perrychicker
Back | Tell A Friend | Search this Site
© 1998 - 2013 psacake.com
Version 3.23
Send me One Million FREE Guaranteed Visitors