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

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
chris@ambler.net

William
Wed Sep 17 12:13:10 CDT 2003

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
"Christopher Ambler" <chris@ambler.net> wrote in message
news:uWcKq2TfDHA.2348@TK2MSFTNGP12.phx.gbl...
> 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
> chris@ambler.net
>
>

Christopher
Wed Sep 17 12:23:09 CDT 2003


"William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
news:%23TsJj6TfDHA.3076@tk2msftngp13.phx.gbl...
> 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,

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

William
Wed Sep 17 13:08:02 CDT 2003

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
"Christopher Ambler" <chris@ambler.net> wrote in message
news:ePsieBUfDHA.1712@TK2MSFTNGP11.phx.gbl...
>
> "William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
> news:%23TsJj6TfDHA.3076@tk2msftngp13.phx.gbl...
> > 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,
>
> 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
>
>

Eirik
Wed Sep 17 13:20:18 CDT 2003

One alternative, not necessarily the best, is to use an XSL-transformation
(XSLT) if you can't do this any other way.

Eirik M

"Christopher Ambler" <chris@ambler.net> wrote in message
news:ePsieBUfDHA.1712@TK2MSFTNGP11.phx.gbl...
>
> "William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
> news:%23TsJj6TfDHA.3076@tk2msftngp13.phx.gbl...
> > 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,
>
> 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
>
>

Christopher
Wed Sep 17 13:59:17 CDT 2003


"William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
news:%231dvNZUfDHA.2172@TK2MSFTNGP09.phx.gbl...
> 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.

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

konsu
Wed Sep 17 19:39:53 CDT 2003

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


"Christopher Ambler" <chris@ambler.net> wrote in message
news:uWcKq2TfDHA.2348@TK2MSFTNGP12.phx.gbl...
> 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
> chris@ambler.net
>
>