help with "FOR XML RAW, ELEMENTS" clause - SQL 2k5 2

[ddiamond]

I'm trying to create and xml representation of many tables (1 to many relationships). I've found the easiest way to do this is using the "for xml raw, elements" clause. All of the more advanced techniques involve creating a schema which I would like to avoid if possible. The only problem I ran into using "for xml raw, elements" is that it is encoding all punctuation marks in my text.

For example I have a varchar field called ProductCode which equals "E&O". I expected the resulting xml element to be

<ProductCode>E&O</ProductCode>

Instead I got

<ProductCode>E&O</ProductCode>

Is there any setting that will prevent this translation?

 

[bborissov]

What is the difference?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ddiamond]

Sorry, that was a cut and paste error.

Instead I got

<ProductCode>E&O</ProductCode>

 

[ddiamond]

Ok, I will try it outside of the code window.

<ProductCode>E&amp;O</ProductCode>

 

[markros]

I believe I saw a blog of Brad Shulz dealing with the exact same problem.

I'll try to find it now.

 

[markros]

I believe this is the blog I meant

http://bradsruminations.blogspot.com/2009/11/xml-paths-of-glory.html

You may ask additional questions in the comments to this blog.

 

[bradschulz]

Thanks for the link to the blog, markros.

The one where I talk about eliminating the XML encoding is this one, though:

http://bradsruminations.blogspot.com/2009/10/making-list-and-checking-it-twice.html

However, the blog entry talks about eliminating the encoding for the purposes of creating a VARCHAR or NVARCHAR column or variable. If ddiamond wants a true XML column or variable target, then you CAN'T eliminate the encoding... that's well-formed XML.

(BTW, Hello to Borislav... long time no talk).

--Brad

 

[markros]

Hi Brad,

Glad you came here! Yes, I got confused in your blogs, I read a few of them and loved them all.

Just curious how did you come, though - I don't remember seeing you here before...

 

[bradschulz]

I have an app in the blog that tells me where people come from. I saw that people got to the "XML Paths of Glory" blog post from this thread in tek-tips, so I decided to come here to see what pointed them in that direction.

(Thanks for the kind words, BTW).

 

[ddiamond]

Thanks for the tips. Brad's blog did not solve the encoding I posted about, but I did learn a lot from it. It showed me easier ways to generate nested XML.

However, the blog entry talks about eliminating the encoding for the purposes of creating a VARCHAR or NVARCHAR column or variable. If ddiamond wants a true XML column or variable target, then you CAN'T eliminate the encoding... that's well-formed XML.

For the purpose of my application, I don't have a problem storing the XML as a varchar, but I do need to preserve the XML tags. The technique described in the blog eliminates the tags as well.

 

[bradschulz]

As far as I can tell, the only characters that are encoded (unless you perhaps have to deal with obscure NVARCHAR characters) are ampersand, less-than, and greater-than, as you can see when you look at the output of this:

with Chars as
(
select [Char]=char(Number)
from master..spt_values
where Type='P' and Number between 33 and 126
)
select * from Chars for xml path('Chars')

So, since you're creating a VARCHAR result anyway, you can just use REPLACE to take care of those encodings... something like this:

select replace(replace(replace(
(select * from MyDataSource for xml path('Data'))
,'&','&')
,'<','<')
,'>','>')

Hope that helps...

--Brad

 

[bradschulz]

Sorry... I just saw my message...

Those REPLACEs should read:

, '& amp ;','&')
, '& lt ;','<')
, '& gt ;','>')

--Brad