SQL Server Order By For XML

 in SQL Server  tagged xml / order / for by

This post considers a couple of SQL Server statements: Order By and For Xml. If you have a table of data that you need to export to XML, then you can use FOR XML. When you use FOR XML you can also use ORDER BY. In the example below the ORDER BY is for AmountInt, which is also in the SELECT statement. The column that you are ordering by does not also need to be in the SELECT statement.

Here is some code you can use on a non-production database. 

CREATE TABLE [dbo].[Persons2](
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[AmountInt] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Persons2] ([FirstName], [LastName], [AmountInt]) VALUES (N'John', N'Doe', 21)
GO
INSERT [dbo].[Persons2] ([FirstName], [LastName], [AmountInt]) VALUES (N'James', N'Bond', 18)
GO
INSERT [dbo].[Persons2] ([FirstName], [LastName], [AmountInt]) VALUES (N'Sally', N'Smith', 2)
GO
INSERT [dbo].[Persons2] ([FirstName], [LastName], [AmountInt]) VALUES (N'Julie', N'Johnson', 45)
GO
INSERT [dbo].[Persons2] ([FirstName], [LastName], [AmountInt]) VALUES (N'Kyle', N'Jackson', 20)
GO
INSERT [dbo].[Persons2] ([FirstName], [LastName], [AmountInt]) VALUES (N'Suzie', N'South', 31)
GO
INSERT [dbo].[Persons2] ([FirstName], [LastName], [AmountInt]) VALUES (N'Peter', N'Michaels', 7)
GO
SELECT  [FirstName],[LastName],[AmountInt]
  FROM [dbo].[Persons2]
  order by AmountInt asc
  FOR XML path('person'), root('allpersons')
GO

The XML output for this is shown below. It is sorted by the AmountInt. 

<allpersons>
  <person>
    <FirstName>Sally</FirstName>
    <LastName>Smith</LastName>
    <AmountInt>2</AmountInt>
  </person>
  <person>
    <FirstName>Peter</FirstName>
    <LastName>Michaels</LastName>
    <AmountInt>7</AmountInt>
  </person>
  <person>
    <FirstName>James</FirstName>
    <LastName>Bond</LastName>
    <AmountInt>18</AmountInt>
  </person>
  <person>
    <FirstName>Kyle</FirstName>
    <LastName>Jackson</LastName>
    <AmountInt>20</AmountInt>
  </person>
  <person>
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
    <AmountInt>21</AmountInt>
  </person>
  <person>
    <FirstName>Suzie</FirstName>
    <LastName>South</LastName>
    <AmountInt>31</AmountInt>
  </person>
  <person>
    <FirstName>Julie</FirstName>
    <LastName>Johnson</LastName>
    <AmountInt>45</AmountInt>
  </person>
</allpersons>