在沒有REPLACE函式的情況下使用“FORXMLPATH”時如何避免字符編碼-有解無憂

使用 FOR XML PATH 對我的 HTML 進行編碼，使超鏈接無用。我看到其他人正在使用 value 方法來防止編碼。但這剝離了我需要的表結構。我想避免使用 SQL Server 2016 的 REPLACE 函式

我期待這個結果：

        <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

uj5u.com熱心網友回復：

您需要嵌套您的 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));

這導致：

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

基于新的預期結果：

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;

這使：

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/373601.html

標籤：sql-server xml 小路

上一篇：我想用python替換xml中子子項的文本

下一篇：將檔案型別與應用程式相關聯