& - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-01-19 : 08:17:38

Hi,
How do you insert & into a table which has xml field?
i.e
create table tblTest (id int, names xml)
insert into tblTest(id, names) values (1, '<value>hello & you</value>')

This insert gives this error:
XML parsing: line 1, character 15, illegal name character

So I replaced & to &
Insert works but I would like to see Hello & you when I run a select query against it.

How is this possible to see & in the field please?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-19 : 08:27:11

[code]DECLARE @Sample TABLE
(
ID INT,
Names XML
)

INSERT @Sample
(
ID,
names
)
SELECT 1, REPLACE('<value>hello & you</value>', '&', '&') UNION ALL
SELECT 2, REPLACE('<value>hello & you</value>', '&', '#38;')

SELECT *
FROM @Sample[/code]See http://www.ascii.cl/htmlcodes.htm

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-01-19 : 08:35:21

Hi,
This is what I still get when running your sample.
Msg 9421, Level 16, State 1, Line 7
XML parsing: line 1, character 15, illegal name character

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-19 : 08:45:20

Display error. This forum replaces some characters. Use this code but remove the space in the replacement string (the space after & sign).
Try this

DECLARE	@Sample TABLE
	(
		ID INT,
		Names XML
	)

INSERT	@Sample
	(
		ID,
		names
	)
SELECT	1, REPLACE('<value>hello & you</value>', '&', '& amp;') UNION ALL
SELECT	2, REPLACE('<value>hello & you</value>', '&', '& #38;')

SELECT	*
FROM	@Sample

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-01-19 : 08:47:27

I still get the same Error when running this sample:
Thanks
SELECT *
FROM @Sample

DECLARE @Sample TABLE
(
ID INT,
Names XML
)

INSERT @Sample
(
ID,
names
)
SELECT 1, REPLACE('<value>hello & you</value>', '&', '&') UNION ALL
SELECT 2, REPLACE('<value>hello & you</value>', '&', '#38;')

SELECT *
FROM @Sample

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-19 : 08:49:23

Read my notes and try again.

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

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-19 : 08:55:21

quote:
Originally posted by arkiboys

I still get the same Error when running this sample:
Thanks
SELECT *
FROM @Sample

DECLARE @Sample TABLE
(
ID INT,
Names XML
)

INSERT @Sample
(
ID,
names
)
SELECT 1, REPLACE('<value>hello & you</value>', '&', '#38;')
SELECT *
FROM @Sample

try this here '&' symbole should be added before '#38;' forum does not support this.. I dont know why..

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-01-19 : 09:20:39

yes,
This is what I get when I run a select:
<value>hello & you</value>
--just noticed I can not type "&" here. please replace the above & with "&"
But I would like to see
<value>hello & you</value>

The sql I run:
DECLARE @Sample TABLE
(
ID INT,
Names XML
)

INSERT @Sample
(
ID,
names
)
--SELECT 1, REPLACE('<value>hello & you</value>', '&', '&') UNION ALL
SELECT 2, REPLACE('<value>hello & you</value>', '&', '#38;')

SELECT *
FROM @Sample

Note that the first select is commented out because it gives an error.
Thanks

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-19 : 09:39:57

Yes you are right.

This forum doesn't allow & symbol.

Karthik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources