CODEWITHSUNDEEP
sql-serverxmlpath
Zorge17
Zorge17

Reputation: 37

How can I avoid character encoding when using "FOR XML PATH" without REPLACE function

Using FOR XML PATH encodes my HTML making hyperlinks useless. I see that others are using the value method to prevent encoding. But this strips out the table structure I need. I would like to avoid using the REPLACE function SQL Server 2016

I expect this result:

        <td>
          <a href="https://backend.com/User/UserDetails.aspx?UserID=1">1</a>
        </td>
        <td>Ann</td>
        <td>GB</td>
      </tr>
      <tr>
        <td>
          <a href="https://backend.com/User/UserDetails.aspx?UserID=2">2</a>
        </td>
        <td>Jason</td>
        <td>DE</td>
      </tr>
      <tr>
        <td>
          <a href="https://backend.com/User/UserDetails.aspx?UserID=3">3</a>
        </td>
        <td>Mary</td>
        <td>PT</td>
      </tr> 






CREATE TABLE #History (UserID int, UserName Varchar(20), CountryName Varchar(2))
Insert into #History
Values(1,'Ann', 'GB'),
       (2,'Jason', 'DE'),
       (3,'Mary', 'PT')

       select * from #History

DECLARE @Values             NVARCHAR(MAX)
SELECT @Values = CAST((SELECT '<a href="https://backend.com/User/UserDetails.aspx?UserID=' 
                ++ CAST(UserId AS varchar(150)) ++ '">'
                ++ CAST(UserID AS varchar(150)) ++ '</a>'  AS 'td','',UserName AS 'td','',CountryName AS 'td',''
                FROM  #History
                FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SELECT @Values

Upvotes: 0

Views: 261

Answers (1)

Thom A
Thom A

Reputation: 95544

You need to nest your XML:

SELECT @Values = CAST((SELECT (SELECT CONCAT('https://backend.com/User/UserDetails.aspx?UserID',UserId) AS [@href] FOR XML PATH('a'),TYPE) AS td,'',
                              UserName AS td,'',
                              CountryName AS td,''
                       FROM #History
                       FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX));

This results in:

<tr>
  <td>
    <a href="https://backend.com/User/UserDetails.aspx?UserID1" />
  </td>
  <td>Ann</td>
  <td>GB</td>
</tr>
<tr>
  <td>
    <a href="https://backend.com/User/UserDetails.aspx?UserID2" />
  </td>
  <td>Jason</td>
  <td>DE</td>
</tr>
<tr>
  <td>
    <a href="https://backend.com/User/UserDetails.aspx?UserID3" />
  </td>
  <td>Mary</td>
  <td>PT</td>
</tr>

Based on the new expected results:

SELECT (SELECT CONCAT('https://backend.com/User/UserDetails.aspx?UserID', UserId) AS [a/@href],
               UserId AS [a]
       FOR XML PATH(''), TYPE) AS td,
       '',
       UserName AS td,
       '',
       CountryName AS td,
       ''
FROM #History
FOR XML PATH('tr'), ELEMENTS;
--or
SELECT CONCAT('https://backend.com/User/UserDetails.aspx?UserID', UserId) AS [td/a/@href],
       UserId AS [td/a],'',
       UserName AS td,
       '',
       CountryName AS td,
       ''
FROM #History
FOR XML PATH('tr'), ELEMENTS;

Which gives:

<tr>
  <td>
    <a href="https://backend.com/User/UserDetails.aspx?UserID1">1</a>
  </td>
  <td>Ann</td>
  <td>GB</td>
</tr>
<tr>
  <td>
    <a href="https://backend.com/User/UserDetails.aspx?UserID2">2</a>
  </td>
  <td>Jason</td>
  <td>DE</td>
</tr>
<tr>
  <td>
    <a href="https://backend.com/User/UserDetails.aspx?UserID3">3</a>
  </td>
  <td>Mary</td>
  <td>PT</td>
</tr>

Upvotes: 1

Related Questions