How do I use column values as xml element names using for xml in SQL Server 2005?
It's cheezy but it works...
select cast('<' + name + '>' + value + '</' + name + '>' as xml) from @values v join @elements e on v.id = e.id for xml path(''), root('Customer')--- results ---
<Customer> <FirstName>XXX</FirstName> <Surname>YYY</Surname> <Address>ZZZ</Address></Customer>
You are trying to model the dreaded semantic database (Entity-Attribute-Value). Read this paper to at least get you started on the right path: Best Practices for Semantic Data Modeling for Performance and Scalability
Technically, this is the query you're looking for:
select * from (select name, valuefrom @values vjoin @elements e on v.id = e.id) vepivot (max(value)for name in ([FirstName], [Surname], [Address])) as pfor xml path('Customer')