HTML using XML PATH

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » HTML using XML PATH

HTML using XML PATH

Rate Topic

Display Mode

Topic Options

Author

Message

parth83.rawal

Posted Wednesday, December 21, 2011 2:28 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 11:48 PM
Points: 66, Visits: 160

Hi,
I am generating the HTML using the XML PATH query, which is happening using the below mentioned code, the only issue is now i want to show the First column as hyperlink and navigate the user from that html page to some other page, the issue when it populates the html, it is considering the html as text so it is printing all the code in the first column:

please help me solve the issue


SET @HTML='<table id="table2" class="mytable">'+
					'<tr>
						<th>Title</th>
						<th>Description</th>
						<th>Reason</th>
						<th>Remarks</th>
						<th>Author</th>
						<th>Created</th>
						<th>Modified</th>
					</tr>' +
				CAST ( ( SELECT	td='<a href=''http://ABC:7777/XYZ/TYPE/EditForm.aspx?ID=0&UID='''+CAST(ACTIVITY_UID AS VARCHAR(36))+'''''>'+ACTIVITY_NAME+'</a>','', 
								td = ACTIVITY_DESC, '' ,
								td = ACTIVITY_REASON, '' , 
								td = ACTIVITY_REMARKS, '' , 
								td = CREATED_BY, '' , 
								td = CONVERT(VARCHAR,CREATED_DATE,6), '' , 
								td = CONVERT(VARCHAR,MOD_DATE,6), ''
								FROM E2E_ACTIVITY_MASTER
						FOR XML PATH( 'tr'), TYPE ) AS NVARCHAR( MAX) ) + 
				N'</table>';			
	SELECT @html

Post #1224958

okbangas

Posted Wednesday, December 21, 2011 4:51 AM

Right there with Babe

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:36 AM
Points: 721, Visits: 1,330

Could you please post DDL and sample data?

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL

Post #1225016

Lowell

Posted Wednesday, December 21, 2011 6:39 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 11,605, Visits: 27,647

the problem is the embedded html.

SELECT td='<a href=''http://ABC:7777/XYZ/TYPE/EditForm.aspx?ID=0&UID='''+CAST(ACTIVITY_UID AS VARCHAR(36))+'''''>'+ACTIVITY_NAME+'</a>','',

the FOR XML is going to htmlencode all that, turning them into amp lt ; a and stuff.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1225077

drew.allen

Posted Wednesday, December 21, 2011 6:39 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 1:20 PM
Points: 1,235, Visits: 5,389

The problem is this line here:

CAST ( ( SELECT td='<a href=''http://ABC:7777/XYZ/TYPE/EditForm.aspx?ID=0&UID='''+CAST(ACTIVITY_UID AS VARCHAR(36))+'''''>'+ACTIVITY_NAME+'</a>','',

Because you have given it an alias ("td") it treats the data as text instead of XML. Because it's treating it as text, it entitizes all of the special XML characters like "<", ">", and "&".

You should be using the aliases and/or subqueries to create the XML document structure. So your code could be re-written as

CAST ( ( SELECT [td/a/@href]='http://ABC:7777/XYZ/TYPE/EditForm.aspx?ID=0&UID='+CAST(ACTIVITY_UID AS VARCHAR(36)),
[td/a]=ACTIVITY_NAME,

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Post #1225079

parth83.rawal

Posted Wednesday, December 21, 2011 10:57 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 11:48 PM
Points: 66, Visits: 160

Hey It just Worked for me!!
Thanks a ton.....
small query how can i mark this as answer..!!! ;)

Post #1225555

Jeff Moden

Posted Wednesday, December 21, 2011 11:52 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893, Visits: 26,770

parth83.rawal (12/21/2011)

Hey It just Worked for me!!
Thanks a ton.....
small query how can i mark this as answer..!!! ;)

Can you post the code you finally ended up with, please? Thanks.

And they don't close posts on this forum and I'm very happy they don't. If someone comes up with an even better answer later on, they can still post it.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
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/

Post #1225578

parth83.rawal

Posted Sunday, October 07, 2012 1:11 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 11:48 PM
Points: 66, Visits: 160

Hey suddenly i am getting very wierd HTML using your solution.....

SET @HTML='<table id="table2" width="100%">'+
						'<tr class="MyHeader" bgcolor="#e2e2e2">'+
							'<th align="Left">Type</th>'+
							'<th align="Left">Gate</th>'+
							'<th align="Left">Task Name</th>'+
							'<th align="Left">Task Type</th>'+
							'<th align="Left">Project Name</th>'+
							'<th align="Left">Project Manager</th>'+
							'<th align="Left">Created Date</th>'+
						'</tr>'+
						CAST((         
							SELECT	td='ProjectName','',
									[td/a/@href]=Url,'',
									[td/a]=TaskName,'',
									td='Test','',
									td=Gate,'',
									td=TaskType,'',
									td=ProjectName,'',
									td=[Project Mngr],'',
									td=convert(char,ProjectStartDate,6)
									FROM    E2E_APPROVER_TASK_VIEW
						FOR XML RAW('tr'), ELEMENTS) AS NVARCHAR(MAX)) + N'</table>

and the result comes as

<td_x002F_a_x002F__x0040_href>http:///Project%?ProjUid=857567</td_x002F_a_x002F__x0040_href>

Post #1369502

mister.magoo

Posted Sunday, October 07, 2012 4:49 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 9:18 AM
Points: 1,287, Visits: 3,852

Try this...


SET @HTML='<table id="table2" width="100%">'+
	'<tr class="MyHeader" bgcolor="#e2e2e2">'+
		'<th align="Left">Type</th>'+
		'<th align="Left">Gate</th>'+
		'<th align="Left">Task Name</th>'+
		'<th align="Left">Task Type</th>'+
		'<th align="Left">Project Name</th>'+
		'<th align="Left">Project Manager</th>'+
		'<th align="Left">Created Date</th>'+
	'</tr>'+
	CAST((         
		SELECT	td='ProjectName','',
				[td/a/@href]=Url,
				[td/a]=TaskName,'',
				[td]='Test','',
				td=Gate,'',
				td=TaskType,'',
				td=ProjectName,'',
				td=[Project Mngr],'',
				td=convert(char,ProjectStartDate,6)
				FROM    E2E_APPROVER_TASK_VIEW
	FOR XML PATH('tr'), ELEMENTS
	) AS NVARCHAR(MAX)) + N'</table>'

I switched you over to FOR XML PATH and removed the empty string after the Url line, which was causing the tag to be split.

This produces this sample output (my dummy data)


<table id="table2" width="100%">
  <tr class="MyHeader" bgcolor="#e2e2e2">
    <th align="Left">Type</th>
    <th align="Left">Gate</th>
    <th align="Left">Task Name</th>
    <th align="Left">Task Type</th>
    <th align="Left">Project Name</th>
    <th align="Left">Project Manager</th>
    <th align="Left">Created Date</th>
  </tr>
  <tr>
    <td>ProjectName</td>
    <td>
      <a href="http://http://ABC:7777/XYZ/TYPE/EditForm.aspx?ID=0&UID=1">My Task</a>
    </td>
    <td>Test</td>
    <td>My Gate</td>
    <td>My TaskType</td>
    <td>My Project Name</td>
    <td>My Project Manager</td>
    <td>07 Oct 12                     </td>
  </tr>
</table>

Post #1369519

« Prev Topic | Next Topic »

Permissions