Inserting a start- and end tag into an xml string

Question

Post reply

Inserting a start- and end tag into an xml string

hennie7863

SSCommitted

Points: 1653
More actions
August 5, 2009 at 7:11 am

#137564

Hi
I've a xml variable @xmldoc with this
And i want to insert a tag as in the following example:
Tried some some things with the xquery query but no luck so far. Any help appreciated!
Gr,
Hennie

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Old school string manipulation?

declare @xml xml

declare @string varchar(max)

set @xml =

'

set @string = cast(@xml as varchar(max))

set @string = replace(@string,'','')

set @xml = @string

select @xml

OK... Editing here because this is weird. I am omitting the opening "less than" brackets because they are apparently confusing the editor.

When I copy this over, it has the '/data>' tag being replaced by '/variable> /data>'. But when I post it, it shows as /variable> being replaced with '/data> /data>'.

The last replace line should read like this, only with the opening brackets reinserted.

set @string = replace(@string,'/data>','/variable>/data>')

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

hennie7863 SSCommitted Points: 1653 More actions · Answer 2

HI Bob,

Thx for your reply. I did that manipulation too. But the problem is that maximum character length of varchar(max) is 65K and the string is cut off. So i assume that this is the maximum length of MAX (?). The xml string is not cut off when i put the data right into a XML datatype. That's the reason why i started looking at a solution with the XML datatype.

I'm currently looking for adapting a FLOWR expression. I'm trying something like this:

SELECT @xmldata.query('for $item in * { $item }');

the data tag can be removed in the query from the database (FOR XML AUTO, ELEMENTS, ROOT ('data')) so that i can add the data tag after the FLOWR expression.

SELECT @xmldata.query('for $item in /data/row return { $item } ');

But i can't get it working, yet.

Hennie