HTML using XML PATH

• parth83.rawal

  SSCommitted

  Points: 1858

  More actions

  December 21, 2011 at 2:28 am

  #262139

  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 ( ( SELECTtd='<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

• okbangas

  SSChampion

  Points: 11773

  More actions

  December 21, 2011 at 4:51 am

  #1424134

  Could you please post DDL and sample data?

  ---

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

  Concatenating Row Values in Transact-SQL[/url]

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 21, 2011 at 6:39 am

  #1424188

  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

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  December 21, 2011 at 6:39 am

  #1424190

  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

  ='http://ABC:7777/XYZ/TYPE/EditForm.aspx?ID=0&UID='+CAST(ACTIVITY_UID AS VARCHAR(36)),

  =ACTIVITY_NAME,

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• parth83.rawal

  SSCommitted

  Points: 1858

  More actions

  December 21, 2011 at 10:57 pm

  #1424575

  Hey It just Worked for me!!

  Thanks a ton.....

  small query how can i mark this as answer..!!! 😉

• Jeff Moden

  SSC Guru

  Points: 1004313

  More actions

  December 21, 2011 at 11:52 pm

  #1424595

  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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• parth83.rawal

  SSCommitted

  Points: 1858

  More actions

  October 7, 2012 at 1:11 am

  #1546472

  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((

  SELECTtd='ProjectName','',

  =Url,'',

  =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&gt;

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  October 7, 2012 at 4:49 am

  #1546485

  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((

  SELECTtd='ProjectName','',

  =Url,

  =TaskName,'',

  ='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>

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]