CODEWITHSUNDEEP
sql-serverxmlfor-xml
evbell
evbell

Reputation: 97

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

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?

Upvotes: 2

Views: 507

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

In most cases FOR XML PATH() is the prefered approach. With PATH you have full control over the output. Nothing is automatic...

Try it out:

SELECT t1.Id AS [@id]
      ,t1.Nome AS [@Nome]
      ,(
        SELECT t2.Id AS [@Id]
              ,t2.DataVencimento
        FROM dbo.Table2 AS t2
        WHERE t1.Id=t2.Table1_Id
        ORDER BY t2.Id
        FOR XML PATH('Table2'),TYPE
       )
FROM dbo.Table1 AS t1
WHERE EXISTS(SELECT 1 FROM dbo.Table2 WHERE Table2.Table1_Id=t1.Id)
ORDER BY t1.Id
FOR XML PATH('Table1'), ROOT('ArrayOfTable1');

Upvotes: 0

IVNSTN
IVNSTN

Reputation: 9299

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

Upvotes: 1

Adam Demidiuk
Adam Demidiuk

Reputation: 19

I don't now if there is a cleaner solution but something like this should work:

select 
    Table1.Id as [@Id],
    Table1.Nome as [@Nome],
    [inner].[xml] as [*]
from Table1 
outer apply 
( select (select
     Table2.Id as [@Id],
    Table2.DataVencimento
  from Table2 
  where Table2.Table1_Id = Table1.Id
  for xml path('Table2'), type) as [xml]

) as [inner]
    inner join Table2 on Table2.Table1_Id = Table1.Id
    order by Table1.Id, Table2.Id
for xml path('Table1'), root('ArrayOfTable1')

Upvotes: 1

Related Questions