XML Conversion Issue - 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

General SQL Server Forums

New to SQL Server Programming

XML Conversion Issue

Author

Topic

raky
Aged Yak Warrior

767 Posts

Posted - 2009-08-11 : 09:46:49

[code]
Hi,

I have a column Comments in my table...I loaded the table from an xml file.
The sample value in that column is

A remake of the 1954 hit Three Coins in the Fountain, THE PLEASURE SEEKERS updates the story of three women searching for love for the swinging 1960s.

Here

I want to find the occurence of < and its corresponding > then i want to make it as empty starting from < to >.

expected o/p:

A remake of the 1954 hit Three Coins in the Fountain, THE PLEASURE SEEKERS updates the story of three women searching for love for the swinging 1960s.
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-11 : 14:17:42

UPDATE Table1
SET Col1 = REPLACE(REPLACE(Col1, '', ''), '', '')
WHERE Col1 LIKE '%I>%

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

raky
Aged Yak Warrior

767 Posts

Posted - 2009-08-12 : 00:24:23

Hi Peso...Thanx for the reply but the requirement is in present example we are having and but
we cant say exactly it is 'I' in some rows we can have other letters....So please assist..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-12 : 02:38:57

So, how many other letters do you have?

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

raky
Aged Yak Warrior

767 Posts

Posted - 2009-08-12 : 05:39:21

it is non-deterministic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-12 : 05:52:41

I guess you always can do the quick-n-dirty...

DECLARE	@Data XML

SET	@Data = '<root>
			A remake of the 1954 hit <i>Three Coins in the Fountain</i>, THE PLEASURE SEEKERS <peso>updates</peso> the story of <x>three women</x> searching for love for the swinging 1960s.
		</root>'

SELECT	n.value('.', 'VARCHAR(MAX)')
FROM	@Data.nodes('root') AS r(n)

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