Geeks With Blogs
Shailen Sukul blog

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

 

Posted on Thursday, September 7, 2006 11:20 AM Database | Back to top


Comments on this post: How to use FOR XML to return an Xml Document with a root element

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Shailen Sukul | Powered by: GeeksWithBlogs.net