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

Question

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

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

I need to make a particular column an element in the return XML when I use the FOR XML AUTO clause. Automatic return turns all fields into attributes of the corresponding element. All right, but one field or another 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 this 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 DataVencement to be an element like in 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?

sql-server for xml

asked by anonymous 02.03.2018 / 20:39

2 answers

1

Here is the suggestion that comes closest to what you are asking for.

-- 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 is probably a simpler way to get the same result ...

02.03.2018 / 21:54

GLMM - Prediction with ID How not to let an animation in CSS stop even after the mouse leaves the element

score 0 · Accepted Answer

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.