[
Lists Home |
Date Index |
Thread Index
]
Since you are using MS SQL Server 2000, here are some tips on your
original questions.
SS2000' main focus for handling XML is in the area of moving relational
data into XML and back. It provides two general approaches: an
XML-centric and a SQL-centric approach.
XML-centric is based on annotated schemata that provide a virtual XML
view of relational data that can be queried using XPath and updated
using updategrams and bulkload. See the website that Dare pointed you to
in order to get the latest SQLXML 3.0 webrelease.
SQL-centric is based on FOR XML queries to generate XML from relational
data and the OpenXML rowset provider to use XML to update relational
data.
One the data is in the relational table, it is just relational data and
you can use your existing relational applications to work on the data.
The FOR XML queries and/or annotated schemata will allow you to generate
XML from the data at any time.
Best regards
Michael
> -----Original Message-----
> From: Neile Bermudes [mailto:Neile.Bermudes@ecb.co.uk]
> Sent: Monday, March 31, 2003 5:56 AM
> To: 'Jonathan Robie'; xml-dev@lists.xml.org
> Subject: RE: [xml-dev] XML into SQL and out again
>
> 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'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_d
ra
> ft
> 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>
|
