如何在SQL Server 2005中使用xml元素名称作为xml元素名?

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.

我希望以以下形式生成XML，其中@elements表的值成为元素名，@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:

然而，到目前为止，我为xml所做的努力并不顺利:

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

对于xml自动返回

<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

对于xml原始返回

<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.

是否有一种方法可以从列中获取值作为元素名?我肯定我漏掉了一些明显很简单的东西。

2 个解决方案

#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 ---

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

#1