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

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 — Gottfried Lesigang
– Gottfried Lesigang, Commented Sep 14, 2016 at 14:44

wst · Accepted Answer · 2013-03-15 15:31:36Z

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, 2013 at 15:31

wst

11.8k1 gold badge27 silver badges41 bronze badges

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

Comments

Mikael Eriksson · Accepted Answer · 2013-03-15 15:40:24Z

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, 2013 at 15:40

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

2 Comments

ca9163d9 Over a year ago

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.

Mikael Eriksson Over a year ago

@NickW Don't have the time right now for this but I did something similar before. Have a look at this: stackoverflow.com/a/7087899/569436

Collectives™ on Stack Overflow

xquery return xml elements in for loop?

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related