?
Solved

Select with strange results in output

Posted on 2012-08-22
6
Medium Priority
?
477 Views
Last Modified: 2012-08-23
My following query works and returns what I need.

But you can see that I am having to do a replace in the final select because FOR XML Path('') part has &lt;  and &gt; instead of < and >

Any better way of handling this?
My code just seems like I'm cleaning things up instead of coding it correctly to start with.

;WITH	cte (email, querystring) AS
(
	SELECT 
		Replace(email,'''',''), 
		querystring = LEFT(o.list, LEN(o.list)-1) 
	FROM #temp c 
CROSS APPLY 
( 
    SELECT 
       CONVERT(VARCHAR(MAX), querystring) + '<br />' AS [text()] 
    FROM 
        #temp s 
    WHERE 
        s.email = c.email 
    ORDER BY 
        email
    FOR XML PATH('') 
    ) o (list))
    
SELECT	email, 
                Replace(Replace(Replace(querystring,'&lt;','<'),'&gt;','>'),'&gt','>') querystring,
                2 AS CampaignTypeID,
                NULL lead_id
 FROM     cte WHERE [dbo].[udf_Txt_IsEmail](email) = 1 GROUP BY email, querystring 
ORDER BY 
    email

Open in new window

0
Comment
Question by:lrbrister
  • 3
  • 2
6 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38320864
Yes, the FOR XML 'cleans' the contents to form valid XML, as < and > have meaning in XML.
I am not sure that this is better ... but you could reduce the Replace() functions by
a) concatenating in something other that could not be in the email address (and that is pretty much almost every character) ... I would suggest char(10) maybe
    i.e. CONVERT(VARCHAR(MAX), querystring) + char(10)
b) use one replace()
    i.e. Replace(querystring,char(10),'<br/>')
0
 
LVL 4

Expert Comment

by:TI2Heaven
ID: 38321121
"CONVERT(VARCHAR(MAX), querystring)" is the quid. It seems you have a well-formed XML at source but you then needs to treat it as Text. During conversion to text you are losing all the xml symbols and replacing as simple characters. After that you ask SQL that you want a XML, and SQL is giving you a fragment of XML, with just one node of type text (no elements, nor attributes).
Insted of + "<br/> (concatenating strings) try to add a child element to your xml.
0
 

Author Comment

by:lrbrister
ID: 38321229
T12 heaven

How would I add the child?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Expert Comment

by:TI2Heaven
ID: 38321356
I cant give you the exact soluction becouse I forgot most of the syntax and becouse  I dont know querystrins data.
But instead of:
SELECT 
       CONVERT(VARCHAR(MAX), querystring) + '<br />' AS [text()] 
    FROM 
        #temp s 
    WHERE 
        s.email = c.email 
    ORDER BY 
        email
    FOR XML PATH('') 
    ) o (list))

Open in new window

try something like:
SELECT 
       querystring AS [/rootelement],
  cast('<br />' AS xmldocument) AS  [/rootelement] 
    FROM 
        #temp s 
    WHERE 
        s.email = c.email 
    ORDER BY 
        email
    FOR XML PATH('') 
    ) o (list))

Open in new window

0
 
LVL 4

Accepted Solution

by:
TI2Heaven earned 2000 total points
ID: 38324119
Just in case you might not find the solution by your own. I am posting you the following example:
SELECT 
       CONVERT(VARCHAR(MAX), 'HELLO>WORLD') AS "text()",
CAST('<br/>' AS xml) AS "node()" 
FOR XML PATH('') 

Open in new window

You will see that ">" is escaped on hello>world but not in br element.
(The quid was "[text()]")
For your query the correct replacement is:
SELECT 
       CONVERT(VARCHAR(MAX), querystring) AS "text()" ,
CAST('<br/>' AS xml) AS "node()"
    FROM 
        #temp s 
    WHERE 
        s.email = c.email 
    ORDER BY 
        email
    FOR XML PATH('') 

Open in new window

I would like to warn you that writing html using sql language may enable your app to be vulnerable to XSS. I don’t see vulnerabilities in the final solution of this particular example.
0
 

Author Closing Comment

by:lrbrister
ID: 38325100
Thanks for sticking with me.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question