主页 / dba / 问题 / 330023
Accepted
Ian W
Asked: 2023-08-07 22:22:04 +0800 CST

从 SQL 查询创建嵌套 HTML 数据

  • 772

下面的代码为我提供了嵌套在另一个表中的表所需的基本输出，请参阅屏幕截图 表格1

但是，如果不添加代码行，我就无法获得正确的输出

Set @BodyTxt =(select REPLACE(replace (@BodyTxt,'&lt;','<'),'&gt;','>'))

因为原始代码被转义为“<”和“>”。

有没有更干净或更好的方法来做到这一点？

请注意，使用 dB 邮件只是查看 HTML 输出的快速方法（有更好的方法吗？）。如果您使用我的代码，您需要配置 dB 邮件并更改电子邮件配置文件和地址

 /***********************************************/
 /*** set up test data                          */
 /***********************************************/

 DROP TABLE IF EXISTS dbo.Units
 DROP TABLE IF EXISTS dbo.Document
CREATE TABLE Units(
DocNum int,
DocRow int,
Serial varchar(255),
Element varchar(255),
ManDate datetime)

INSERT dbo.Units
Values (50009,0,'Serial1','Element1','2021-01-04 00:00:00'),
        (50009,0,'Serial2','Element2','2021-01-04 00:00:00'),
        (50009,1,'Serial4','Element2','2021-01-06 00:00:00'),
        (50006,0,'Serial3','Element2','2021-01-07 00:00:00')
CREATE TABLE Document(
DocNum int,
DocRow int,
Part varchar(255),
Qty int,
DocDate datetime)

INSERT dbo.Document
Values (50009,0,'PartA',2,'2023-07-04 00:00:00'),
        (50008,0,'PartC',1,'2023-06-28 00:00:00'),
        (50007,0,'PartB',4,'2023-06-24 00:00:00'),
        (50006,0,'PartA',1,'2023-01-04 00:00:00')
/******* End of Test Data set up       *************/

declare  @BodyTxt nvarchar(max)

Set @BodyTxt =(
SELECT  
    (SELECT 'Table I' FOR XML PATH(''),TYPE) AS 'caption',
    (SELECT 'Doc' AS th, 'Line' AS th,'Part' as th,'Qty' as th,'Unit Details' as th FOR XML raw('tr'),ELEMENTS, TYPE) AS 'thead',
    --(SELECT 'sum' AS th, 'twenty' AS th FOR XML raw('tr'),ELEMENTS, TYPE) AS 'tfoot',
    (SELECT DocNum AS td, DocRow AS td,Part AS td,Qty AS td, (Select (Select Serial as td,Element as td,convert (varchar ,ManDate, 6) as td from dbo.Units U Where U.DocNum=D.DocNum and U.DocRow=D.DocRow FOR XML RAW('tr'), ELEMENTS, TYPE ) AS 'tbody'
  FOR XML PATH(''), ROOT('table')) AS td FROM dbo.Document D FOR XML RAW('tr'), ELEMENTS, TYPE
    ) AS 'tbody'
  FOR XML PATH(''), ROOT('table'))
Set @BodyTxt =(select REPLACE(replace (@BodyTxt,'&lt;','<'),'&gt;','>'))


EXEC msdb.dbo.sp_send_dbmail  
                                @profile_name = 'Send via 365'  
                                ,@recipients   = '[email protected]'
                                
                                ,@subject      = 'Test table'
                                ,@body         =  @BodyTxt
                                ,@body_format = 'HTML';
sql-server

1 个回答

  1. Best Answer

    如果您正确地格式化了代码，您会发现您, TYPE在其中一个子查询中丢失了，在AS tbody FOR XML PATH(''), ROOT('table').

    Set @BodyTxt = (
  SELECT  
      (SELECT 'Table I' FOR XML PATH(''), TYPE) AS caption,
      (
          SELECT
            'Doc' AS th,
            'Line' AS th,
            'Part' as th,
            'Qty' as th,
            'Unit Details' as th
           FOR XML raw('tr'), ELEMENTS, TYPE
      ) AS thead,
      (
          SELECT
            DocNum AS td,
            DocRow AS td,
            Part AS td,
            Qty AS td,
            (
              Select (
                Select
                  Serial as td,
                  Element as td,
                  convert(varchar(30), ManDate, 6) as td
                from dbo.Units U
                Where U.DocNum = D.DocNum
                  and U.DocRow = D.DocRow
                FOR XML RAW('tr'), ELEMENTS, TYPE
              ) AS tbody
              FOR XML PATH(''), ROOT('table'), TYPE
            ) AS td
          FROM dbo.Document D
          FOR XML RAW('tr'), ELEMENTS, TYPE
      ) AS tbody
  FOR XML PATH(''), ROOT('table')
);

    FOR XML PATH您可以通过使用代替RAW和使用完整路径来减少子查询的数量。为了避免它与前一列合并，您需要在中间放置一个空字符串列。

          (
          SELECT
            DocNum AS td,
            '',    -- do not remove this
            DocRow AS td,
            '',    -- do not remove this
            Part AS td,
            '',    -- do not remove this
            Qty AS td,
            '',    -- do not remove this
            (
                Select
                  Serial as td,
                  Element as td,
                  convert(varchar(30), ManDate, 6) as td
                from dbo.Units U
                Where U.DocNum = D.DocNum
                  and U.DocRow = D.DocRow
                FOR XML RAW('tr'), ROOT('tbody'), ELEMENTS, TYPE
            ) AS [td/table]
          FROM dbo.Document D
          FOR XML PATH('tr'), ELEMENTS, TYPE
      ) AS tbody

    或者，您可以将td列逆透视为单独的行，这样就不会出现多个同名的列。

    • 2

相关问题