xslt queries in xml to SQL queries

Discussion in 'Python' started by Ian Roddis, Feb 26, 2006.

  1. Ian Roddis

    Ian Roddis Guest

    Hello

    I want to embed SQL type queries within an XML data record. The XM
    looks something like this
    <DISPLAYPAGE
    <FIELD NAME="SERVER" TYPE="DROPDOWN"
    <OPTION>1<OPTION
    <OPTION>2<OPTION
    <OPTION>3<OPTION
    </FIELD
    </DISPLAYPAGE

    I want to populate the drop down options from a database. The tabl
    looks like this (example)
    CREATE TABLE options_table
    id int primary key
    name varchar(255)
    field varchar(255


    I read an article that would allow me to embed an SQL query using XSL
    like this (in place of <OPTIONS>)
    <xslt:template match="options_table[field='SERVER']"
    <OPTION><xslt:value-of select="name/text()"/></OPTION
    </xslt:template

    Which would be equivalent of

    SELECT name FROM options_table WHERE field='SERVER'

    Does anyone else have experience of describing SQL queries in XML o
    parsing XSLT in python
    --
    To mail remove REMOVEME. from address
     
    Ian Roddis, Feb 26, 2006
    #1
    1. Advertising

  2. Ian Roddis

    Crutcher Guest

    Skipping ahead, let me try to rephrase this.

    First, this isn't really a python question, it is SQL, XSLT, and
    program design, but I'll try to answer.

    You have templates, they contain general layout stuff, and input
    fields. You are transforming them into HTML pages, and part of what you
    want to do is to expand the enumerated fields with the enumeration
    values at the time of template application.

    You read an article about XSLT, and decided that you could use it in
    your app. For some unknown reason, perhaps involving large amounts of
    alcohol, you want to use the syntax of XSLT, but have it actually
    parsed by python, which silently does an SQL querry to give you your
    results.

    This is a really, really bad idea. There are many ways to solve your
    problem using XSLT, and though all of them are easy, none of them are
    so simple that I could put them in a post to someone who doesn't know
    XSLT. If you know XSLT, follow the directions below, if you don't, DO
    NOT USE XSLT for this project. And for the love of anything you may
    believe in, don't try to implement a partial XSLT engine in python.


    Pattern 1, paramaterized templates:

    Pattern 1 assumes that all the data you will need for your templates is
    known in advance, and is common to your templates. If this is true, you
    need only generate an XML fragment with the data you need, and compose
    a document which contains both the data you need, and the page template
    you are rendering. Then, write an XSLT transform to build your pages,
    which can now resolve data needs in the template section using the data
    provided in the data section.

    Pattern 2, query extraction:

    When you don't know what data the page template might need, and the
    full dataset is unmanageably large, you can't use pattern 1 easily. So
    instead, write an XSLT transform which _only_ extracts information
    about the data you need. You can then run this transform, get the query
    list, perform the queries, and use the resultant data for the data tree
    from pattern 1.
     
    Crutcher, Feb 26, 2006
    #2
    1. Advertising

  3. Ian Roddis

    Paul Boddie Guest

    Crutcher wrote:
    > Skipping ahead, let me try to rephrase this.
    >
    > First, this isn't really a python question, it is SQL, XSLT, and
    > program design, but I'll try to answer.


    Well, first of all, it's about mapping XPath onto a relational data
    model. This is clear from the original posting:

    <xslt:template match="options_table[field='SERVER']">
    <OPTION><xslt:value-of select="name/text()"/></OPTION>
    </xslt:template>

    Here, it makes sense to consider instances of options_table elements as
    rows in the options_table table, just as described. Perhaps, if field
    and name are columns in options_table, I would rather model them using
    attributes:

    <xslt:template match="options_table[@field='SERVER']">
    <OPTION><xslt:value-of select="@name"/></OPTION>
    </xslt:template>

    Is this possible or wise to do? Well, I did myself write an
    XPath-to-SQL query engine, together with a simple "document relational"
    mapper in order to be able to execute XPath expressions similar to
    those above. However, I don't believe that my code really delivered on
    the concept because...

    * Generating efficient sets of queries can be tricky: a simple
    descent into what appears to be an XML document needs
    to involve a lot of joins to enforce the virtual "element
    hierarchy".

    * One has to decide whether to enforce a schema or not: if you don't
    this means that XPath axes like "child" can behave like
    "descendant-or-self". If you do, generating "descendant-or-self"
    queries becomes very difficult (as far as I can tell and remember).

    * The XPath specification states that nodes are returned in document
    order - this is difficult to enforce unless you litter your tables
    with additional information (again as far as I remember).

    * Generating simple DOM structures for simple SQL queries is
    straightforward, but generating them for arbitrarily complicated
    XPath queries mapped to SQL is quite difficult. If you permit axes
    like "ancestor-or-self" and "parent", or have any query that
    returns a node over and over again, it's best to instantiate that
    node only once in order to keep the number of instantiated
    objects low and to more easily test relationships between nodes.
    It's all awkward if not particularly difficult.

    > You have templates, they contain general layout stuff, and input
    > fields. You are transforming them into HTML pages, and part of what you
    > want to do is to expand the enumerated fields with the enumeration
    > values at the time of template application.
    >
    > You read an article about XSLT, and decided that you could use it in
    > your app. For some unknown reason, perhaps involving large amounts of
    > alcohol, you want to use the syntax of XSLT, but have it actually
    > parsed by python, which silently does an SQL querry to give you your
    > results.


    I don't know what it is with comp.lang.python/python-list these days
    and the cheap put-downs. Unless you know the person you're responding
    to personally, and thus the above counts as some kind of banter, you
    would do better to keep the insults to yourself.

    > This is a really, really bad idea. There are many ways to solve your
    > problem using XSLT, and though all of them are easy, none of them are
    > so simple that I could put them in a post to someone who doesn't know
    > XSLT. If you know XSLT, follow the directions below, if you don't, DO
    > NOT USE XSLT for this project. And for the love of anything you may
    > believe in, don't try to implement a partial XSLT engine in python.


    Why not? 4XSLT is in Python, or at least large parts of it were.
    Moreover, my XPath query experiments used a variant of PyXML's XPath
    library which could quite probably have been integrated with 4XSLT,
    although I didn't try it at the time.

    [Patterns]

    Anyway, I'm not suggesting that the XPath-to-SQL route is the right
    way. The various patterns you suggest are easier to comprehend and
    require a lot less magic, even if there's a bit more integration to be
    done to prepare data for use by the templates. But given the existence
    of XML database systems, I don't think the idea of accessing them (or
    things pretending to be like them) via XML technologies is outrageous
    at all.

    Paul
     
    Paul Boddie, Feb 26, 2006
    #3
  4. Ian Roddis

    Crutcher Guest

    > I don't know what it is with comp.lang.python/python-list these days
    > and the cheap put-downs. Unless you know the person you're responding
    > to personally, and thus the above counts as some kind of banter, you
    > would do better to keep the insults to yourself.


    You are completely right, I was overly familiar.

    Let me restate my central thesis.

    1) It is possible to do what he is asking to do.
    2) It requires expert knowledge of XSLT to do it well, and then it
    would still require a bit of work.
    3) There are simpler ways to do it, both using and not using xslt, than
    this.

    You could also do something like this:

    <options ...>
    <sql-hack query="select name from servers">
    <option><sql-value value="name"></option>
    </sql-hack>
    </options>

    and then do an XPath search for sql-hack nodes, and process them with
    your python.

    If you are happy staying on the python ranch, you could even do this:

    <options ...>
    <python><![CDATA[
    rows = sql('select names from servers')
    for row in rows:
    xml('<option>%s</option>' % row[0])
    ]]>
    </python>
    </options>

    With this, you find the python sections, and exec them in a context
    with sql defined to do queries against your db and xml defined to parse
    the xml you give it, and emit it in place of the python element.


    > But given the existence of XML database systems,
    > I don't think the idea of accessing them (or things pretending to be like them)
    > via XML technologies is outrageous at all.


    No, not outrageous. But this wasn't about quering XML database systems.
    This was about implementing something which looked like, but did not
    act like, XSLT. It's just bad design.
     
    Crutcher, Feb 26, 2006
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    3
    Views:
    1,636
  2. BaKMaN
    Replies:
    0
    Views:
    383
    BaKMaN
    Jan 30, 2004
  3. Stylus Studio
    Replies:
    0
    Views:
    419
    Stylus Studio
    Jul 26, 2004
  4. ecoolone
    Replies:
    0
    Views:
    783
    ecoolone
    Jan 3, 2008
  5. Abby Lee

    so many queries within queries I'm confused

    Abby Lee, Aug 4, 2004, in forum: ASP General
    Replies:
    11
    Views:
    367
    Aaron [SQL Server MVP]
    Aug 6, 2004
Loading...

Share This Page