Find and Replace string manipulation Help

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)

Find and Replace string manipulation Help

Author

Topic

2008nkm
Starting Member

3 Posts

Posted - 2008-03-14 : 17:49:39

Please help me with the sql script to manipulate the string data:

I need to add <Text> at the beginning and end of the string.
I also need to add <option> before the first and after last occurence of the <Option> string. The original string

<StockNumber>502</StockNumber>
<OptionKey>113</OptionKey>
<OptionKey>151</OptionKey>
<Warranty>1</Warranty>

should look like

<Text>
<StockNumber>502</StockNumber>
<Option>
<OptionKey>113</OptionKey>
<OptionKey>151</OptionKey>
<Option>
<Warranty>1</Warranty>
<Text>

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 17:52:12

Use the FOR XML ROOT('Text') option.

E 12°55'05.25"
N 56°04'39.16"

2008nkm
Starting Member

3 Posts

Posted - 2008-03-14 : 17:58:40

I have corrected my earlier posting. Please read the following and ignore the original one. Sorry for the inconvenience.

Please help me with the sql script to manipulate the string data:

I need to add <Text> at the beginning and </Text> at the end of the string.
I also need to add <Option> before the first and </Option> after the last occurence of the <OptionKey> string. The original string

<StockNumber>502</StockNumber>
<OptionKey>113</OptionKey>
<OptionKey>151</OptionKey>
<Warranty>1</Warranty>

should look like

<Text>
<StockNumber>502</StockNumber>
<Option>
<OptionKey>113</OptionKey>
<OptionKey>151</OptionKey>
</Option>
<Warranty>1</Warranty>
</Text>

after manipulation/update

Thanks in advance for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 18:02:04

How is the data stored today?

Please post back similar to this

DECLARE @Sample TABLE (ddl here)

insert @sample select (sample data here)

E 12°55'05.25"
N 56°04'39.16"

2008nkm
Starting Member

3 Posts

Posted - 2008-03-17 : 08:53:46

The source data is in xml format without tags; I think to modify the string by string functions after converting the xml data into nvarchar as that approach seems to be easy.

After converting and adding the tags I can insert the data into the xml datatype column(which will have all the required tags).

thanks

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-17 : 09:09:48

The XML format you posted have tags with the same name at same level.It will cause an error.You have 2 tags with the name "Optionkey" at the same level.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-17 : 15:09:51

You can add a "virtual" level by adding "option\" before the optionkey name.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources