How to return a specific column as an element with FOR XML AUTO clause

Question

I need to return a particular column as an element in the xml returned with the FOR XML AUTO clause in the sql server. Automatic return turns all fields into attributes of the corresponding element. Okay, but one field or another I need it to be an element.

I have two tables:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
    [Id] [int] NULL,
    [Nome] [varchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Table2]    Script Date: 02/03/2018 16:24:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table2](
    [Id] [int] NULL,
    [DataVencimento] [date] NULL,
    [Table1_Id] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Table1] ([Id], [Nome]) VALUES (1, N'AAA')
GO
INSERT [dbo].[Table1] ([Id], [Nome]) VALUES (2, N'BBB')
GO
INSERT [dbo].[Table1] ([Id], [Nome]) VALUES (3, N'CCC')
GO
INSERT [dbo].[Table2] ([Id], [DataVencimento], [Table1_Id]) VALUES (1, CAST(N'2018-01-01' AS Date), 1)
GO
INSERT [dbo].[Table2] ([Id], [DataVencimento], [Table1_Id]) VALUES (2, CAST(N'2018-01-02' AS Date), 2)
GO
INSERT [dbo].[Table2] ([Id], [DataVencimento], [Table1_Id]) VALUES (3, CAST(N'2018-01-03' AS Date), 2)
GO

I have the following relationship between them:

select 
    Table1.Id,
    Table1.Nome,
    Table2.Id,
    Table2.DataVencimento
from Table1 
    inner join Table2 on Table2.Table1_Id = Table1.Id
    order by Table1.Id, Table2.Id
for xml auto, root('ArrayOfTable1')

which returns:

<ArrayOfTable1>
  <Table1 Id="1" Nome="AAA">
    <Table2 Id="1" DataVencimento="2018-01-01" />
  </Table1>
  <Table1 Id="2" Nome="BBB">
    <Table2 Id="2" DataVencimento="2018-01-02" />
    <Table2 Id="3" DataVencimento="2018-01-03" />
  </Table1>
</ArrayOfTable1>

But I need DataVencimento to be an element, like this:

<ArrayOfTable1>
    <Table1 Id="1" Nome="AAA">
        <Table2 Id="1">
            <DataVencimento>2018-01-01</DataVencimento>
        </Table2>
    </Table1>
    <Table1 Id="2" Nome="BBB">
        <Table2 Id="2">
            <DataVencimento>2018-01-02</DataVencimento>
        </Table2>
        <Table2 Id="3">
            <DataVencimento>2018-01-03</DataVencimento>
        </Table2>
    </Table1>
</ArrayOfTable1>

How to do this?

Answer 1

select 
    Table1.ID,
    Table1.Nome,
    Table2.Id,
    (select table2.DataVencimento for xml path(''), elements, type)
from Table1 
    inner join Table2 on Table2.Table1_Id = Table1.Id
order by Table1.Id, Table2.Id
for xml auto, root('ArrayOfTable1')

output:

<ArrayOfTable1>
  <Table1 ID="1" Nome="AAA">
    <Table2 Id="1">
      <DataVencimento>2018-01-01</DataVencimento>
    </Table2>
  </Table1>
<Table1 ID="2" Nome="BBB">
  <Table2 Id="2">
    <DataVencimento>2018-01-02</DataVencimento>
  </Table2>
  <Table2 Id="3">
    <DataVencimento>2018-01-03</DataVencimento>
  </Table2>
</Table1>
</ArrayOfTable1>

http://sqlfiddle.com/#!18/71fe9/29