generate xml with key/value structure using for xml

Question

I am trying to generate this

<Mail>
  <Field key="SenderName" value="someone" />
  <Field key="RecipientName" value="someone else" />
<Mail/>
<Mail>
  <Field key="SenderName" value="another someone" />
  <Field key="RecipientName" value="another someone else" />
<Mail/>

heres the test data

declare @tab table (SenderName varchar(255), RecipientName varchar(255))
insert @tab
select 'someone' , 'someone else' union 
select 'another someone' , 'another someone else'

I have tried various things with for xml path but I cant work out how to tell declare that I want a new element of the same name after recipient name.

thanks,

Mikael Eriksson · Accepted Answer · 2012-02-09 20:35:40Z

2

declare @tab table (SenderName varchar(255), RecipientName varchar(255))
insert @tab
select 'someone' , 'someone else' union 
select 'another someone' , 'another someone else' 

select (select 'SenderName' as "@key",
                SenderName  as "@value"
        for xml path('Field'), type),
       (select 'RecipientName' as "@key",
                RecipientName  as "@value"
        for xml path('Field'), type) 
from @tab
for xml path('Mail')

answered Feb 9, 2012 at 20:35

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

Collectives™ on Stack Overflow

generate xml with key/value structure using for xml

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related