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
– Greg White, Commented May 26, 2016 at 13:29
i think you might be missing a tag. html and xml cant process sql results — Takarii
– Takarii, Commented May 26, 2016 at 13:31
sql has an xml for.. something similar to this but with a group by stackoverflow.com/questions/7086393/… — Greg White
– Greg White, Commented May 26, 2016 at 13:33
Sorry, I wasn't clear. neither html nor xml can execute SQL statements. what web platform are you using. — Takarii
– Takarii, Commented May 26, 2016 at 13:34

Gottfried Lesigang · Accepted Answer · 2016-05-26 20:41:09Z

1

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

answered May 26, 2016 at 20:41

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gottfried Lesigang Over a year ago

@GregWhite You might look at this question There I just posted a new solution which will create a XHTML table from any SELECT generically. Might be interesting...

wvdz · Accepted Answer · 2016-05-26 13:37:41Z

0

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.

answered May 26, 2016 at 13:37

wvdz

16.2k4 gold badges60 silver badges97 bronze badges

Comments

artm · Accepted Answer · 2016-05-26 14:46:10Z

0

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

edited May 26, 2016 at 14:46

answered May 26, 2016 at 14:36

artm

8,5943 gold badges28 silver badges43 bronze badges

1 Comment

Gottfried Lesigang Over a year ago

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

Collectives™ on Stack Overflow

SQL to html using XML?

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related