• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5334
  • Last Modified:

Converting SQLDataReader to Recordset to save as XML file

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
r_i_x
Asked:
r_i_x
  • 4
1 Solution
 
chaniewskimCommented:
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
 
chaniewskimCommented:
OK, you don't have to add <ROOT> element, I needed that for my solution (and copy-pasted here)
0
 
chaniewskimCommented:
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
vascovCommented:
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
 
chaniewskimCommented:
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
 
r_i_xAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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