The query below shows how to create an XML document using FOR XML from a table (based on Sql Server 2000). To start with, run the sql script further below to create the sample table, and populate it with some data. Once the select script is run, data will be returned in the form:
<Employees>
<Employee>
<field1></field1>
...
</Employee>
</Employees>
Query
select 1 as Tag,
NULL as Parent,
NULL as [Employees!1!Employee!xml],
NULL as [Employee!2!OfficerId!xml],
NULL as [Employee!2!GivenName!xml],
NULL as [Employee!2!Surname!xml],
NULL as [Employee!2!LoginCode!xml],
NULL as [Employee!2!IsActive!xml]
UNION ALL
select 2,
1,
NULL,
Officer_ID,
Officer_Given_Names,
Officer_Surname,
CRTS_Login_Code,
Is_Active
from staging.dbo.tbl_Officer
for xml explicit
Table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Officer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Officer]
GO
CREATE TABLE [dbo].[tbl_Officer] (
[Officer_ID] [int] NOT NULL ,
[Officer_Given_Names] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Officer_Surname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CRTS_Login_Code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Is_Active] [tinyint] NOT NULL
) ON [PRIMARY]
GO