Best way to take DB output and turn it into XML?

  • Thread starter Thread starter Christopher Ambler
  • Start date Start date
C

Christopher Ambler

This is one of those "what's my best way to do this?" questions...

I've written a shell for an ASP.NET web service. This service takes in a
single string and makes a database call to get information. The information
comes back as a whole slew of data. Numerous recordsets with a variable
number of rows each, a number of individual data items sent back as output
parameters to stored procedures... the works.

What I need to do is package up all of this information into XML, in a
format that I get to define. This chunk of XML will be sent back out from
the web service. Consumers of the web service will then use the XML to do
what they wish with it. Some will use the data to populate windows forms,
some will apply XSLT to it for very simple formatted text output, and the
like.

The web service is very simple - takes in a string and returns a string (the
return string being the XML blob). I presume that this is the easiest and
most robust way to expose this, right?

So my first thought was to take the database output and build an XMLDocument
from scratch as I go along. I do not have the luxury of getting the database
to give me my results in XML, so that's out of the question. Building the
XMLDocument and populating it is pretty easy, but tedious.

But then I wondered - is it possible to take the recordsets that I get back
and have them serialize to an XML format of my choosing? Would this be any
easier?

Or am I overlooking an obvious solution?

Many thanks in advance!

Christopher
(e-mail address removed)
 
You can serialize a Dataset by using
DataSet.WriteXML("SomePath\someFile.xml") now it's in XML. From there, you
can deserialize it with DataSet.ReadXML("SomePath\someFile.xml")

This is the simplest form but you can mold your dataset hwoever you want and
serialize accordingly.

HTH,

Bill
 
William Ryan said:
You can serialize a Dataset by using
DataSet.WriteXML("SomePath\someFile.xml") now it's in XML. From there, you
can deserialize it with DataSet.ReadXML("SomePath\someFile.xml")

This is the simplest form but you can mold your dataset hwoever you want and
serialize accordingly.

HTH,
Click to expand...

So let me ask you two questions

1. Can I serialize it to a string, so I can send it back out with the web
service? I don't want to write it to a file...

2. Can I specify the XML structure? That is, let's say that I have the
DataSet with 3 rows, and each row has 2 columns.

I want the XML to look like this, then:

<MyDataSetName>
<MyRowName>
<Column1>Some Data A</Column1>
<Column2>More Data A</Column2>
</MyRowName>
<MyRowName>
<Column1>Some Data B</Column1>
<Column2>More Data B</Column2>
</MyRowName>
<MyRowName>
<Column1>Some Data C</Column1>
<Column2>More Data C</Column2>
</MyRowName>
</MyDataSetName>

All of those tags have names that I want to specify, of course.

Am I on the right track, then? Would I want to use GetXML() instead of
WriteXML() for this purpose?

As an aside... I just need to point out that this is fascinating, and most
incredible to be able to learn how to do this in the course of developing
code. I wake up every morning amazed that I get paid to learn, and that
there are so many helpful people on the net - everyone working together to
learn and create. Yeah, I'm being touchy-feely this morning :-)

Christopher
 
You could get the representation as a string indirectly but writing and
reading from an IO.Stream and converting it. If you don't want to use files,
Streams will get you there and they are supported as one of the 8
WriteXML/ReadXML overloads.

By Default, the DataSet is going generate the structure of the XML file, but
if you don't like it, you can restructure your DataSet. Here is an example
of one that I use (I tried to collapse them but it didn't take in the
paste.) In this instance, I have 5 columns (Work_Type, WT_Description,
Abbreviation, Title and Sync). I have 8 rows in this particular dataset.
If you look at the structure, it's almost Identical to what you want, I just
have more columns and the have different names.

The best part of all of this is that it takes very VERY little code to
accomplish this. Even if you write to a stream, you only add another 2
lines of code. So you can get an XML representation in one line of code
using a file, 3 using a stream ...

As far as your last paragraph, I agree. It's amazing how many people are
out there helping out...it's a great time to be a programmer.

Let me know if you have any problems.

<?xml version="1.0" standalone="yes" ?>
- <NewDataSet>
- <WorkTypes>
<Work_Type>1</Work_Type>
<WT_Description>Physical</WT_Description>
<Abbreviation>C & P</Abbreviation>
<Title>My Title</Title>
<Sync>true</Sync>
</WorkTypes>
+ <WorkTypes>
<Work_Type>2</Work_Type>
<WT_Description>Comprehensive & Physical</WT_Description>
<Abbreviation>C&P</Abbreviation>
<Title>Some Title</Title>
<Sync>true</Sync>
</WorkTypes>
+ <WorkTypes>
<Work_Type>3</Work_Type>
<WT_Description>Discharge Summary</WT_Description>
<Abbreviation>DS</Abbreviation>
<Title>Some Title1</Title>
<Sync>true</Sync>
</WorkTypes>
+ <WorkTypes>
<Work_Type>4</Work_Type>
<WT_Description>Progress Notes</WT_Description>
<Abbreviation>PN</Abbreviation>
<Title>Some Title3</Title>
<Sync>true</Sync>
</WorkTypes>
+ <WorkTypes>
<Work_Type>6</Work_Type>
<WT_Description>Consults</WT_Description>
<Abbreviation>Consults</Abbreviation>
<Sync>true</Sync>
</WorkTypes>
+ <WorkTypes>
<Work_Type>7</Work_Type>
<WT_Description>BillWorkType</WT_Description>
<Abbreviation>BWT</Abbreviation>
<Title>None</Title>
<Sync>true</Sync>
</WorkTypes>
+ <WorkTypes>
<Work_Type>8</Work_Type>
<WT_Description>Earl Love Work Type</WT_Description>
<Abbreviation>ELWT</Abbreviation>
<Title>ELWT</Title>
</WorkTypes>
</NewDataSet
 
One alternative, not necessarily the best, is to use an XSL-transformation
(XSLT) if you can't do this any other way.

Eirik M
 
William Ryan said:
By Default, the DataSet is going generate the structure of the XML file, but
if you don't like it, you can restructure your DataSet. Here is an example
of one that I use (I tried to collapse them but it didn't take in the
paste.) In this instance, I have 5 columns (Work_Type, WT_Description,
Abbreviation, Title and Sync). I have 8 rows in this particular dataset.
If you look at the structure, it's almost Identical to what you want, I just
have more columns and the have different names.
Click to expand...

Have you any sample code, or a pointer to the best practices to restructure
the DataSet?

Additionally, if I'm calling into a stored procedure that returns me a
number of RecordSets as well as some output parameters, what's the canonical
way to put those into the dataset? I'm after efficiency, of course :-)

I'm looking through my copy of "Applied XML Programming for Microsoft .NET"
(MSPress), chapter 9, "ADO.NET XML Data Serialization," which I suspect has
what I'm looking for - but I'd be interested in your thoughts, since you've
done this. No sense re-inventing the wheel.

Thanks again.

Christopher
 
SQLXML library has .net classes to accomplish this efficiently. it is for
free from microsoft. to control the structure of output xml, specify your
own schema.

konst
 
Back
Top