Cart
|
[XML-DEV Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message] Re: XML into SQL and out again
 <Quote> whether to use attribute or element based tagging here.. </Quote> IMHO, this should not be an "or" type of choice - elements should be used to represent data, attributes to qualify (modify) metadata. In terms of english, one may think (in a very basic sense) of attributes as adjectives, and elements as nouns. Kind Regards, Joe Chiusano Booz | Allen | Hamilton Neile Bermudes wrote: > > 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@d...] > Sent: Friday, March 28, 2003 3:01 PM > To: Neile Bermudes; xml-dev@l... > Subject: Re: 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'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 > > ----------------------------------------------------------------- > 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> begin:vcard n:Chiusano;Joseph tel;work:(703) 902-6923 x-mozilla-html:FALSE url:www.bah.com org:Booz | Allen | Hamilton;IT Digital Strategies Team adr:;;8283 Greensboro Drive;McLean;VA;22012; version:2.1 email;internet:chiusano_joseph@b... title:Senior Consultant fn:Joseph M. Chiusano end:vcard 
|
PURCHASE STYLUS STUDIO ONLINE TODAY!Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced! 
Download The World's Best XML IDE!Accelerate XML development with our award-winning XML IDE - Download a free trial today! Subscribe in XML format
|
|||||||||
