November 28, 2008 at 10:49 am
Him
Im using the following code to "replace" a value in a textstring that contains XML...
It completes but replaces nothign as the final select shows... Ive got to get this workign on Monday so any help happily recieved !
thanks si
--create temptable subset
SELECT * into #user_message
FROM user_message
where messageXML like '%user%'
go
alter table #user_message
add rowid int identity (1,1)
go
DECLARE @ptrmessageXML varbinary(16),
@rowid int,
@otxt varchar(40),
@ntxt varchar(40),
@txtlen int,
@StartingPosition int
SET @rowid = 1
SET @otxt = 'user'
SET @ntxt = ' '
SET @txtlen = LEN(@otxt )
WHILE @rowid <= 2
BEGIN
SET @StartingPosition = 2
SELECT @ptrmessageXML = TEXTPTR(messageXML) FROM #user_message WHERE rowid = @rowid
WHILE @StartingPosition > 0
BEGIN
SELECT @StartingPosition = CHARINDEX(@otxt , messageXML) - 1
FROM #user_message
where rowid = @rowid
IF @StartingPosition > 0
begin
UPDATETEXT #user_message.messageXML @ptrmessageXML @StartingPosition @txtlen @ntxt
end
END
SET @rowid = @rowid + 1
END
SELECT *
FROM #user_message
where messageXML like '%user%'
drop table #user_message
go
November 28, 2008 at 12:37 pm
Charindex does not work with text.
It implicitly converts it to CHAR(4000).
If your string to be replaced is after 4000th character it's never gonna find it.
And SQL is a VERY bad choice of a text editor. I's a language of structured queries, not editing long strings.
Even VBS is more powerful for this purpose.
As for XML it does not belong in relational database. Pass it to web-service it was designed for, let it parse XML to data and store what you need in database.
Then you can easily manipulate the data and, when needed, pass it to the same web-service in order to build outbound XML.
_____________
Code for TallyGenerator
November 28, 2008 at 9:52 pm
XQuery is the choice for this, but it's limited in 2000.
This is really something you want to do in another language, not T-SQL.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply