XML to export hudge data into excel

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

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

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

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

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

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