Insert value into NText column

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 2005 Forums

Transact-SQL (2005)

Insert value into NText column

Author

Topic

ljp099
Yak Posting Veteran

79 Posts

Posted - 2009-08-25 : 17:08:20

I have an NText column which stores blocks of xml data. I need to insert an xml node, <PrimaryResearchPurpose></PrimaryResearchPurpose>, into each record of data in this table. I know that each item has an end tag:

</DocInfo>

So what I was thinking I could do was insert this node before the </DocInfo> end tag. If anyone can help me figure out the t-sql statment to do this, I would appreciate it.

Find </DocInfo> in a string block. Then insert immediately before this item the string <PrimaryResearchPurpose></PrimaryResearchPurpose>

-----------------------------------------------------------------

When done properly each record will look like this:

<PrimaryResearchPurpose></PrimaryResearchPurpose>
</DocInfo>

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-25 : 17:23:15

Maybe this:

declare @yak table(test ntext)
insert @yak
select '<DocInfo></DocInfo>'

select * from @yak

update @yak
set test=replace(convert(nvarchar(max),test),'</DocInfo>', '<PrimaryResearchPurpose></PrimaryResearchPurpose></DocInfo>')

select * from @yak

No, you're never too old to Yak'n'Roll if you're too young to die.

ljp099
Yak Posting Veteran

79 Posts

Posted - 2009-08-25 : 17:28:24

Thanks. I think this almost does it. I just noticed that when I view the table data by opening up the table, I see that the nodes look to be separated by squares - I guess there is some encoding that identifies these as line breaks. Do you know if this matters, and if so, what I used to encode the line breaks?

set test=replace(convert(nvarchar(max),test),'</DocInfo>', '<PrimaryResearchPurpose></PrimaryResearchPurpose>IDENTIFY THAT THIS IS A NEW LINE??</DocInfo>')

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-25 : 17:33:22

In XML you need line breaks only for your eyes if you want to read the structure. They are not important for processes that work on XML.

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-25 : 17:55:28

Is there a special reason the column is not of XML datatype?
All these things are so much easier with XML datatype.
You can even have index on the xml column!

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources