& when assigning to XML variable

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)

& when assigning to XML variable

Author

Topic

eltonwheelock
Starting Member

5 Posts

Posted - 2010-10-20 : 17:25:51

Got a variable declared:

DECLARE @VARIABLE XML

Building an XML string:

...<field1>' + ISNULL(@VARIABLE,'') + '</field1>...

@VARIABLE has "&" in the value, like "Cookies & Milk"

When I try to make the assignment, SQL barks:

Msg 9421, Level 16, State 1, Line 1065
XML parsing: line 2, character 33, illegal name character

I don't want to have to massage every variable when building my XML string, is there a built in SQL function that will do this automatically (i.e, change the & to "& a m p ;") ? SQL massages its own output when using "FOR XML RAW"...certainly there's something that works when putting SQL in.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-20 : 18:23:45

Look at the other FOR XML options like AUTO and PATH, you don't need to concatenate the elements as strings.

eltonwheelock
Starting Member

5 Posts

Posted - 2010-10-21 : 09:01:53

Yeah, but I'm having to build XML to put in an XML field -- I'm putting data in. XML AUTO, etc... is for getting data out of a table.

Sachin.Nand

2937 Posts

Posted - 2010-10-21 : 09:15:51

But why are you doing it in SQL? I prefer to have the formatted XML from the Business layer.

PBUH

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-21 : 14:25:21

could you show us the table(s) with sampel data , this is easy stuff I believe

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-21 : 15:59:29

Proof of concept:

DECLARE @VARIABLE XML
;with s(s) as (select 'Cookies & Milk')
select @VARIABLE=(select s as field1 from s for xml path(''), type)
select @VARIABLE

eltonwheelock
Starting Member

5 Posts

Posted - 2010-10-22 : 09:44:24

ha! I love it.

quote:

Originally posted by robvolk

Proof of concept:

DECLARE @VARIABLE XML
;with s(s) as (select 'Cookies & Milk')
select @VARIABLE=(select s as field1 from s for xml path(''), type)
select @VARIABLE

eltonwheelock
Starting Member

5 Posts

Posted - 2010-10-22 : 09:47:11

We ended up writing a UDF that massaged the text, replacing the escape characters with their character references.

So it ends up being
<code>
...<field1>' + udfMassageXML('Cookies & Milk') + '</field1>...
</code>

Just means we have to use the UDF whenever we are building XML strings, not optimal but doable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources