xml - SQL Server 2008 XQuery one to many issue -

i have 2 tables in sql server 1 many relationship. need perform xquery across these tables, obtain xml output

the tables (and sample records) those:

entity_a

id  name     description     value1     value2 ------------------------------------------------ 10  aname1   adescription1   attval1    attval2 11  aname2   adescription2   attval21   attval25 

entity_b

id   id_entity_a  subvalue --------------------------- 1      10         sv1 2      10         sv2 3      10         sv3 4      10         sv4 5      11         sv5 6      11         sv6 

the output want obtain performing xquery this:

<myroot>     <entitynode>         <identification>             <name>aname1</name>             <description>adescription1</description>         </identification>         <entityattributes>             <value1>attval1</value1>             <value2>attval2</value2>             <subvalue>sv1<subvalue>             <subvalue>sv2<subvalue>             <subvalue>sv3<subvalue>             <subvalue>sv4<subvalue>         </entityattributes>     </entitynode>     <entitynode>         <identification>             <name>aname2</name>             <description>adescription2</description>         </identification>         <entityattributes>             <value1>attval21</value1>             <value2>attval25</value2>             <subvalue>sv5<subvalue>             <subvalue>sv6<subvalue>         </entityattributes>     </entitynode>     ... </myroot> 

and xquery run:

select      name 'identification/name',     description 'identification/description',     value1 'entityattributes/value1',     value2 'entityattributes/value2',     (      select            subvalue subvalue                entity_b                entity_b.id_entity_a = entity_a.id      xml path(''), root('entityattributes'), type) entity_a xml path('entitynode'), root('myroot'), elements 

but output obtain (different desired output):

<myroot>     <entitynode>         <identification>             <name>aname1</name>             <description>adescription1</description>         </identification>         <entityattributes>             <value1>attval1</value1>             <value2>attval2</value2>         </entityattributes>         <entityattributes>             <subvalue>sv1<subvalue>             <subvalue>sv2<subvalue>             <subvalue>sv3<subvalue>             <subvalue>sv4<subvalue>         </entityattributes>     </entitynode>     <entitynode>         <identification>             <name>aname2</name>             <description>adescription2</description>         </identification>         <entityattributes>             <value1>attval21</value1>             <value2>attval25</value2>         </entityattributes>         <entityattributes>             <subvalue>sv5<subvalue>             <subvalue>sv6<subvalue>         </entityattributes>     </entitynode>     ... </myroot> 

any suggestion about?

best regards

specify element name subquery using column alias instead of root(). make value1 , value2 end in same node subvalue's.

sql fiddle

ms sql server 2008 schema setup:

create table entity_a (   id int,   name varchar(10),   description varchar(20),   value1 varchar(10),   value2 varchar(10) );  create table entity_b (   id int,   id_entity_a int,   subvalue char(3) );  insert entity_a values (10,  'aname1',   'adescription1',   'attval1',    'attval2'), (11,  'aname2',   'adescription2',   'attval21',   'attval25');  insert entity_b values (1,      10,         'sv1'), (2,      10,         'sv2'), (3,      10,         'sv3'), (4,      10,         'sv4'), (5,      11,         'sv5'), (6,      11,         'sv6'); 

query 1:

select a.name [identification/name],        a.description [identification/description],        a.value1 [entityattribute/value1],        a.value2 [entityattribute/value2],        (        select b.subvalue        dbo.entity_b b        a.id = b.id_entity_a        xml path(''), type        ) [entityattribute] dbo.entity_a xml path('entitynode'), root('myroot'); 

results:

<myroot>   <entitynode>     <identification>       <name>aname1</name>       <description>adescription1</description>     </identification>     <entityattribute>       <value1>attval1</value1>       <value2>attval2</value2>       <subvalue>sv1</subvalue>       <subvalue>sv2</subvalue>       <subvalue>sv3</subvalue>       <subvalue>sv4</subvalue>     </entityattribute>   </entitynode>   <entitynode>     <identification>       <name>aname2</name>       <description>adescription2</description>     </identification>     <entityattribute>       <value1>attval21</value1>       <value2>attval25</value2>       <subvalue>sv5</subvalue>       <subvalue>sv6</subvalue>     </entityattribute>   </entitynode> </myroot>    |