Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 550
  • Last Modified:

Write data from SQL Server 2008 to XML file using C#

Hi,

I like to get the stucture from a SQL Server 2008 table, here's my idea about:
SELECT C.COLUMN_NAME AS [Column]
FROM INFORMATION_SCHEMA.Tables T JOIN INFORMATION_SCHEMA.Columns C
ON T.TABLE_NAME = C.TABLE_NAME
WHERE T.TABLE_NAME ='person'

Output:
FirstName
LastName

Now I like to get all data from the table:
SELECT * FROM 'person'

Output:
John | Doe
Fred | Smith

My output should looks like:
<Table>
   <Person>
     <FirstName>John</FirstName>
     <LastName>Doe</LastName>
   </Person>
   <Person>
    <FirstName>Fred</FirstName>
     <LastName>Smith</LastName>
   </Person>
</Table>

The only idea I would have to make some C# code with many loops:
foreach() // all rows
{
// write start element
foreach() // all columns
{
   // write element
}
}

For sure I think this will work but do you've any better solution for because I think with big tables it'll need a lot of time for ...

Thanks,

Andre
0
andre72
Asked:
andre72
1 Solution
 
Anthony PerkinsCommented:
Have you looked at the FOR XML clause?
0
 
anarki_jimbelCommented:
What about reading data into a DataSet and saving it in a XML format?

Have a look:
http://msdn.microsoft.com/en-us/library/zx8h06sz.aspx

If you need to write just separate tables you can do so:
http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic39227.aspx

Just be aware you may loose some data if you have relations etc.
0
 
andre72Author Commented:
@acperkins
No, I don't now about. Do you have a URL in the net with some information about?

@anarki jimbel
Looks very intresting - I'll have a look for

Thanks,

Andre
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
0
 
andre72Author Commented:
Thanks rrjegan17 - any way to save this to a file than?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes you have..
In your SSMS, Click Query in Menu --> Results to --> Results to File.

When you run the query it will save the resultset in a file.
Rename that *.rpt file to *.xml and that's done.
0
 
andre72Author Commented:
No, I'm sorry about for missunderstanding - I need to do this programmatically ...
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Using OPENROWSET or OPENDATASOURCE, you can achieve that.

Refer Books Online for more info regarding that.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now