i played with this, and it looks like this gets you what you are after, but it's missing the <Root> tag...not sure how to get that yet, but will pot it if I get it to work:

select * from @xmldata FOR XML RAW('Node')

the complete test set i was playing with...very helpful to look at some of the other formats to familiarize yourself:

DECLARE @idoc int
DECLARE @doc xml
DECLARE @xmldata Table   (FirstName  varchar(110),
						  LastName   varchar(210),
						  Email	     varchar(110),
						  Phone		 varchar(110),
						  Position   varchar(110),
						  Branch     varchar(110),
						  Address    varchar(110))
SET @doc ='<Root>
	<Node FirstName="Andrew" LastName="Fuller" Email="afuller@contoso.com" Phone="(205) 555 - 9898" Position="CEO" Branch="TopManagement" Address="London, 120 Hanover Sq.">
		<Node FirstName="Jeremy" LastName="Boather" Email="jboather@contoso.com" Phone="(205) 555 - 9888" Position="President QA" Branch="QA" Address="London, 120 Hanover Sq.">
			<Node FirstName="Anne" LastName="Dodsworth" Email="adodsworth@contoso.com" Phone="(205) 555 - 9887" Position="VP QA" Branch="QA" Address="London, 120 Hanover Sq.">
				<Node FirstName="Alexander" LastName="Tuckings" Email="atuckings@contoso.com" Phone="(205) 555 - 9886" Position="Team Lead Team1" Branch="QA" Address="London, 120 Hanover Sq.">
					<Node FirstName="Brenda" LastName="Smith" Email="bsmith@contoso.com" Phone="(205) 555 - 9885" Position="Senior QA" Branch="QA" Address="London, 120 Hanover Sq."/>
				</Node>
				<Node FirstName="Mary" LastName="Bird" Email="mbird@contoso.com" Phone="(205) 555 - 9885" Position="Team Lead Team2" Branch="QA" Address="London, 120 Hanover Sq."/>
			</Node>
		</Node>
		<Node FirstName="Steven" LastName="Buchanan" Email="sbuchanan@contoso.com" Phone="(205) 555 - 9897" Position="President Dev Dept." Branch="Development" Address="London, 120 Hanover Sq.">
			<Node FirstName="Robert" LastName="King" Email="rking@contoso.com" Phone="(205) 555 - 9896" Position="VP Dev Dept." Branch="Development" Address="London, 120 Hanover Sq.">
				<Node FirstName="Laura" LastName="Callahan" Email="lcallahan@contoso.com" Phone="(205) 555 - 9892" Position="Team Lead Team1" Branch="Development" Address="London, 120 Hanover Sq.">
					<Node FirstName="Jason" LastName="Roland" Email="jroland@contoso.com" Phone="(205) 555 - 9872" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
					</Node>
				</Node>
				<Node FirstName="Eric" LastName="Danstin" Email="edanstin@contoso.com" Phone="(205) 555 - 9882" Position="Team Lead Team2" Branch="Development" Address="London, 120 Hanover Sq.">
					<Node FirstName="Elizabeth" LastName="Lincoln" Email="elincoln@contoso.com" Phone="(205) 555 - 9862" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
					</Node>
					<Node FirstName="Margaret" LastName="Peacock" Email="mpeacock@contoso.com" Phone="(205) 555 - 9852" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
					</Node>
				</Node>
			</Node>
		</Node>
	</Node>
</Root>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

Insert @xmldata
SELECT    *
FROM       OPENXML (@idoc,'//Node',1)
            WITH (FirstName  varchar(110),
                  LastName   varchar(210),
                  Email	     varchar(110),
                  Phone		 varchar(110),
                  Position   varchar(110),
                  Branch     varchar(110),
                  Address    varchar(110))
                  
select * from @xmldata   

select * from @xmldata FOR XML RAW('Node')  --('Root') --RAW,AUTO,EXPLICIT,PATH
select * from @xmldata FOR XML AUTO
--select * from @xmldata FOR XML EXPLICIT 
select * from @xmldata FOR XML PATH('Root') 

select * from @xmldata FOR XML RAW('Root') ,ELEMENTS --('Root') --RAW,AUTO,EXPLICIT,PATH
select * from @xmldata FOR XML AUTO ,ELEMENTS
--select * from @xmldata FOR XML EXPLICIT 
select * from @xmldata FOR XML PATH('Root') ,ELEMENTS

