SQL Server and XML

[Amy]

Hi,
I am looking for a third party tool or something within SQL Server itself,
where I can pull a stored XML String from a SQL Database (and or oracle),
edit it, check for errors and then insert the modified string into the database.
If any one has information on a tool that can do this or knows how SQL Server
is capable of doing this, I would greatly appreciate it.
Thank You
Amy

[jfermin]

"Amy" <akotsmith@idsgrp.com> wrote:
>
>Hi,
>I am looking for a third party tool or something within SQL Server itself,
>where I can pull a stored XML String from a SQL Database (and or oracle),
>edit it, check for errors and then insert the modified string into the database.
>If any one has information on a tool that can do this or knows how SQL Server
>is capable of doing this, I would greatly appreciate it.
>Thank You
>Amy

Try using this procedure. Hope will help.

CREATE PROCEDURE DomDocumentFieldToObject

/*
This procedure creates an instance of a DomDocument.2.6 and returns an identifier
(integer) that is a handle to that object
Before calling this procedure you have to create a temporary table named
#T_XML ( doc_xml ntext )
and insert in that table a record with your XML
The procedure returns 0 if everything was OK and <> 0 if something was wrong

After calling this procedure you have a handle to a DomDocument.2.6. Then
you can call methods and properties of that object
using the extended stored procedures sp_OA.
Example

declare @oXMLDocument int,
@oDocumentElement int,
@Error int

create table #T_XML ( doc_xml ntext )
insert into #T_XML values ( <your XML string> ) or
insert into #T_XML select xml_field from another_table

execute @Error = DomDocumentFieldToObject @oXMLDocument output
if @Error <> 0 select 'Error'

' Get DocumentElement node
execute @Error = sp_OAGetProperty @oXMLDocument, 'documentElement', @oDocumentElement
output
' Get some attribute
execute @Error = sp_OAMethod @oDocumentElement, 'Attributes.GetNamedItem("att_name").nodeValue,
@AttValue output

...

YOU NEED:

- the dbo of the database where you are executing the code above must be
sa or you have to give execute permissions to the sp_OA extended
stored procedures to the user you use
- You have to install in the same machine where SQL Server is running MDAC
2.6 and MSXML2. If you use another version(s) you can change
the code above.

*/

@oXMLDoc int output

AS

declare @oStream int,
@hr int,
@VaLen int,
@VaPortion nvarchar(120),
@VaResto nvarchar(200),
@VaOffset int,
@VaNocount int,
@VaLoad int,
@VaChars int,
@VaChar char(1)

select @VaNocount = @@options & 512
set nocount on

select @VaChar = char(9)

execute @hr = sp_OACreate 'ADODB.Stream.2.6', @oStream output
if @hr <> 0 goto Fin
execute @hr = sp_OASetProperty @oStream, 'Type', 2 -- text stream
if @hr <> 0 goto Fin
execute @hr = sp_OAMethod @oStream, 'Open', null
if @hr <> 0 goto Fin

select @VaLen = datalength ( doc_xml ) from #T_XML -- length of your XML
select @VaOffset = 1
while @VaOffset <= @VaLen
begin
/* Get part of text */
select @VaPortion = substring ( doc_xml, @VaOffset, 100 ) from #T_XML
if len ( @VaPortion ) > 0
begin
/* Write data to stream */
execute @hr = sp_OAMethod @oStream, 'WriteText', null, @VaPortion, 0
if @hr <> 0 goto Fin
select @VaChars = 100 - len ( @VaPortion )
if @VaChars > 0
begin
select @VaResto = replicate ( @VaChar, @VaChars )
execute @hr = sp_OAMethod @oStream, 'WriteText', null, @VaResto, 0
if @hr <> 0 goto Fin
end
end
/* Next part */
select @VaOffset = @VaOffset + 100
end

/* Load Document from stream */
execute @hr = sp_OASetProperty @oStream, 'Position', 0
if @hr <> 0 goto Fin
execute @hr = sp_OACreate 'Msxml2.DOMDocument.2.6', @oXMLDoc output
if @hr <> 0 goto Fin
execute @hr = sp_OASetProperty @oXMLDoc, 'async', 0
if @hr <> 0 goto Fin
execute @hr = sp_OAMethod @oXMLDoc, 'Load', @VaLoad output, @oStream
if @hr <> 0 goto Fin
if @hr = 0 and @VaLoad = 0 select @hr = 2

Fin:
/* Destroy objects */
if @oStream is not null execute sp_OADestroy @oStream
if @VaNocount > 0 set nocount on else set nocount off
/* Delete info from temp table */
delete from #T_XML

/* Return code */
return ( @hr )