SQL to html using XML?

Question

ok here is what im trying to do I have an sql query within a ssis package that I need to transform into html for storing into an sql table field varbinary(max)

here is the source table (simplified):

Customer ID Question        Answer
1                   how are you? ok
1                   like beans?     mm yes
2                   how are you? fine
2                   like beans?     yuk

The output ideally needs to be:

CustomerID html
1                  see below
2                  see below

where the html is the table of the questions / answers converted to varbinary(max) grouped by customerID. Thinking of using for xml or something like that but super stuck on this. Help much appreciated.

<table>
<tr>
<th>Question</th>
<th>Answer</th>
</tr>
<tr>
<td>How are you?</td>
<td>ok</td>
</tr>
<tr>
<td>like beans?</td>
<td>mm yes</td>
</tr>
</table>

simplified: I want to group the source table by customerID and store the Question and Answer as a html table string. — Greg White, May 26 '16 at 13:29
i think you might be missing a tag. html and xml cant process sql results — Takarii, May 26 '16 at 13:31
sql has an xml for.. something similar to this but with a group by http://stackoverflow.com/questions/7086393/create-html-table-with-sql-for-xml — Greg White, May 26 '16 at 13:33
Sorry, I wasn't clear. neither html nor xml can execute SQL statements. what web platform are you using. — Takarii, May 26 '16 at 13:34
I dont want to execute html or xml from sql I want to format the query results into HTML to store in varbinary(max) — Greg White, May 26 '16 at 13:37

score 1 · Accepted Answer · answered May 26 '16 at 20:41

This should be what you need:

DECLARE @questions TABLE(QuestID INT IDENTITY, CustomerID INT, Question VARCHAR(100),Answer VARCHAR(100));
INSERT INTO @questions(CustomerID,Question,Answer) VALUES
 (1,'How are you?','ok')
,(1,'Like beans?','mm yes')
,(2,'How are you?','fine')
,(2,'Like beans?','yuk');

SELECT CustomerID
      ,(
            SELECT       
                   (
                        SELECT 'Question' AS [th],''
                              ,'Answer' AS [th],''
                        FOR XML PATH('tr'),TYPE
                   )
                  ,(    
                        SELECT  Question AS [td],''
                                ,Answer AS [td],'' 
                        FROM @questions AS q 
                        WHERE q.CustomerID=Cust.CustomerID
                        FOR XML PATH('tr'),TYPE
                    )
            FOR XML PATH(''),ROOT('table'),TYPE
        )
FROM @questions AS Cust
GROUP BY CustomerID

@GregWhite You might look at [this question](http://stackoverflow.com/questions/7086393/create-html-table-with-sql-for-xml) There I just posted a new solution which will create a `XHTML` table from any `SELECT` generically. Might be interesting... — Shnugo, Sep 14 '16 at 14:39

score 0 · Answer 2 · answered May 26 '16 at 13:37

It is a little bit tedious, but this can be achieved by using XML functions, that most DBMS's support. For example, here is the documentation for PostgreSQL: https://www.postgresql.org/docs/9.1/static/functions-xml.html

Your sql would look something like this:

SELECT XMLELEMENT("table",
          (XMLELEMENT("tr",
             XMLCONCAT(
                XMLELEMENT("th",'Question'),
                XMLELEMENT("th",'Answer'))
              ...etc.
           ))
FROM mytable
GROUP BY customerID;

Use subqueries to get the questions and answers per customer.

artm · Answer 3 · 2016-05-26T14:46:10.447

Using xml path with ,type you can define which values should go to a th and which to a td:

declare @cust table(custid int, que nvarchar(50), ans nvarchar(50))

insert into @cust values (1, 'how are you?', 'ok')
, (1, 'like beans?', 'yes')
, (2, 'how are you?', 'good')
, (2, 'like beans?', 'no')

 select c.custid, 
(select 
    (select th
            from (select 'Question' th
                    union all
                  select 'Answer') d 
          for xml path(''),type) tr --first row for the headers
          , (select 
                (select que for xml path('td'),type) --question column
                , (select ans for xml path('td'),type) --answer column
                from (select custid, que, ans
                        FROM @cust i
                        where i.custid = c.custid
                    ) data 
           for xml path ('tr'),type) --each question answer in a row
           for xml path('table'), type) --for the whole table 
 from @cust c
 group by c.custid

Fiddle

Your result will include a "que" and a "ans" tag before each inner element. You can avoid this by adding `select que as [*]` (similar with "ans") - And secondly this can be done much easier. Look at my answer... — Shnugo, May 26 '16 at 20:42

SQL to html using XML?

3 Answers3