Solved

Converting SQLDataReader to Recordset to save as XML file

Posted on 2004-04-19
6
4,885 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Preface In the first article: A Better Website Login System (http://www.experts-exchange.com/A_2902.html) I introduced the EE Collaborative Login System and its intended purpose. In this article I will discuss some of the design consideratio…
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
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…

751 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