Solved

Converting SQLDataReader to Recordset to save as XML file

Posted on 2004-04-19
6
4,732 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Most of the sites are being standardized with W3C Web Standards. W3C provides lot of web standard services to the web. They have the web specification, process and documentation for all the web standards. You can apply HTML, CSS and Accessibility st…
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…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

706 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

15 Experts available now in Live!

Get 1:1 Help Now