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

Re: SQL DTD and related questions

• To: Márcio Fernando Keller <marcio@a...>
• Subject: Re: SQL DTD and related questions
• From: Ronald Bourret <rpbourret@r...>
• Date: Wed, 03 Apr 2002 00:23:11 -0800
• Cc: xml-dev@l...
• References: <00ed01c1da68$8ec95e20$0100007f@a...>

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?

• Follow-Ups:
  • RE: SQL DTD and related questions
    • From: "Larry Fillion" <larry.fillion@c...>

• References:
  • SQL DTD and related questions
    • From: Márcio Fernando Keller <marcio@a...>

• Prev by Date: RFC 3252 - XML format for IP, TCP, UDP
• Next by Date: Re: Proposal for a new RDDL purpose: canonicalization
• Previous by thread: SQL DTD and related questions
• Next by thread: RE: SQL DTD and related questions
• Index(es):
  • Date
  • Thread