Creating a nested HTML data from SQL query

Question

The code I have below gives me the basic output I require of a table nested in another table, see screen shot table1

However, I could not get the right out put without adding the line of code

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

because the original code was being escaped for the "<" and ">".

Is there a cleaner or better way to do this?

Note the use of dB mail is just a quick way to see the HTML output (is there a better way?). If you use my code you need dB mail configured and to change the email profile and address

 /***********************************************/
 /*** 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';

Answer 1

If you would have formatted your code properly you would have seen you are missing , TYPE in one of the subqueries, on the line 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')
);

You can reduce the number of subqueries, by using FOR XML PATH instead of RAW, and using a full path. To avoid it being merged with the previous column you need to put an emptry string column in between.

 (
 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

Alternatively you can unpivot the td columns into separate rows, so you don't have multiple columns with the same name.

• Ian W: Sorry, I missed the formatting, I should have checked better when I had to use a Replace statement. I was working from a couple of examples and tbh my For xml theoretical knowledge is not great and I really do not get the difference between For XML Path and For XML Raw. Is there simple difference between the two?
• Charlieface: RAW does not nest elements and takes every name literally, PATH uses @ for attributes and / to descend into nested elements.