xquery return xml elements in for loop?

Question

I have the following xquery for Sql server 2008 tables.

declare @tableName sysname = 'tableName'
declare @colNames xml = (select COLUMN_NAME from INFORMATION_SCHEMA.columns where TABLE_NAME = @tableName for xml path(''), elements)
select @colNames.query('<html><body>
<table>
<tr>
{
    for $c in /COLUMN_NAME
    return data($c)[1]
}
</tr>
</table>
</body></html>
')

However, it returns the following xml.

<html>
  <body>
    <table>
      <tr>col1col2col3col4col5</tr>
    </table>
  </body>
</html>

And the expected result is

<html>
  <body>
    <table>
      <tr><th>col1</th><th>col2</th><th>col3</th><th>col4</th><th>col5</th></tr>
    </table>
  </body>
</html>

I tried to change return data($c)[1] to concat("<th>", data($c)[1], "</th>"). However, the < and > were escaped to < and >?

Possible duplicate of [Create HTML Table with SQL FOR XML](http://stackoverflow.com/questions/7086393/create-html-table-with-sql-for-xml) — Shnugo, Sep 14 '16 at 14:42
Hi, if this is still interesting for your, especially due to the comment below (*Is it possible to append the data of the table to xml under the column header too?*), I'd like to point you to an [answer to a related question I just answered](http://stackoverflow.com/a/39487565/5089204) — Shnugo, Sep 14 '16 at 14:44

score 2 · Accepted Answer · answered Mar 15 '13 at 15:31

2

for $c in /COLUMN_NAME
return element th { data($c)[1] }

or

for $c in /COLUMN_NAME
return <th>{ data($c)[1] }</th>

answered Mar 15 '13 at 15:31

wst

11,681
1
24
39

score 1 · Answer 2 · answered Mar 15 '13 at 15:40

1

Alternatively you could do it directly in your query.

select
  (
  select COLUMN_NAME as '*'
  from INFORMATION_SCHEMA.columns 
  where TABLE_NAME = @tableName
  for xml path('th'), root('tr'), type
  ) as "table"
for xml path('body'), root('html')

or

select
  (
  select COLUMN_NAME as th
  from INFORMATION_SCHEMA.columns 
  where TABLE_NAME = @tableName
  for xml path(''), type
  ) as "html/body/table/tr"
for xml path('')

answered Mar 15 '13 at 15:40

Mikael Eriksson

136,425
22
210
281

Great solution too. Is it possible to append the data of the table to xml under the column header too? I think I will need to create a new question for it. – ca9163d9 Mar 15 '13 at 15:54
@NickW Don't have the time right now for this but I did something similar before. Have a look at this: http://stackoverflow.com/a/7087899/569436 – Mikael Eriksson Mar 15 '13 at 16:05

xquery return xml elements in for loop?

2 Answers2