preserving & when doing xml tasks

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)

preserving & when doing xml tasks

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-01-11 : 17:28:18

Greetings

When I do some xml concatenation all my & turn into &. How can I preserve what is in the data while using FOR XML or any other XML function.

Thanks

If you don't have the passion to help people, you have no passion

Sachin.Nand

2937 Posts

Posted - 2011-01-12 : 00:41:00

From where are you getting the XML data?

PBUH

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-01-12 : 16:15:41

I don't have an elegant solution for this problem; once when I had to generate a pipe-delimited string, I solved it the quick and dirty way (which I am not proud of), but here it is:

declare @test table (col1 varchar(100));
insert into @test values ('Johnson & Johnson');
insert into @test values ('A & P Markets');

select replace
((
   select 
      col1 as [text()],
      '|' as [text()]
   from
      @test
   order by
      col1
   for xml path('')
),'&_amp_;','&');

In the above, the &_amp_; should not really have the underscores. If I try to post it without those underscores, it doesn't post propertly. I guess sqlteam website and Snitz also use XML :-)

Of course, you will need to do replace for the other four or five special characters as well if your data could have any of those. Toldya, it's quick and dirty.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources