CODEWITHSUNDEEP
sql-serverxmlsql-server-2008xquery
ca9163d9
ca9163d9

Reputation: 29159

xquery return xml elements in for loop?

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 &lt; and &gt;?

Upvotes: 0

Views: 1397

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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('')

Upvotes: 1

wst
wst

Reputation: 11773

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

or

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

Upvotes: 2

Related Questions