sarwaanmca (12/28/2012)

---

Any One Please Help me...
please take it as Challenges...


Regards,
Saravanan.D

thanks 4 ur work... but i need the same xml file Generation....

sarwaanmca (12/10/2012)

---

Hi,

I Successfully Imported the Following XML File ...
Again I Want to Export (Generate) the Same XML File Format...
Using The Output Data (Table Value )....
How To Generate XML File...
pls Any one Help me....

-------------------------------------------------------------------------------------

DECLARE @idoc int
DECLARE @doc xml
DECLARE @xmldata Table   (FirstName  varchar(110),
						  LastName   varchar(210),
						  Email	     varchar(110),
						  Phone		 varchar(110),
						  Position   varchar(110),
						  Branch     varchar(110),
						  Address    varchar(110))
SET @doc ='<Root>
	<Node FirstName="Andrew" LastName="Fuller" Email="afuller@contoso.com" Phone="(205) 555 - 9898" Position="CEO" Branch="TopManagement" Address="London, 120 Hanover Sq.">
		<Node FirstName="Jeremy" LastName="Boather" Email="jboather@contoso.com" Phone="(205) 555 - 9888" Position="President QA" Branch="QA" Address="London, 120 Hanover Sq.">
			<Node FirstName="Anne" LastName="Dodsworth" Email="adodsworth@contoso.com" Phone="(205) 555 - 9887" Position="VP QA" Branch="QA" Address="London, 120 Hanover Sq.">
				<Node FirstName="Alexander" LastName="Tuckings" Email="atuckings@contoso.com" Phone="(205) 555 - 9886" Position="Team Lead Team1" Branch="QA" Address="London, 120 Hanover Sq.">
					<Node FirstName="Brenda" LastName="Smith" Email="bsmith@contoso.com" Phone="(205) 555 - 9885" Position="Senior QA" Branch="QA" Address="London, 120 Hanover Sq."/>
				</Node>
				<Node FirstName="Mary" LastName="Bird" Email="mbird@contoso.com" Phone="(205) 555 - 9885" Position="Team Lead Team2" Branch="QA" Address="London, 120 Hanover Sq."/>
			</Node>
		</Node>
		<Node FirstName="Steven" LastName="Buchanan" Email="sbuchanan@contoso.com" Phone="(205) 555 - 9897" Position="President Dev Dept." Branch="Development" Address="London, 120 Hanover Sq.">
			<Node FirstName="Robert" LastName="King" Email="rking@contoso.com" Phone="(205) 555 - 9896" Position="VP Dev Dept." Branch="Development" Address="London, 120 Hanover Sq.">
				<Node FirstName="Laura" LastName="Callahan" Email="lcallahan@contoso.com" Phone="(205) 555 - 9892" Position="Team Lead Team1" Branch="Development" Address="London, 120 Hanover Sq.">
					<Node FirstName="Jason" LastName="Roland" Email="jroland@contoso.com" Phone="(205) 555 - 9872" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
					</Node>
				</Node>
				<Node FirstName="Eric" LastName="Danstin" Email="edanstin@contoso.com" Phone="(205) 555 - 9882" Position="Team Lead Team2" Branch="Development" Address="London, 120 Hanover Sq.">
					<Node FirstName="Elizabeth" LastName="Lincoln" Email="elincoln@contoso.com" Phone="(205) 555 - 9862" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
					</Node>
					<Node FirstName="Margaret" LastName="Peacock" Email="mpeacock@contoso.com" Phone="(205) 555 - 9852" Position="Senior Dev" Branch="Development" Address="London, 120 Hanover Sq.">
					</Node>
				</Node>
			</Node>
		</Node>
	</Node>
</Root>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

Insert @xmldata
SELECT    *
FROM       OPENXML (@idoc,'//Node',1)
            WITH (FirstName  varchar(110),
                  LastName   varchar(210),
                  Email	     varchar(110),
                  Phone		 varchar(110),
                  Position   varchar(110),
                  Branch     varchar(110),
                  Address    varchar(110))
                  
select * from @xmldata                  
 

Regards,
Saravanan.K.D