[XML-DEV Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message]

RE: SQL DTD and related questions

• To: <xml-dev@l...>
• Subject: RE: SQL DTD and related questions
• From: "Larry Fillion" <larry.fillion@c...>
• Date: Wed, 3 Apr 2002 13:19:42 -0500
• Importance: Normal
• In-reply-to: <3CAABBEF.68BEB435@r...>

I would like to know if someone has written a DTD or
Schema for SQL92? I personally think that the following
does not give you too much freedom:
<Statement>INSERT INTO Order (Number, Date, Customer) VALUES (123,
2/4/02, 'XYZ Corp.')</Statement>

I think that there might be more granularity like:
(Of course a DTD would define the correct language).

<Statement>
	<Command name = "INSERT" />
	<Tables>
		<Table name = "Order">
			<Column name = "Number">123</Column>
			<Column name = "Date">2/4/02</Column>
			<Column name = "Customer">XYZ Corp.</Column>
		</Table>
	</Tables>
</Statement>

Of course this brings up many issues.

What about the database type? Oracle vs. MSSQL? The way that the
client has to connect to the DB may be different unless you are
using DSNs or Pools through J2EE, ...
What about username/password? These need to be passed.
What about database name?
Sequences are not the same for each DB?
What about Date formats?
Maybe the data type needs to be sent down with the column?
There would be 100 issues?
What is the goal? Is the goal to call SQL over HTTP in a generic
way? This seems to be an implementation of SOAP? (I am not
saying that as a bad thing -  think it is good)

I think that this would be an interesting project to do.

I think that it would be beneficial to write a layer to do the
INSERTS, DELETES, UPDATES, ... over HTTP as XML. I think that
the scope would have to be defined? Is this going to wrap SQL92
or is it going to work for only MSSQL?

And of course, once you have the DTD/Schema done, you then need
to write the Servlet/ColdFusion/ASP/... to parse the packages
and do the right thing.

Is Microsoft or Oracle coming up with this DTD?

	Larry


-----Original Message-----
From: Ronald Bourret [mailto:rpbourret@r...]
Sent: Wednesday, April 03, 2002 3:23 AM
To: Márcio Fernando Keller
Cc: xml-dev@l...
Subject: Re:  SQL DTD and related questions


Do you mean something like the following? Transform the XML document:

   <Order>
      <Number>123</Number>
      <Date>2/4/02</Date>
      <Customer>XYZ Corp.</Customer>
      <Item>
         <Number>1</Number>
         <Part>14-3</Part>
         <Quantity>12</Quantity>
      </Item>
      <Item>
         <Number>2</Number>
         <Part>347</Part>
         <Quantity>23</Quantity>
      </Item>
   </Order>

into the document:

   <SQL>
      <Statement>INSERT INTO Order (Number, Date, Customer) VALUES (123,
2/4/02, 'XYZ Corp.')</Statement>
      <Statement>INSERT INTO Item (Number, Part, Quantity) VALUES (1,
'14-3', 12)</Statement>
      <Statement>INSERT INTO Item (Number, Part, Quantity) VALUES (2,
'347', 23)</Statement>
   </SQL>

I have seen this suggested several times, but I don't know anybody who
has actually done it. Note that this strategy has a number of problems:

1) It assumes that XML names and database names are the same. This is
unlikely. First, table names are often plural (e.g. Orders) while XML
names are often singular (Order). Second, database names are often all
upper case or all lower case, while XML names are often mixed case.

2) There is no way to detect data type from an XML document. For
example, how do I know that part number 347 is a string and not an
integer?

3) This does not use prepared statements and is therefore not very
efficient. You could construct statements with parameters, but if a
complex element has optional children, doing so might be difficult.

4) There is no way to tell from the XML document how to link related
tables. For example, the order number might be repeated in the items
table as a foreign key, but there is no way to determine this.

etc.

Note that many (most?) of these problems can be solved by using a
different XSLT document for each class of XML documents. You could
probably even write a small XML=>DB mapping language that could be
transformed into an XSLT document. The resulting XSLT document could be
used to do what you want.

Is there a reason you can't use one of the many products already on the
market that transfers data between XML documents and relational
databases?

-- Ron

> Márcio Fernando Keller wrote:
>
> Exist a standard dtd for SQL statements?
>
> I will transform(xslt) xml documents into xml docs with sql
> statements. Is this useful, advantageous? Sugestions?

-----------------------------------------------------------------
The xml-dev list is sponsored by XML.org <http://www.xml.org>, an
initiative of OASIS <http://www.oasis-open.org>

The list archives are at http://lists.xml.org/archives/xml-dev/

To subscribe or unsubscribe from this list use the subscription
manager: <http://lists.xml.org/ob/adm.pl>

• References:
  • Re: SQL DTD and related questions
    • From: Ronald Bourret <rpbourret@r...>

• Prev by Date: Re: WD for Namespaces 1.1
• Next by Date: Re: WD for Namespaces 1.1
• Previous by thread: Re: SQL DTD and related questions
• Next by thread: Re: SQL DTD and related questions
• Index(es):
  • Date
  • Thread