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.

I don't think you can do that. You can do a lot with the various FOR XML commands - but in all cases, the names of the resulting XML elements and/or attributes need to be fixed, e.g. typed in by you. I don't know of any way to get those to be pulled from a table and dynamically assigned — marc_s
– marc_s, Commented Jul 13, 2010 at 19:09

takendarkk · Accepted Answer · 2014-07-25 16:25:20Z

15

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>

edited Jul 25, 2014 at 16:25

takendarkk

3,4568 gold badges28 silver badges38 bronze badges

answered May 24, 2011 at 17:52

Chris Gessler

23.2k8 gold badges59 silver badges86 bronze badges

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

3 Comments

tjmoore Over a year ago

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 Over a year ago

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

Konstantin Over a year ago

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)

Emyr · Accepted Answer · 2015-12-23 13:32:20Z

7

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

edited Dec 23, 2015 at 13:32

Emyr

2,38118 silver badges39 bronze badges

answered Jul 13, 2010 at 19:08

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

4 Comments

marc_s Over a year ago

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 Over a year ago

@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 Over a year ago

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

tjmoore Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related