How to use TEXT var. in here - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

How to use TEXT var. in here

Author

Topic

markgoldin
Starting Member

2 Posts

Posted - 2002-08-19 : 22:52:55

I have this code. Is it possible to use @xmlstring
parameter as TEXT? I cannot get it because I use dynamic SQL and cannot combine strings with TEXT. Is there any way to do that?
Thanks

CREATE PROCEDURE usp_xmlupdate (@cursorname varchar(20),
@xmlstring varchar(8000), @tablename varchar(20))

AS
DECLARE @idoc int
DECLARE @fieldname varchar(40)
DECLARE @lastfieldname varchar(40)
DECLARE @ssql varchar(8000)
DECLARE @withstr varchar(100)
DECLARE @updatestr varchar(200)
DECLARE @columnstr varchar(100)
SET @updatestr = 'UPDATE ' + @tablename + ' SET '
SET @withstr = 'id INT, '
SET @columnstr = ''

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlstring

DECLARE FieldsCrsr CURSOR LOCAL SCROLL FOR
select distinct localname
from OpenXML(@idoc, @cursorname, 1)
where nodetype = 2 AND localname <> 'id' -- no updates
for ID field

OPEN FieldsCrsr
FETCH LAST FROM FieldsCrsr INTO @lastfieldname

FETCH FIRST FROM FieldsCrsr INTO @fieldname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @withstr = @withstr + @fieldname + ' varchar
(40)'
SET @updatestr = @updatestr + @tablename + '.' +
@fieldname + ' = #tempdata.' + @fieldname
SET @columnstr = @columnstr + '#tempdata.' +
@fieldname
IF @fieldname <> @lastfieldname
BEGIN
SET @withstr = @withstr + ', '
SET @updatestr = @updatestr + ', '
SET @columnstr = @columnstr + ', '
END
FETCH NEXT FROM FieldsCrsr INTO @fieldname
END

set @ssql = '
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, ''' +
@xmlstring + '''
CREATE TABLE #tempdata (' + @withstr + ')' + CHAR(13) +
' insert into #tempdata
select *
from OpenXML(@idoc, ''' + @cursorname + ''', 1)
with (' + @withstr + ') ' + CHAR(13) +
--updates
@updatestr + CHAR(13) + ' FROM #tempdata INNER JOIN ' +
@tablename +
' ON #tempdata.id = ' + @tablename + '.id' + CHAR(13) +

' INSERT INTO ' + @tablename + ' SELECT ' + @columnstr +
CHAR(13) +
' FROM #tempdata WHERE id NOT IN' + CHAR(13) +
' (SELECT id FROM ' + @tablename + ')' + CHAR(13) +

' DELETE FROM ' + @tablename + CHAR(13) +
' WHERE deleted = 1'

--select @ssql
exec(@ssql)
exec sp_xml_removedocument @idoc
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-20 : 08:51:15

There is a way to do this, it is documented in Ken Henderson's

The Guru's Guide to SQL Server Stored Procedures, XML, and HTML

You can purchase it through the SQL Team Bookstore.

markgoldin
Starting Member

2 Posts

Posted - 2002-08-20 : 09:12:28

I've placed an order for that book. Hopefully I will find my answer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources