Mapping Nested XML to Relational Tables

[Fred Chateau]

I'm having a problem translating nested XML to relational tables and would
appreciate anyone's advice with it.

I've written some code in C# that retrieves a fairly complex XML document
structure from a remote server and loads in into a dataset. Originally, the
complete XML document would not load because of conflicts caused by nested
nodes of the same name in different parts of the document.

I generated a schema by separating out parts of the XML which would load
successfully, eliminating the conflicting nodes in the schema, and then
re-integrating the XML schema without the conflicting nodes. To my surprise
this worked fairly well. I can now load the incoming XML into the dataset,
minus the conflicting nodes and their accompanying data.

So I was wondering if there was a way to set up a mapping of the identically
named nodes so that I can recover the data in those parts of the document.
Since the document will not load with the complete schema, I'm assuming this
cannot be accomplished using DataSet methods, but I was hoping for a way to
do it within the schema itself.

An example fragment is listed below:

<overview>
< . . . >

</. . . >
<nearbyLocations>
<restaurants>
<location>
<name>Red Lobster</name>
<distance>1/2 mile</distance>
</location>
<location>
<name>Olive Garden</name>
<distance>1 mile</distance>
</location>
</restaurants>
<attractions>
<location>
<name>Walt Disney World</name>
<distance>8 miles</distance>
</location>
</attractions>
</nearbyLocations>
< . . . >
</. . . >
</overview>

 

[Oleg Tkachenko]

I'm having a problem translating nested XML to relational tables and would
appreciate anyone's advice with it.

Sorry for silly question, but are you really need to constrain XML to
relational view? XML is much wider than relational model, so limiting to
that model only makes sense in specific scenarios (such as RDBMS
involved). Otherwise System.Xml classes allows you to process any XML
documents.

 

[Fred Chateau]

...

Sorry for silly question, but are you really need to constrain XML to

relational view? XML is much wider than relational model, so limiting to
that model only makes sense in specific scenarios (such as RDBMS involved).
Otherwise System.Xml classes allows you to process any XML documents.

I have over 56,000 XML documents that must be retrieved from a remote server
on a random demand basis. When the document is received, the plan is to
store each document as one record in a relational database with a timestamp.

When a request comes in for a particular document, the application first
checks the database to see if the record has already been stored and is less
than one week old. If so it uses the database copy. If not, it retrieves a
new copy from the remote server, serves the record, and stores the new copy
in place of the older one in the database with a new timestamp, and so on .
.. .

The timing of document updates is not critical, but the record lookup must
be very fast, because the application is assembling Web pages on-the-fly
from the record contents. Not all of the record contents is used per
request. Sometimes one XML fragment is required while another request
requires a different part of the document, or even various nodes spread
throughout the document. Some nodes are one level deep while others may have
up to 100 child elements. I have no control whatsoever on how the XML
document is structured, rather I must deal with a pre-existing structure
that does not conform to W3C standards.

Currently, for cost reasons, having invested all my budget in new hardware
and Windows 2003 Server, I am limited to using either a MySQL database or
storing the XML documents individually as files and parsing them each time
they're needed.

Do you see a better way?

 

[Fred Chateau]

...

Currently, for cost reasons, having invested all my budget in new hardware

and Windows 2003 Server, I am limited to using either a MySQL database or
storing the XML documents individually as files and parsing them each time
they're needed.

While I'm on the subject, I thought I'd mention that although I'd really
like to have SQL Server for this project, I have no regrets spending my
budget on Windows 2003 Server. I believe that was the best investment in
software I've ever made.

 

[scorpion53061]

While I'm on the subject, I thought I'd mention that although I'd really

like to have SQL Server for this project, I have no regrets spending my
budget on Windows 2003 Server. I believe that was the best investment in
software I've ever made.

tell me why. I was thinking about purchasing WIN2003......will my learning
curve be huge? Will my old statements, DTS, stored procedures still work
etc?

 

[Fred Chateau]

...

tell me why. I was thinking about purchasing WIN2003......will my

learning curve be huge? Will my old statements, DTS, stored procedures still
work etc?

For my purposes it's perfect . . .

We're a small business and we don't have an IT administrator to take care of
our servers. I am a developer and I want to spend my time developing
software, not administering machines. We installed Windows 2003 Server soon
after it was released. I'm not sure how long ago that was but it seems like
over 6 months now.

The server basically runs itself. I did spend some initial time during the
first couple of weeks setting it up the way I wanted it, but I have not
touched it since. In the six months or so since we put it online it has
crashed only once, and that was while trying to set up MySQL on it. I fixed
the problem and it's back to business as usual.

I have never experienced an operating system that ran perfectly before. It
seems like they all have something going on to aggravate the hell out of
you. This one doesn't. With the exception of setting up MySQL, there has
never even been an error in the event logs . . . never . . . and we push
them hard.

Our servers handle everything we need. Their primary function is IIS6, which
as a developer I can attest is the coolest Web server available anywhere. We
have 128 IP addresses attached to the server, of which around 30 are
currently in use. We run IIS as a Web server, Web service, POP3 mail server,
SMTP server, FrontPage 2002 Extensions, Windows SharePoint Services, IPSec .
.. . we even run our DNS on it.

It is attached directly to the public Internet without a firewall. I use
IPSec and IP filtering to limit access and we have had absolutely no
security problems so far.

We have one instance of MSDE running with around ten databases, and now
we're running several MySQL Server databases as well.

The Web server is as fast as any server I have ever seen, including servers
that employ separate SQL databases on another machine, and nothing ever goes
wrong. I say nothing but actually we have had a few minor problems with IIS
and get this . . . the problems corrected themselves. Go figure . . .

At this point you couldn't pay me to use another operating system on a
server. Give me a copy of anything other than Windows 2003 Server and I will
pass it on to someone else who's interested. I'm not. I have never been as
impressed with a software product as this one. I don't know how they did it,
but they far, far, exceeded my expectations.

To be fair, I should point out that as a small business I have the luxury of
not having to worry about legacy applications. Everything that's running on
the servers is designed to run on Windows 2003 Server, no backward
compatibility to worry about . . . I don't even run .NET 1.0, although we
do have several ASP sites in addition to .NET 1.1.

Like I said . . . best investment I ever made in software.