Solved

Converting SQLDataReader to Recordset to save as XML file

Posted on 2004-04-19
6
4,777 Views
Last Modified: 2013-11-19
I have data in a SQL Server database and want it as a recordset in XML.

How can you convert a SQLDataReader/DataAdapter to a recordset?

OR

Is that the most appropriate way to do this?
0
Comment
Question by:r_i_x
  • 4
6 Comments
 
LVL 5

Accepted Solution

by:
chaniewskim earned 500 total points
ID: 10864572
You can return it from SQL Server as XML:

SELECT * FROM MyTable
FOR XML RAW

or any of:
FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64]

where mode is RAW, AUTO or EXPLICIT

XMLDATA to return inline XML-Data schema together with XML
ELEMENTS to return columns as subelements not attributes (only working in AUTO mode)
BINARY BASE64 to return data encoded as binary (needed to return binary data from database)


RAW

Takes the query result and transforms each row in the result set into an XML element with a generic identifier <row /> as the element tag.

AUTO

Returns query results in a simple, nested XML tree. Each table in the FROM clause, for which at least one column is listed in the SELECT clause, is represented as an XML element. The columns listed in the SELECT clause are mapped to the appropriate element attributes.

EXPLICIT

Specifies that the shape of the resulting XML tree is defined explicitly. Using this mode, queries must be written in a particular way so that additional information about the desired nesting is specified explicitly. Look for it in MSDN Library.




Next, you have to retrieve those data a normal way using SqlDataReader. When you have that datareader (ex. drXml), you do that to read them and put into XML document object:

private XmlDocument Xml;
System.Text.StringBuilder strBuilder = new System.Text.StringBuilder( "" );
//add root tag as top xml
strBuilder.Append( "<ROOT>" );
while( drXml.Read() )
{
     strBuilder.Append( drXml[0].ToString () );                    
}
strBuilder.Append( "</ROOT>" );
Xml.LoadXml( strBuilder.ToString() );



0
 
LVL 5

Expert Comment

by:chaniewskim
ID: 10864577
OK, you don't have to add <ROOT> element, I needed that for my solution (and copy-pasted here)
0
 
LVL 5

Expert Comment

by:chaniewskim
ID: 10864615
oops
C# :)

There is VB version (if it doesn't work - sorry, inline translation ;) ):

private Xml As XmlDocument
private strBuilder As System.Text.StringBuilder = new System.Text.StringBuilder( "" )
while drXml.Read()
     strBuilder.Append( drXml(0).ToString () )
wend
Xml.LoadXml( strBuilder.ToString() )
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 12

Expert Comment

by:vascov
ID: 10864637
Hi, have a look at:

HOW TO: Convert an ADO.NET DataSet to ADO Recordset in Visual Basic .NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;316337

Or, in case you're refering to a recordset in xml as the xml that DataSet returns, just do:

DataSet ds; ...
ds.GetXml();
or
ds.WriteXml( ... )

hth

Vasco
0
 
LVL 5

Expert Comment

by:chaniewskim
ID: 10864655
To clarify:
If you already have data in DataSet, then vascov's solution is best. If it is still in database, or you're using DataReader, then look at my solution.

Does it help you?
0
 
LVL 1

Author Comment

by:r_i_x
ID: 10864853
I tried both of these and because I want to write this to a file and then open it (on another machine) as a recordset, I've choosed chaniewskim's solution. Thanks to all for your input.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now