How do I use column values as xml element names using for xml in SQL Server 2005?

Question

Simplifying what I'm doing somewhat, as an example, say I have the following tables:

declare @elements table (id int, name nvarchar(20))

insert into @elements (id, name) values (1, 'FirstName')
insert into @elements (id, name) values (2, 'Surname')
insert into @elements (id, name) values (3, 'Address')

declare @values table (id int, value nvarchar(20), elementId int)

insert into @values (id, value, elementId) values (1, 'XXX', 1)
insert into @values (id, value, elementId) values (2, 'YYY', 2)
insert into @values (id, value, elementId) values (3, 'ZZZ', 3)

which simply defines a table of element names that could be dynamic, against which are defined a table of values.

What I would like is to generate XML in the following form, where the values of the @elements table become the element names, and the values of the @values table become the values.

<Customer>
    <FirstName>XXX</FirstName>
    <Surname>YYY</Surname>
    <Address>ZZZ<Address>
</Customer>

However my efforts with for xml so far are not going so well:

select e.name, v.value from @elements e
inner join @values v on v.elementId = e.id
for xml path(''), root('customer')

returns

<customer>
  <name>FirstName</name>
  <value>XXX</value>
  <name>Surname</name>
  <value>YYY</value>
  <name>Address</name>
  <value>ZZZ</value>
</customer>

for xml auto returns

<customer>
  <e name="FirstName">
    <v value="XXX" />
  </e>
  <e name="Surname">
    <v value="YYY" />
  </e>
  <e name="Address">
    <v value="ZZZ" />
  </e>
</customer>

for xml raw returns

<customer>
  <row name="FirstName" value="XXX" />
  <row name="Surname" value="YYY" />
  <row name="Address" value="ZZZ" />
</customer>

Is there a way I can get the values from a column to output as element names? I'm sure I'm missing something obviously simple here.

Solution 1

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>

Solution 2

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, value
from @values v
join @elements e on v.id = e.id) ve
pivot (max(value)
for name in ([FirstName], [Surname], [Address])) as p
for xml path('Customer')

Comments

[marc_s]

Good idea - but again: you need to explicitly and manually specify the columns in the PIVOT command - there's no reading those from the @elements table and using them, right?

[Remus Rusanu]

@Marc: right. But that is the whole idea of why the EAV model is such a bad idea to start with. SQL is a relational system, where tables have columns. So store columns as columns and you won't have this 'problem' to start with ;)

[marc_s]

@Renus: agree totally! EAV is messy and evil - and still unfortunately widely used :-(

[tjmoore]

Thanks. I'd agree also. I've got complex reasons for attempting it this way, involving mapping entity hierarchies (which can be dynamic) and merging resulting xml with some existing xml that is of the same structure. It's not nice though. I will likely change my approach.

[tjmoore]

Sorry for the long delay (3 years!). Yes, this does work. Cheezy maybe, but works. I agree with the above comments also that EAV is not the right approach anyway, but this does the job. I've long forgotten what I actually did. It might have been a bit of XSL or client code to translate the XML into the format I needed.

[vittore]

@tjmoore so how you ended up changing your model in order to get custom xml ?

[Konstantin]

If value contains for instance '&' it fails. So no this does not work in the general case. Test with this to see the failure select cast( '<a>barnes & noble</a>' as xml)