stop html encode select ... for xml path

select '<p> hi there <b>joe!</b></p>' as 'welcome'
for xml path('greetings')

returns: <greetings><welcome>&lt;p&gt; hi there &lt;b&gt;joe!&lt;/b&gt;&lt;/p&gt;</welcome></greetings>

I want: <greetings><welcome><p> hi there <b>joe!</b></p></welcome></greetings>

Are there any options?
LVL 3
leos_Asked:
Who is Participating?
 
Anthony PerkinsCommented:
The best you can achieve with that approach is:

select HTMLColumn [welcome/comment()]
from SomeTable
for xml path('greeting')
0
 
Anthony PerkinsCommented:
select      '' welcome,
            ' hi there ' [welcome/p],
            'joe!' [welcome/p/b]
for xml path('greetings')
0
 
leos_Author Commented:
hehe acperkins ...  not quite what I'm after, unless you're telling me it can't be done.

Here is a clarification.

select HTMLColumn as Welcome
from SomeTable
for xml path('greeting')

the entries in HTMLColumn are:

1.  <p>hi there <b>joe!</b></p>
2.  <p>Dear bob,</p>
3.  <p>You Rock! <i>we want you back</i>.  Call us</p>
4.  ??? who knows

upped point value to 500
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
leos_Author Commented:
oh, and give the above table, I don't want the results htmlencoded.
0
 
leos_Author Commented:
Thanks Anthony,

I think I can find a way to remove the comment markers after the fact.

Is the comment() function documented somewhere?

Jens
0
 
Anthony PerkinsCommented:
>>Is the comment() function documented somewhere? <<
It is under "Using PATH Mode" in BOL in the "Columns with the name of an XPath Node Test" section
http://msdn.microsoft.com/en-us/library/ms189885(SQL.90).aspx
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.