OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.

 


Help: OASIS Mailing Lists Help | MarkMail Help

 


 

   RE: [xml-dev] XML into SQL and out again

[ Lists Home | Date Index | Thread Index ]

Hi Jonathan,

Firstly, thank you very much for your informative reply- very much
appreciated. I think the SQL/XML path looks like the most suited.

The database I'll be using is MS SQL Server 2000. The database will exchange
information with a Quark document, very infrequently though. Firstly I will
need to export all info in the original quark file to XML, and put this in a
SQL database. I will only need to do the reverse once a year (i.e. generate
XML from the SQL data and out it back into Quark). So it isn't a regular
thing. The xml/sql code example you gave in your e-mail looks just right.

I guess my main concern at the moment is whether to use attribute or element
based tagging here...i still don't know what the exact implications of my
choice will be...what I need is a bunch of scenarios/examples to look at so
I can get a good idea of different ways I can do this...

Thanks a lot

Neile
 
-----Original Message-----
From: Jonathan Robie [mailto:jonathan.robie@datadirect-technologies.com] 
Sent: Friday, March 28, 2003 3:01 PM
To: Neile Bermudes; xml-dev@lists.xml.org
Subject: Re: [xml-dev] XML into SQL and out again

Hi Neale,

At 03:17 PM 3/27/2003 +0000, Neile Bermudes wrote:

>What I would like to do: first, export all the information in Quark into a 
>SQL database, having exactly the same information in both. Then, as 
>details change, just update the database. Once a year, when we print out 
>the Staff Directory booklet, I would like to then like to get the updated 
>information into Quark so it can be printed.

First, if you ping me in about three or four weeks, I may be able to give 
you an early draft of a paper that compares doing this by hand using the 
DOM and SQL queries, proprietary vendor extensions from major database 
vendors, using SQL/XML to generate XML from relational data, and XQuery on 
relational views.

Does your solution need to work for multiple database vendors, or is there 
one particular brand of database that you care about? This makes a big 
difference. How do you feel about tedious programming that may not run all 
that efficiently - is this a process that just has to run once a year, 
where the data is not all that complex? Doing it by hand may not be too bad.

If your data isn't too complex, you can do this by hand. This does get 
complex fast.

Every major database vendor has their own proprietary approaches that solve 
this problem. Some of these solutions are better than others, most are a 
bit ad hoc.

If you want a solution that works for more than one database vendor, there 
are two standards to consider: XQuery and SQL/XML.

SQL/XML is a set of proposed extensions to the SQL standard that has been 
implemented by Oracle and IBM, and is expected to be adopted in 2003. My 
company has a cross-database implementation based on an early version of 
SQL/XML, and will soon have a new release that conforms to the latest 
drafts. SQL/XML allows you to write SQL queries that return XML as their 
results. Suppose you have a table like this:

EmpId       LastName     FirstName
-----       ----------   -------------------------
1           Marshall     Marc
2           Ayers        Brian
3           Simpson      Joanna
4           O'Donnel     Gavin


You can create XML from this data using a query like this:


select
     xmlelement ( name "employee",
         xmlattributes (e.EmpId as "id"),
         xmlelement ( name "names",
             xmlelement ( name "first", e.FirstName),
             xmlelement ( name "last", e.LastName)
         )
     )
from  Employees   e

The result of the above query would be:

<employee id='1'>
   <names>
     <first>Marc</first>
     <last>Marshall</last>
   </names>
</employee>
<employee id='2'>
   <names>
     <first>Brian</first>
     <last>Ayers</last>
   </names>
</employee>
<employee id='3'>
   <names>
     <first>Joanna</first>
     <last>Simpson</last>
   </names>
</employee>
<employee id='4'>
   <names>
     <first>Gavin</first>
     <last>O&apos;Donnel</last>
   </names>
</employee>

This is pretty easy for SQL programmers to learn. It may not be obvious 
from this simple query, but when queries more complex, involving grouping, 
multiple joins, and mapping the tabular representation of SQL into various 
hierarchies, SQL/XML can be a significant timesaver - and more efficient at 
run-time than programming it yourself.

My company, DataDirect Technologies, has extended SQL/XML with updates that 
use XPath to identify the data to be used for updating a relational 
database. You could use this to insert the data from your XML files with a 
simple Update statement. Our current syntax is not quite conformant with 
the standard, but in the second quarter of this year, we will be releasing 
a version that is.

The other standard you should care about is XQuery, which takes XML as 
input and output. Many vendors are supplying an XML view of a relational 
database. I think this will be a really good way to go fairly soon, as 
XQuery implementations on relational databases become more mature. Most 
relational database vendors will be offering this, as well as third 
parties, including my own company.

I'm out of time for this right now, but here are some resources you may be 
interested in:

Ron Bourret's XML and Databases page:
http://www.rpbourret.com/xml/XMLAndDatabases.htm

XQuery:
http://www.w3.org/xml/query.html

SQL/XML: Two readable articles:
http://www.acm.org/sigmod/record/issues/0109/standards.pdf
http://www.acm.org/sigmod/record/issues/0206/standard.pdf

SQL/XML: The current drafts of the proposed standard:
ftp://sqlstandards.org/SC32/WG3/Progression_Documents/Informal_working_draft
s/

Hope that helps!

Jonathan


-----------------------------------------------------------------
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>


_____________________________________________________________________
This message has been checked for all known viruses by the 
MessageLabs Virus Scanning Service. For further information about managed
virus detection visit Sirocom at
http://www.sirocom.com/newsfram.cfm?NewsID=51




 

News | XML in Industry | Calendar | XML Registry
Marketplace | Resources | MyXML.org | Sponsors | Privacy Statement

Copyright 2001 XML.org. This site is hosted by OASIS