Exporting Select Query's content to XML

USE [Northwind]
GO
/****** Object: Table [dbo].[test] Script Date: 07/29/2008 17:27:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
[Location] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Department] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Positions] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Roll] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Experience] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Educational Experience] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


SELECT
Location,
Department,
Positions ,
Roll,
Experience,
[Educational Experience] as [EducationalExperience]
FROM test
FOR XML PATH('Opening'), ROOT('Openings')



<Openings>

<Opening>

<Location>Chennai</Location>

<Department>Marketing</Department>

<Positions>Sales</Positions>

<Roll>Off Roll</Roll>

<Experience>3 yrs</Experience>

</Opening>

<Opening>

<Location>Mangalore</Location>

<Department>Sales</Department>

<Positions>Superior officer</Positions>

<Roll>On Roll </Roll>

<Experience>1 Yrs</Experience>

<EducationalExperience>Graduate</EducationalExperience>

</Opening>

</Openings>

No comments: