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

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



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

CodePudding user response：

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>

CodePudding user response：

This seems to build the desired xml:

declare @values nvarchar(max);
set @values = (
    select
        (select 'https://backend.com/user/userdetails.aspx?userid='   cast(userid as varchar) as [a/@href], userid as [a/text()] for xml path('td'), type),
        (select username as [text()] for xml path('td'), type),
        (select countryname as [text()] for xml path('td'), type)
    from (values
        (1, '<Ann>', 'GB'),
        (2, 'Jason', 'DE'),
        (3, 'Mary', 'PT')
    ) as history(userid, username, countryname)
    for xml path('tr'), root('table')
);
select @values;

Result:

<table>
    <tr>
        <td>
            <a href="https://backend.com/user/userdetails.aspx?userid=1">1</a>
        </td>
        <td>&lt;Ann&gt;</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>
</table>