html - 如何将sql查询结果转换为带有属性的html标签

Question

考虑 SQL-Server 数据库中的下表:

Id    ProductName   Price    Size
-----------------------------------
1     T-Shirt       1000     M
2     Jeans         1800     L

如何将此表格转换为以下 HTML 字符串:

<div class='row' Id='1' size='M'>
    <div class='cell'>T-Shirt</div> 
    <div class='cell'>1000</div>
</div>
<div class='row' Id='2' size='L'>
    <div class='cell'>Jeans</div> 
    <div class='cell'>1800</div>
</div>

Answer 1

像这样尝试:

首先是一个模型表(下次请自己做):

DECLARE @tbl TABLE(Id INT,ProductName VARCHAR(100),Price DECIMAL(10,4),Size VARCHAR(10));
INSERT INTO @tbl VALUES
 (1,'T-Shirt',1000,'M')
,(2,'Jeans',1800,'L');

查询将使用 FOR XML PATH , 它提供了对 XML 命名的最高控制

SELECT 'row' AS [@class]
      ,t.Id AS [@Id]
      ,t.Size AS [@size]
      ,'cell' AS [div/@class]
      ,t.ProductName AS [div]
      ,''
      ,'cell' AS [div/@class]
      ,t.Price AS [div]
FROM @tbl t
FOR XML PATH('div');

结果:

<div class="row" Id="1" size="M">
  <div class="cell">T-Shirt</div>
  <div class="cell">1000.0000</div>
</div>
<div class="row" Id="2" size="L">
  <div class="cell">Jeans</div>
  <div class="cell">1800.0000</div>
</div>

提示 1:我们需要空的 ,''在中间开始一个新的<div>元素。否则引擎会抛出错误。

提示 2:典型的 HTML 表格 can be built with some XQuery