DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: XML and relational databases

  1. #1
    Colin Webster Guest

    XML and relational databases


    What are my options for getting my XML data into my relational database -
    and my relational data out to XML?

    Everybody is talking about performance problems, and problems recreating
    the XML docs back to their original order.

  2. #2
    Russell Jones Guest

    Re: XML and relational databases

    The specific capabilities depend on the RDBMS you're using. In general, you
    have these options:
    1. parse the XML -- putting the data into individual columns. This gives you
    the most options for sorting and searching, but is the source of the
    performance and document recreation complaints you've been hearing.
    2. parse the XML -- putting only critical data into individual columns and
    also storing the XML document as a long text string. This method can give
    you the flexibility of #1 if you can identify all the critical fields, but
    it requires code to ensure the document doesn't get out of sync with the
    column data. It also requires modification if you change the critical field
    set and increases storage requirements, because you duplicate the critical
    data.
    3. Store the XML as long text strings. You can search within these using
    LIKE, but it's highly inefficient.You lose the ability to sort easily.
    4. Use a native XML database. See products like Tamino and Excelon. These
    can interface with your existing RDBMS.

    Russell Jones
    Sr. Web Development Editor,
    DevX.com



    "Colin Webster" <colin@edeal.com> wrote in message
    news:3b681f2f$1@news.devx.com...
    >
    > What are my options for getting my XML data into my relational database -
    > and my relational data out to XML?
    >
    > Everybody is talking about performance problems, and problems recreating
    > the XML docs back to their original order.




  3. #3
    Raj Das Guest

    Re: XML and relational databases


    This has been one of my basic questions also. Here is what I have found:

    1. Determine what the primary use/purpose of the system is. If XML is being
    used simply as a information transport, I prefer to take the performance
    hit and ignore the XML requirements during the design of my database. The
    XML documents generated are simply reports. An example of a data driven website
    where XML+XSL is used to generate the pages (either client-side or server-side).
    The meat of the system is the database. XML is a technology choice for your
    site. The same results are achieved with "traditional" data access and dynamic
    web pages (ASP, JSP, etc.)

    2. If XML is an input as well as an output of your database some design consideration
    must be payed to the impact the design of each will have on the other. I
    will usually start with the database ignoring the XML structure and then
    make necessary changes as warranted by the XML requirements. This is not
    unlike the process of applying normalization rules to the data model, start
    simple and work to the higher levels. XML input has a much greater impact
    on your system than output.

    3. I am primarily focused on SQL Server and MS, so I have been trying to
    measure the system impacts of various XML generation methods. With SQL2000
    we have native XML generation or externally code generated, e.g. COM+ components.
    Having designed and optimized my data models before XML became popular I
    have been simply trying to measure which method has the least impact on the
    database (since my external components run on a seperate server anyway).
    This has proven to be quite difficult since most of the XML documents we
    have been attempting to model are quite small in nature, representing single
    a record, usually including joins, 30-100K in size. I have been unable to
    measure any significant performance impact. As for generating large resultset,
    I try to focus the design of the system so that this is virtually impossible
    (paging of recordsets, limiting returned rows, etc.). Very rarely should
    it be necessary for any single request to require the result set to include
    all records. This is an obvious drain on the all tiers of the system as well
    as the network itself.

    SQL's native XML generation has worked quite well for me. The XML view mapper
    available in their Web release makes the task almost trivial (after the schema
    has been defined, which is anything but trivial sometimes). As for the external
    components, I have a lot of experience in writing COM/COM+ components in
    VB and VC++ so this is also straightforward for me and my company. The question
    comes down to maintainability, the native SQL tools/techniques are not "standard"
    skill sets at this time, all though that should be changing. While VB string
    manipulation is notoriously a poor performer, for the size of the documents
    we are generating a more efficient C++ component would hardly seem worth
    the additional development and long-term maintenance effort.

    I haven't had an opportunity to use some of the new XML bulk load capabilities,
    but this would hopefully be rare events in the lifetime of the system so
    I don't see myself being overly concerned with spending inordinate time optimizing
    the database or XML for one-of/rare usage.

    Raj


    "Colin Webster" <colin@edeal.com> wrote:
    >
    >What are my options for getting my XML data into my relational database

    -
    >and my relational data out to XML?
    >
    >Everybody is talking about performance problems, and problems recreating
    >the XML docs back to their original order.




  4. #4
    Stormkraft Guest

    Re: XML and relational databases


    "Colin Webster" <colin@edeal.com> wrote:
    >
    >What are my options for getting my XML data into my relational database

    -
    >and my relational data out to XML?
    >
    >Everybody is talking about performance problems, and problems recreating
    >the XML docs back to their original order.


    Colin,

    One option is a programming environment like you've never seen! (maybe)
    ... depending on your db format - either ODBC or native DBMS access is included
    - with a Visual DataFlex (current release 7.1) solution you get built in
    classes & commands for direct creation of the Microsoft ActiveX Doc objects
    that are instances of .doc objects. Parse, search, render through xslt,
    all methods are available either in VDF code or on your .asp/.html page via
    javascript/vbscript. Heck man, for a real step up, we even have a full-fledged
    industrial-quality web-server product called WebApp Server/Studio (current
    release 3.0) that integrates perfectly with your database for RAD Web development
    of all types.

    Check it out:
    http://www.dataaccess.com
    http://www.webappdeveloper.com

    Andrew Bates
    Technical Support
    Data Access Worldwide

  5. #5
    Sanjay Manchanda Guest

    Re: XML and relational databases

    Another option is the Xfinity Server from B-Bop. It runs on an RDBMS such as
    Oracle or SQLServer. It gives you "native" XML functionality e.g. preserves
    element order, provides XML round-tripping, etc.

    The XML document is not stored as a blob. Xfinity automatically parses the XML
    into its elements and stores in an RDBMS. No schema design or mapping code is
    required.

    When querying, you have access to individual elements and can search across
    document collections.

    More information is available at www.b-bop.com/products_xfinity_server.htm.

    Hope this helps.

    -sanjay

    Russell Jones wrote:

    > The specific capabilities depend on the RDBMS you're using. In general, you
    > have these options:
    > 1. parse the XML -- putting the data into individual columns. This gives you
    > the most options for sorting and searching, but is the source of the
    > performance and document recreation complaints you've been hearing.
    > 2. parse the XML -- putting only critical data into individual columns and
    > also storing the XML document as a long text string. This method can give
    > you the flexibility of #1 if you can identify all the critical fields, but
    > it requires code to ensure the document doesn't get out of sync with the
    > column data. It also requires modification if you change the critical field
    > set and increases storage requirements, because you duplicate the critical
    > data.
    > 3. Store the XML as long text strings. You can search within these using
    > LIKE, but it's highly inefficient.You lose the ability to sort easily.
    > 4. Use a native XML database. See products like Tamino and Excelon. These
    > can interface with your existing RDBMS.
    >
    > Russell Jones
    > Sr. Web Development Editor,
    > DevX.com
    >
    > "Colin Webster" <colin@edeal.com> wrote in message
    > news:3b681f2f$1@news.devx.com...
    > >
    > > What are my options for getting my XML data into my relational database -
    > > and my relational data out to XML?
    > >
    > > Everybody is talking about performance problems, and problems recreating
    > > the XML docs back to their original order.


    --
    Sanjay Manchanda
    B-Bop Associates, Inc.
    Ph : 650-340-2702
    Fax: 650-340-2701
    Email: sanjay@b-bop.com
    http://www.b-bop.com



  6. #6
    Jimbo Guest

    Re: XML and relational databases


    Nice plug pity the product sucks!!!


    Sanjay Manchanda <sanjay@b-bop.com> wrote:
    >Another option is the Xfinity Server from B-Bop. It runs on an RDBMS such

    as
    >Oracle or SQLServer. It gives you "native" XML functionality e.g. preserves
    >element order, provides XML round-tripping, etc.
    >
    >The XML document is not stored as a blob. Xfinity automatically parses the

    XML
    >into its elements and stores in an RDBMS. No schema design or mapping code

    is
    >required.
    >
    >When querying, you have access to individual elements and can search across
    >document collections.
    >
    >More information is available at www.b-bop.com/products_xfinity_server.htm.
    >
    >Hope this helps.
    >
    >-sanjay
    >
    >Russell Jones wrote:
    >
    >> The specific capabilities depend on the RDBMS you're using. In general,

    you
    >> have these options:
    >> 1. parse the XML -- putting the data into individual columns. This gives

    you
    >> the most options for sorting and searching, but is the source of the
    >> performance and document recreation complaints you've been hearing.
    >> 2. parse the XML -- putting only critical data into individual columns

    and
    >> also storing the XML document as a long text string. This method can give
    >> you the flexibility of #1 if you can identify all the critical fields,

    but
    >> it requires code to ensure the document doesn't get out of sync with the
    >> column data. It also requires modification if you change the critical

    field
    >> set and increases storage requirements, because you duplicate the critical
    >> data.
    >> 3. Store the XML as long text strings. You can search within these using
    >> LIKE, but it's highly inefficient.You lose the ability to sort easily.
    >> 4. Use a native XML database. See products like Tamino and Excelon. These
    >> can interface with your existing RDBMS.
    >>
    >> Russell Jones
    >> Sr. Web Development Editor,
    >> DevX.com
    >>
    >> "Colin Webster" <colin@edeal.com> wrote in message
    >> news:3b681f2f$1@news.devx.com...
    >> >
    >> > What are my options for getting my XML data into my relational database

    -
    >> > and my relational data out to XML?
    >> >
    >> > Everybody is talking about performance problems, and problems recreating
    >> > the XML docs back to their original order.

    >
    >--
    >Sanjay Manchanda
    >B-Bop Associates, Inc.
    >Ph : 650-340-2702
    >Fax: 650-340-2701
    >Email: sanjay@b-bop.com
    >http://www.b-bop.com
    >
    >



  7. #7
    Unknown Guest

    Re: XML and relational databases


    Come On Jimbo, BE NICE!!
    We'll Tell Tim!!

    "Jimbo" <jimbo@hotmail.com> wrote:
    >
    >Nice plug pity the product sucks!!!
    >
    >
    >Sanjay Manchanda <sanjay@b-bop.com> wrote:
    >>Another option is the Xfinity Server from B-Bop. It runs on an RDBMS

    such
    >as
    >>Oracle or SQLServer. It gives you "native" XML functionality e.g. preserves
    >>element order, provides XML round-tripping, etc.
    >>
    >>The XML document is not stored as a blob. Xfinity automatically parses

    the
    >XML
    >>into its elements and stores in an RDBMS. No schema design or mapping

    code
    >is
    >>required.
    >>
    >>When querying, you have access to individual elements and can search across
    >>document collections.
    >>
    >>More information is available at www.b-bop.com/products_xfinity_server.htm.
    >>
    >>Hope this helps.
    >>
    >>-sanjay
    >>
    >>Russell Jones wrote:
    >>
    >>> The specific capabilities depend on the RDBMS you're using. In general,

    >you
    >>> have these options:
    >>> 1. parse the XML -- putting the data into individual columns. This gives

    >you
    >>> the most options for sorting and searching, but is the source of the
    >>> performance and document recreation complaints you've been hearing.
    >>> 2. parse the XML -- putting only critical data into individual columns

    >and
    >>> also storing the XML document as a long text string. This method can

    give
    >>> you the flexibility of #1 if you can identify all the critical fields,

    >but
    >>> it requires code to ensure the document doesn't get out of sync with

    the
    >>> column data. It also requires modification if you change the critical

    >field
    >>> set and increases storage requirements, because you duplicate the critical
    >>> data.
    >>> 3. Store the XML as long text strings. You can search within these using
    >>> LIKE, but it's highly inefficient.You lose the ability to sort easily.
    >>> 4. Use a native XML database. See products like Tamino and Excelon. These
    >>> can interface with your existing RDBMS.
    >>>
    >>> Russell Jones
    >>> Sr. Web Development Editor,
    >>> DevX.com
    >>>
    >>> "Colin Webster" <colin@edeal.com> wrote in message
    >>> news:3b681f2f$1@news.devx.com...
    >>> >
    >>> > What are my options for getting my XML data into my relational database

    >-
    >>> > and my relational data out to XML?
    >>> >
    >>> > Everybody is talking about performance problems, and problems recreating
    >>> > the XML docs back to their original order.

    >>
    >>--
    >>Sanjay Manchanda
    >>B-Bop Associates, Inc.
    >>Ph : 650-340-2702
    >>Fax: 650-340-2701
    >>Email: sanjay@b-bop.com
    >>http://www.b-bop.com
    >>
    >>

    >



  8. #8
    Unknown Guest

    Re: XML and relational databases


    Have you tried Software AG's Tamino?
    Might be what you're looking for...

    Sanjay Manchanda <sanjay@b-bop.com> wrote:
    >Another option is the Xfinity Server from B-Bop. It runs on an RDBMS such

    as
    >Oracle or SQLServer. It gives you "native" XML functionality e.g. preserves
    >element order, provides XML round-tripping, etc.
    >
    >The XML document is not stored as a blob. Xfinity automatically parses the

    XML
    >into its elements and stores in an RDBMS. No schema design or mapping code

    is
    >required.
    >
    >When querying, you have access to individual elements and can search across
    >document collections.
    >
    >More information is available at www.b-bop.com/products_xfinity_server.htm.
    >
    >Hope this helps.
    >
    >-sanjay
    >
    >Russell Jones wrote:
    >
    >> The specific capabilities depend on the RDBMS you're using. In general,

    you
    >> have these options:
    >> 1. parse the XML -- putting the data into individual columns. This gives

    you
    >> the most options for sorting and searching, but is the source of the
    >> performance and document recreation complaints you've been hearing.
    >> 2. parse the XML -- putting only critical data into individual columns

    and
    >> also storing the XML document as a long text string. This method can give
    >> you the flexibility of #1 if you can identify all the critical fields,

    but
    >> it requires code to ensure the document doesn't get out of sync with the
    >> column data. It also requires modification if you change the critical

    field
    >> set and increases storage requirements, because you duplicate the critical
    >> data.
    >> 3. Store the XML as long text strings. You can search within these using
    >> LIKE, but it's highly inefficient.You lose the ability to sort easily.
    >> 4. Use a native XML database. See products like Tamino and Excelon. These
    >> can interface with your existing RDBMS.
    >>
    >> Russell Jones
    >> Sr. Web Development Editor,
    >> DevX.com
    >>
    >> "Colin Webster" <colin@edeal.com> wrote in message
    >> news:3b681f2f$1@news.devx.com...
    >> >
    >> > What are my options for getting my XML data into my relational database

    -
    >> > and my relational data out to XML?
    >> >
    >> > Everybody is talking about performance problems, and problems recreating
    >> > the XML docs back to their original order.

    >
    >--
    >Sanjay Manchanda
    >B-Bop Associates, Inc.
    >Ph : 650-340-2702
    >Fax: 650-340-2701
    >Email: sanjay@b-bop.com
    >http://www.b-bop.com
    >
    >



  9. #9
    at Guest

    Re: XML and relational databases


    "Colin Webster" <colin@edeal.com> wrote:

    Hi Colin,

    >
    >What are my options for getting my XML data into my relational database

    -
    >and my relational data out to XML?


    There are a range of options, from storing as BLOBs/CLOBs to shredding into
    multiple tables to hierarchical storage to ...

    For an overview of some options provided by Informix, please see my slides
    from Net.ObjectDays in Germany last year:

    http://www.soi.city.ac.uk/~akmal/pre...objectdays.zip
    [PowerPoint 600 KB]

    >
    >Everybody is talking about performance problems, and problems recreating
    >the XML docs back to their original order.


    I have a benchmarks page with some XML DB benchmark references:

    http://www.soi.city.ac.uk/~akmal/htm...enchmarks.html

    The paper by Tian et al. shows that in one performance scenario, an RDB can
    do well.

    HTH

    akmal chaudhri
    Senior Architect
    IBM Informix Labs


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center