November 24, 2011 at 7:11 am
Hey, as some of you guys know im building a mailing system, and yesterday i learned about the wonderful for xml path operator.
I got it to work, but im having a minor format problem. The for xml path operator converts characters like < and > to & lt and & gt.
This screws up the html format of the email... Insted of interpreting the html <img>, it just writes it out as plain text.
Is there any way to convert them back to <>?
Here's a sample
SELECT '<img src="http://simple-image-reducer.org.ua/static/img/simple-image-reducer-big.png" />' for xml path('td')
Ty
November 24, 2011 at 8:58 am
Why are you using an XML in the select with for xml clause? The for xml clause is meant to be used when you take a tabular data and want to convert it to XML.
The XML that you are getting is because as far as SQL Server knows, it takes a string and converts it to XML. Strings in XML can’t contain characters that mark the beginning or end of an element. For example this is not a valid XML
<math>5<8</math>
The sign < is part of the string, but it confuses any XML reader application to think that an element was just opened. In order to make it a valid XML we need to modify it and use the escape char & l t ; (without the spaces. I've added the spaces because without it you don't see the escape charatar) instead of the < sign
The same thing happened to your XML. SQL Server treated your XML as string so it had to replace the signs < and > with there escape characters. If you don’t want that to happen, you have to let SQL Server know that it should treat it as XML and not as string. You can do it with the CAST function. Here is an example:
SELECT cast('<img src="http://simple-image-reducer.org.ua/static/img/simple-image-reducer-big.png" />' as XML) for xml path('td')
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 25, 2011 at 2:23 am
Adi Cohn-120898 (11/24/2011)
Why are you using an XML in the select with for xml clause?
I used it to demonstrate the problem. The actual code looks like this:
declare @clikesstring varchar(MAX)
SELECT DISTINCT [databases].dbo.users.id, +'<table>'+
STUFF((SELECT +'<img href="http://google.com/'+cast(companies.dbo.companies.id as varchar(50))+'_thumb.jpg>' AS
, companies.dbo.companies.company AS
FROM [databases].dbo.users INNER JOIN
companies.dbo.likes ON [databases].dbo.users.id = companies.dbo.likes.userid INNER JOIN
companies.dbo.companies ON companies.dbo.likes.companyid = companies.dbo.companies.id WHERE users.id = 1
ORDER BY users.id for xml path('tr')),1,1,'<')+'</table>' AS clikes
INTO #temp1
FROM [databases].dbo.users INNER JOIN
companies.dbo.likes ON [databases].dbo.users.id = companies.dbo.likes.userid INNER JOIN
companies.dbo.companies ON companies.dbo.likes.companyid = companies.dbo.companies.id WHERE users.id = 1
ORDER BY users.id
SELECT @clikesstring=clikes FROM #temp1
print REPLACE(REPLACE(@clikesstring, N'<', N'<'), N'>', N'>')
DROP TABLE #temp1
I used replace function but it doesn't feel as robust as your solution.
print REPLACE(REPLACE(@clikesstring, N'<', N'<'), N'>', N'>')
I tryed the cast on the varchar but got error: XML parsing: line 1, character 77, unexpected end of input
SELECT +cast('<img href="http://google.com/'+cast(companies.dbo.companies.id as varchar(50))+'_thumb.jpg>' as xml) AS
,
And while replacing <> to & l t ; without spaces, i still got wrong output... (& l t ; instead of the less sign)
Any ideas?
November 25, 2011 at 2:52 am
BEGIN TRAN
CREATE TABLE companies (id INT)
INSERT INTO companies
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
SELECT CAST('<img href="http://google.com/' + CAST(id AS VARCHAR(50)) + '_thumb.jpg" />' AS XML) AS
FROM companies
ROLLBACK
Returns
td
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<img href="http://google.com/1_thumb.jpg" />
<img href="http://google.com/2_thumb.jpg" />
<img href="http://google.com/3_thumb.jpg" />
BEGIN TRAN
CREATE TABLE companies (id INT)
INSERT INTO companies
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
DECLARE @SQL AS VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') +
'<img href="http://google.com/' + CAST(id AS VARCHAR(50)) + '_thumb.jpg" />'
FROM companies
SELECT @SQL = '<table>'+@SQL+'</table>'
SELECT CAST(@SQL AS XML)
ROLLBACK
Returns
<table>
<img href="http://google.com/1_thumb.jpg" />
<img href="http://google.com/2_thumb.jpg" />
<img href="http://google.com/3_thumb.jpg" />
</table>
November 25, 2011 at 3:27 am
Oh, ty so much, it actually worked after all. I realize now that i've missed a couple of html errors in my prototype version! Which XML noticed and threw up errors.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply