XML Returning &amp instead of &

Question

XML Returning &amp instead of &

BoilerJ

Default port

Points: 1428
More actions
May 4, 2011 at 11:25 am

#389438

I am fairly new dealing with XML but I know there are special characters that XML does not like. So with that being said here is my issue (I am shorting the code/columns so it is more readable)
1. I load an XML file into a standard table:
DECLARE @XML xml
select
@XML = convert(xml,x,2)
from
openrowset( bulk 'G:\Import\Jason.xml', SINGLE_BLOB) as T(x)
insert into dbo.DailyDeals
(
category
)
select X.product.query('advertisercategory').value ('.', 'VARCHAR(150)') as category
from @XML.nodes('catalog/product') AS X(product)
When I do the select in the table I get this for the category:
"Beauty, Spas, & Salons"
So loading the XML into the table maintains the "&"
2. Now when I want to reproduce the data in another formatted XML I do this:
SELECT TOP 1 category as category
FROM dbo.table
FOR XML PATH ('daily_deal'), ROOT('import_Request')
Here are the results:
<import_Request>
<daily_deal>
<category>Beauty, Spas, & Salons</category>
</daily_deal>
</import_Request>
As you can see the & was converted &amp. I need to keep it as &.
What "simple" thing am I doing wrong?
As ALWAYS, I appreciate the help!!!

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

BoilerJ Default port Points: 1428 More actions · Answer 1

wow, the "&amp" was not converted right in the post. I should have put "" around it. I had not clue it would interpret it that way when I did the post. Sorry for any confusion.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

can you reformat it to show exactly what you are seeing?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

BoilerJ Default port Points: 1428 More actions · Answer 3

Here is a repost with quotes so it will maintain the amp

Here are the results:

<import_Request>

<daily_deal>

<category>Beauty, Spas, "&" Salons</category>

</daily_deal>

</import_Request>

As you can see the & was converted to "&". I need to keep it as &.

BoilerJ Default port Points: 1428 More actions · Answer 4

I just tried and it still is converting it to & (which is what I WANT in my XML but that is NOT what is being returned.)

BoilerJ Default port Points: 1428 More actions · Answer 5

<import_Request>

<daily_deal>

<category>Beauty, Spas, amp Salons</category>

</daily_deal>

</import_Request>

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

JCSQL (5/4/2011)
Here is a repost with quotes so it will maintain the amp
Here are the results:
<import_Request>
<daily_deal>
<category>Beauty, Spas, "&" Salons</category>
</daily_deal>
</import_Request>
As you can see the & was converted to "&". I need to keep it as &.

Is it translating it to this (pseudo) in your xml?

&"amp"

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

BoilerJ Default port Points: 1428 More actions · Answer 7

OK so the last post I just hard coded amp (which is &). I need this to come back as & and not amp.

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

JCSQL (5/4/2011)
OK so the last post I just hard coded amp (which is &). I need this to come back as & and not amp.
Thanks

K - i was posting mine at the same time.

Let me try a few things

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

BoilerJ Default port Points: 1428 More actions · Answer 9

Yes it is translating it to that and that is not what is desired.

GSquared SSC Guru Points: 260824 More actions · Answer 10

XML and HTML both read the code for the same way, so the forum is doing that to you. Using the "Quote" button on your posts shows what you originally wrote in there.

The "TYPE" tag on the FOR XML function should give you what you need.

SELECT TOP 1 category as category

FROM dbo.

FOR XML PATH ('daily_deal'), ROOT('import_Request'), TYPE

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

BoilerJ Default port Points: 1428 More actions · Answer 11

BoilerJ

Default port

Points: 1428

May 5, 2011 at 8:51 am

#1320472

Any ideas on this....THANKS!

BoilerJ Default port Points: 1428 More actions · Answer 12

sorry did not see your reply on page 2...let me take a look.

BoilerJ Default port Points: 1428 More actions · Answer 13

even with the TYPE option specified, I am still getting the special character returned for &

GSquared SSC Guru Points: 260824 More actions · Answer 14

If you simply query the XML, that's exactly what you will get. That's how XML stores that type of data.

What end result are you trying to get?

If, for example, you do this:

IF OBJECT_ID(N'tempdb..#T') IS NOT NULL

DROP TABLE #T ;

CREATE TABLE #T

(ID INT IDENTITY

PRIMARY KEY,

X XML NOT NULL) ;

DECLARE @X XML ;

SELECT @X = (SELECT 'A&B' AS MyValue

FOR XML RAW,

TYPE) ;

INSERT INTO #T

(X)

VALUES (@X) ;

SELECT *,

X.value('(/row/@MyValue)[1]', 'varchar(10)')

FROM #T ;

You'll see the raw value of X, which will have the special characters in it, and you'll see the text conversion for it, which will just have the ampersand.

So, what is it you're trying to achieve?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon