How to eliminate XML Escape Characters in "FOR XML PATH"?

Question

0.00/5 (No votes)

See more:

Hi Friends,

I need to eliminate XML Escape Characters (" ",' '< <> >& &)
after using "FOR XML PATH", With out using "REPLACE" Command I need to solve this.
Refer my sample data and pls give the solution.

SQL

-- Tables Creation
IF OBJECT_ID('TempDB..#Test') IS NOT NULL DROP TABLE #Test
CREATE TABLE #Test (ID INT, Name VARCHAR(30))
-- Sample Data
INSERT INTO #Test(ID, Name) VALUES(1,'Test'),(2,'&Test'), (3,'<test>')
-- Actual Data
SELECT ID, Name FROM #Test
-- Reguired Output
'Test,&Test,<test>'
SELECT STUFF((SELECT ','+Name AS [text()] FROM #Test FOR XML PATH('')),1,1,'')
-- Current Output
'Test,&Test,<test>'

Regards,
GVPrabu

Posted 17-Feb-13 21:41pm

gvprabu

Add a Solution

Comments

Karthik Harve 18-Feb-13 4:03am

What have you tried so far? Where you stuck?

gvprabu 18-Feb-13 4:32am

Hi Karthik,

I am using REPLACE Command for solve this. But different solution Because I need to write 5 REPLACE Command for this.

Regards,
GVPrabu

2 solutions

Solution 1

SQL

Select STUFF((SELECT ',' + cast(id as varchar) FROM x with (nolock)
 FOR XML PATH('')),1,1, '') as c

Posted 17-Feb-13 22:08pm

Hrushikesh_phapale

Updated 17-Feb-13 22:09pm

v2

Comments

gvprabu 18-Feb-13 4:31am

HI Friend,

Check your Query,

I need to Remove All XML Escape Character after Join My Character Column.

As per your query it will convert INT Values to Character. Any how tnks

Regards,
GVPrabu

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Accepted Answer · 2013-02-17T22:34:00

Solution 3

Hi All,

At last I find the solution for this....

SQL

SELECT STUFF((SELECT ','+Name AS [text()] FROM #Test FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'') AS 'NameList'

Check this :-)

Regards,
GVPrabu

Posted 17-Feb-13 22:34pm

gvprabu

Updated 17-Feb-13 23:13pm

v2

Comments

Karthik Harve 18-Feb-13 4:40am

my 5+.!!

gvprabu 18-Feb-13 5:13am

Hi Karthik,

Tnks