Solved

Converting SQLDataReader to Recordset to save as XML file

Posted on 2004-04-19
6
4,831 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
The viewer will learn how to dynamically set the form action using jQuery.

808 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