467,134 Members | 1,043 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,134 developers. It's quick & easy.

Converting mysql query result to xml

Hi all, I'm a newbie in .net xml programming, so please be patient. And
sorry for my uncorrect english, too.
I'm going to explain my problem:

I've built a web service which responds to ferries timetable requests.
It receives an xml document with this format:
<TimeTableRequest>
<Routes>
<Route>
<Company></Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<Date>2006-12-20T13:00:00</Date>
</Route>
<Route>
<Company></Company>
<DeparturePort>pio</DeparturePort>
<ArrivalPort>ptf</ArrivalPort>
<Date>2006-12-23T15:00:00</Date>
</Route>
</Routes>
</TimeTableRequest>

I deserialize the xml file to a TimeTableRequest object through xml
methods provided by C# 2005. Then I query the mysql (rel.5) database
with those parameters and I'd need back an XML file with this format:

<TimeTableResponse>
<Solutions>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-23T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-23T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-22T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-22T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
</Solutions>
</TimeTableResponse>

Querying the db, I obtain a dataset, and then the xml from it, through
the method GetXml(), but this method gave me an object like this:

<TimeTableResponse>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-23T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-23T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
...
</TimeTableResponse>

i.e. without the "Solutions" (plural) tag. I got this xml loading the
schema file (.xsd) in the dataset, but keeps ignoring that tag. How can
obtain an exact mapping to the schema?
Thanks in advance.

Nov 27 '06 #1
  • viewed: 3032
Share:
2 Replies

create an xmlWriter
use the response dataset's createreader function to create a datareader
of the set
use the writer's writeStartElement method with "Solutions" as the
argument
use the writer's writeStartElement method with "Solution" as the
argument
now use the writer's writeElementString("Company" with
reader.item("Company"))
now use the writer's writeElementString("DeparturePort" with
reader.item("DeparturePort"))

and so on

Then Finish with the writer's writeEndElement()... twice (one for
Solution, and another for Solutions)

you can clean this up, make a function out of it that returns a stream,
an xmlDocument, Uri to a new file,a new dataSet or dataTable, raw
XML... whatever.

Since the writer is forward only and the datareader you use is a direct
push of the data, performance should be excelent. Its probably the
easiest way to do it, but you must set it up correctly, preferably as a
private or protected function or sub of a class in your Data layer.

On Nov 27, 9:09 am, carlo.gherardu...@gmail.com wrote:
Hi all, I'm a newbie in .net xml programming, so please be patient. And
sorry for my uncorrect english, too.
I'm going to explain my problem:

I've built a web service which responds to ferries timetable requests.
It receives an xml document with this format:
<TimeTableRequest>
<Routes>
<Route>
<Company></Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<Date>2006-12-20T13:00:00</Date>
</Route>
<Route>
<Company></Company>
<DeparturePort>pio</DeparturePort>
<ArrivalPort>ptf</ArrivalPort>
<Date>2006-12-23T15:00:00</Date>
</Route>
</Routes>
</TimeTableRequest>

I deserialize the xml file to a TimeTableRequest object through xml
methods provided by C# 2005. Then I query the mysql (rel.5) database
with those parameters and I'd need back an XML file with this format:

<TimeTableResponse>
<Solutions>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-23T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-23T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-22T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-22T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
</Solutions>
</TimeTableResponse>

Querying the db, I obtain a dataset, and then the xml from it, through
the method GetXml(), but this method gave me an object like this:

<TimeTableResponse>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-23T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-23T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
...
</TimeTableResponse>

i.e. without the "Solutions" (plural) tag. I got this xml loading the
schema file (.xsd) in the dataset, but keeps ignoring that tag. How can
obtain an exact mapping to the schema?
Thanks in advance.
Nov 28 '06 #2
Thank you, I'll try with that.

de*******@gmail.com ha scritto:
create an xmlWriter
use the response dataset's createreader function to create a datareader
of the set
use the writer's writeStartElement method with "Solutions" as the
argument
use the writer's writeStartElement method with "Solution" as the
argument
now use the writer's writeElementString("Company" with
reader.item("Company"))
now use the writer's writeElementString("DeparturePort" with
reader.item("DeparturePort"))

and so on

Then Finish with the writer's writeEndElement()... twice (one for
Solution, and another for Solutions)

you can clean this up, make a function out of it that returns a stream,
an xmlDocument, Uri to a new file,a new dataSet or dataTable, raw
XML... whatever.

Since the writer is forward only and the datareader you use is a direct
push of the data, performance should be excelent. Its probably the
easiest way to do it, but you must set it up correctly, preferably as a
private or protected function or sub of a class in your Data layer.

On Nov 27, 9:09 am, carlo.gherardu...@gmail.com wrote:
Hi all, I'm a newbie in .net xml programming, so please be patient. And
sorry for my uncorrect english, too.
I'm going to explain my problem:

I've built a web service which responds to ferries timetable requests.
It receives an xml document with this format:
<TimeTableRequest>
<Routes>
<Route>
<Company></Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<Date>2006-12-20T13:00:00</Date>
</Route>
<Route>
<Company></Company>
<DeparturePort>pio</DeparturePort>
<ArrivalPort>ptf</ArrivalPort>
<Date>2006-12-23T15:00:00</Date>
</Route>
</Routes>
</TimeTableRequest>

I deserialize the xml file to a TimeTableRequest object through xml
methods provided by C# 2005. Then I query the mysql (rel.5) database
with those parameters and I'd need back an XML file with this format:

<TimeTableResponse>
<Solutions>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-23T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-23T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-22T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-22T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
</Solutions>
</TimeTableResponse>

Querying the db, I obtain a dataset, and then the xml from it, through
the method GetXml(), but this method gave me an object like this:

<TimeTableResponse>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-23T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-23T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
...
</TimeTableResponse>

i.e. without the "Solutions" (plural) tag. I got this xml loading the
schema file (.xsd) in the dataset, but keeps ignoring that tag. How can
obtain an exact mapping to the schema?
Thanks in advance.
Nov 29 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Bruce A. Julseth | last post: by
reply views Thread by Phil Powell | last post: by
4 posts views Thread by Mark Wilson CPU | last post: by
3 posts views Thread by dean.elwood@gmail.com | last post: by
15 posts views Thread by harvey | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.