XML to export hudge data into excel

Posted by Man2vb under Sql Server on 6/7/2010 | Views : 2189 | Status : [Member] | Replies : 6
Can any buddy tell me how to generate XML for exporting data into excel.




Responses

Posted by: Vuyiswamb on: 6/7/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
You can use

select Column1,Column2,Column3
for xml raw


and you can save it somewhere.





Thank you for posting at Dotnetfunda
[Administrator]

Man2vb, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Deeraj on: 6/7/2010 [Member] Starter

Up
0
Down

There are several ways to do it:

Option #1:

Works with File->Open->Raw.xml

Select C.CategoryName, P.* from Category C inner join Product P on C.CategoryID = P.CategoryID

for xml raw('Category'), Root('Category')


Raw.xml:
<Category>
<Category CategoryName="Clothing" ProductID="1" ProductName="T-Shirt XL" Description="T-Shirt Extra Large" CategoryID="1" />
<Category CategoryName="Clothing" ProductID="2" ProductName="T-Shirt XL" Description="T-Shirt Extra Large" CategoryID="2" />
</Category>

Excel file open output:
CategoryName ProductID ProductName Description CategoryID
Clothing 1 T-Shirt XL T-Shirt Extra Large 1
Clothing 2 T-Shirt XL T-Shirt Extra Large 2

Option #2:

Works with Data->XML->Import->Auto.xml
Select C.CategoryName, P.* from Category C inner join Product P on C.CategoryID = P.CategoryID

for xml Auto, Root('Category')


Auto.xml:
<Category>
<C CategoryName="Clothing">
<P ProductID="1" ProductName="T-Shirt XL" Description="T-Shirt Extra Large" CategoryID="1" />
<P ProductID="2" ProductName="T-Shirt XL" Description="T-Shirt Extra Large" CategoryID="2" />
</C>
</Category>

Excel import output:
CategoryName ProductID ProductName Description CategoryID
Clothing 1 T-Shirt XL T-Shirt Extra Large 1
Clothing 2 T-Shirt XL T-Shirt Extra Large 2

Option #3:

To directly save the output to excel file:

open the command prompt and use the following command:

bcp "Select C.CategoryName, P.* from ADONETEntityTransactionTest.dbo.Category  C inner join ADONETEntityTransactionTest.dbo.Product P  on C.CategoryID = P.CategoryID" QueryOut d:\temp\ProductAndCategory.xls -T -S sr-corp-ezpdev\sqlexpress -c


Hope that helps!


Man2vb, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Deeraj on: 6/7/2010 [Member] Starter

Up
0
Down

If you want, please check this one out:

http://www.dotnetfunda.com/articles/article119.aspx


Man2vb, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Man2vb on: 6/8/2010 [Member] Starter

Up
0
Down
Thanks a lot for your reply,
this works fine on SQL server,
but my porblem is that my SQL server is on different machine and my web server on different machine and I want to provied facility on web server to export huge data into excel. So please suggest me what can I do for this task

Man2vb, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Deeraj on: 6/8/2010 [Member] Starter

Up
0
Down
Now that, this thread was posted under 'SQL Server' category, the answers were specific to SQL Server.

The proposed solutions work well on the client side as well. Meaning, you are using SSMS on a different machine that is not a DB server.

Are you looking at exporting data in a gridview to excel?

Please let me know.

Thanks,
Dheeraj.

Man2vb, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Man2vb on: 6/8/2010 [Member] Starter

Up
0
Down
My problem is that, I want to give a huge data from Database to user, let it be comes from directly from database or it comes through any front end application.

Man2vb, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response