Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How To Export The XML File From The T-SQL Table.... Expand / Collapse
Author
Message
Posted Sunday, December 09, 2012 10:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 15, 2014 10:37 AM
Points: 46, Visits: 182
Hi,

I Successfully Imported the Following XML File ...

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

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



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

Regards,
Saravanan.K.D
Post #1394448
Posted Monday, December 10, 2012 10:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 15, 2014 10:37 AM
Points: 46, Visits: 182
Any One Please Help me...



Regards,
Saravanan.D
Post #1394883
Posted Tuesday, December 11, 2012 10:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 15, 2014 10:37 AM
Points: 46, Visits: 182
Any One Please Help me...
pls take it as challenges...



Regards,
Saravanan.D
Post #1395423
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse