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

Question

I need to make a certain column an element in the return xml when I use the FOR XML AUTO clause. Auto-return transforms all fields into attributes of the corresponding element. All right, but either field I need 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
for xml auto, root('ArrayOfTable1')

that produces that result:

<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 Datamaturity to be an element like in the xml below:

<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

This is a suggestion that comes closer to what you request.

-- código #1 v3
select 
    1 as Tag,
    0 as Parent,
    T1.Id as [Table1!1!Id],
    T1.Nome as [Table1!1!Nome],
    NULL as [Table2!2!Id],
    NULL as [DataVencimento!3]
  from Table1 as T1 
  where exists(SELECT * from Table2 as T2 where T2.Table1_Id = T1.Id)

union all

select 2, 1, T1.Id, T1.Nome, T2.Id, NULL
  from Table1 as T1 
       inner join Table2 as T2 on T2.Table1_Id = T1.Id

union all

select 3, 2, T1.Id, T1.Nome, T2.id, T2.DataVencimento
  from Table1 as T1 
       inner join Table2 as T2 on T2.Table1_Id = T1.Id

  order by [Table1!1!Id], [Table2!2!Id], [DataVencimento!3] 

  for XML EXPLICIT, ROOT('ArrayOfTable1');

But there’s probably a simpler way to get the same result...

Answer 2

They gave me this solution in stackoverflow in English:

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

and that provides the expected result.