SQL Server 2008 XQuery one to many issue

Question

I have two tables in SQL Server with a one to many relationship. I need to perform a XQuery across these tables, to obtain an xml output

The tables (and sample records) are 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 I want to obtain performing xquery is 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 this is the xquery I run:

SELECT 
    Name AS 'Identification/Name',
    Description AS 'Identification/Description',
    Value1 AS 'EntityAttributes/Value1',
    Value2 AS 'EntityAttributes/Value2',
    (
     SELECT  
         SubValue AS SubValue
     FROM 
         Entity_B
     WHERE 
         Entity_B.ID_Entity_A = Entity_A.ID
     FOR XML PATH(''), ROOT('EntityAttributes'), TYPE)
FROM Entity_A
FOR XML PATH('EntityNode'), ROOT('MyRoot'), ELEMENTS

But the output I obtain is this (different from the 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

Answer 1

Specify the element name for the subquery using a column alias instead of root(). That will make Value1 and Value2 end up in the same node as the 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 into Entity_A values
(10,  'aName1',   'aDescription1',   'AttVal1',    'AttVal2'),
(11,  'aName2',   'aDescription2',   'AttVal21',   'AttVal25');

insert into 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 as [Identification/Name],
       A.Description as [Identification/Description],
       A.Value1 as [EntityAttribute/Value1],
       A.Value2 as [EntityAttribute/Value2],
       (
       select B.SubValue
       from dbo.Entity_B as B
       where A.ID = B.ID_Entity_A
       for xml path(''), type
       ) as [EntityAttribute]
from dbo.Entity_A as A
for 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>    |