creating XML elements and values from table data



I have a table that stores data:



declare @t table
(
el nvarchar(50),
val nvarchar(50)
)
insert into @t values ('n1', 'value 1'), ('n2', 'value 2'), ('n3', 'value 3 <>')


I should get xml, that look like this:



<data>
<n1>value 1</n1>
<n2>value 2</n2>
<n3>value 3 &lt;&gt;</n3>
</data>


the best I could get so far is:



select cast('<' + el + '>' + (select val from @t i where i.el = t.el for xml raw(''), elements) + '</' + el + '>' as xml)
from @t t
For XML Raw(''), root('data'), Elements, Type


Could you help? Thanks!


No comments:

Post a Comment