XSLT and XPath lookup problem

Discussion in 'XML' started by Patrick Reilly, Sep 27, 2003.

  1. I am trying to implement a better method than I already have to document
    database schemas with XML, and use XSLT to both generate database DDL
    statements (CREATE TABLE, etc) and to transform to HTML for documentation
    purposes. In particular I want XSLT to transform to HTML so that my XML
    documents can be "live", doing the transform in the browser.

    In the new system a very short XML document looks like:

    <database>
    <table id="PROVIDER">
    <field name="KEY" type="decimal" precision="9" scale="0"/>
    ...
    <primary-key id="PKPROVIDER" fields="KEY"/>
    </table>

    <table id="PERSON">
    <field name="PROVIDER"/>
    <field name="KEY" type="decimal" precision="9" scale="0"/>
    ...
    <primary-key id="PKPERSON" fields="PROVIDER KEY"/>
    <foreign-key id="FKPERSON" fields="PROVIDER" foreign-table="PROVIDER"
    on-delete="cascade" on-update="restrict"/>
    </table>
    </database>

    Short description would be:

    PROVIDER - a table
    PROVIDER.KEY decimal(9,0) (is primary key, so not-null and unique).
    Primary key is PROVIDER.KEY

    PERSON - a table
    PERSON.PROVIDER is foreign key to PROVIDER.KEY, so is same data type.
    PERSON.KEY decimal(9,0) is unique number within provider.
    Primary key is PERSON.PROVIDER and PERSON.KEY
    Foreign key to the PROVIDER table where PERSON.PROVIDER = PROVIDER.KEY

    Note that PERSON.PROVIDER doesn't have any type,etc attributes. Since
    PERSON.PROVIDER is actually a foreign key to the PROVIDER.KEY field, the
    two fields need to have the same data type, and when generating DDL, etc
    the data type should be looked up from the field that this foreign field
    references. Therefore PERSON.PROVIDER's data type should be listed as the
    same as PROVIDER.KEY's type,precision,etc. A "gotcha" is that
    PERSON.PROVIDER could possibly be listed in more than one foreign-key
    element, so I'd have to only follow the first one found.

    I'm trying to write a template (mode="datatype") for a field node which
    outputs the actual type,precision,length,etc values for a field. In the
    case of PROVIDER.KEY that's easy, because it is explicitly defined by the
    attributes for the node. But for PERSON.PROVIDER I can't figure it out. So
    far my template looks like:

    <xsl:template match="field" mode="datatype">
    <xsl:choose>
    <!-- If type is present, go ahead with the data type -->
    <xsl:when test="@type">
    <xsl:choose>
    <!-- If length present, write out type(length) -->
    <xsl:when test="@length"><xsl:value-of
    select="@type"/>(<xsl:value-of select="@length"/>)</xsl:when>
    <xsl:when test="@precision">
    <!-- If precision is present... -->
    <xsl:choose>
    <!-- If scale is also present, write out type(precision,scale)
    -->
    <xsl:when test="@scale"><xsl:value-of
    select="@type"/>(<xsl:value-of select="@precision"/>,<xsl:value-of
    select="@scale"/>)</xsl:when>
    <!-- Otherwise write out type(precision) -->
    <xsl:eek:therwise><xsl:value-of select="@type"/>(<xsl:value-of
    select="@precision"/>)</xsl:eek:therwise>
    </xsl:choose>
    </xsl:when>
    <!-- No length,precision,scale - write out type -->
    <xsl:eek:therwise><xsl:value-of select="@type"/></xsl:eek:therwise>
    </xsl:choose>
    </xsl:when>
    <!-- Otherwise, assume it is a foreign key and use the foreign field
    instead -->
    <xsl:eek:therwise>
    ????????????
    </xsl:eek:therwise>
    </xsl:choose>
    </xsl:template>

    Any help appreciated.
     
    Patrick Reilly, Sep 27, 2003
    #1
    1. Advertisements

  2. Use:

    <xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:eek:utput method="text"/>

    <xsl:template match="table">
    <xsl:value-of select="concat('
    ', @id, ' - a table')"/>
    <xsl:apply-templates/>
    </xsl:template>

    <xsl:template match="field" >

    <xsl:variable name="vRefTable"
    select="/*/table[@id
    = current()/../foreign-key
    [@fields=current()/@name]
    /@foreign-table
    ]"/>

    <xsl:variable name="vRef"
    select="self::*[@type]
    |
    $vRefTable/field
    [@name
    =
    $vRefTable/primary-key/@fields
    ]"/>

    <xsl:value-of select="concat(@name, ' ')"/>
    <xsl:choose>
    <!-- If length present, write out type(length) -->
    <xsl:when test="$vRef/@length">
    <xsl:value-of select="$vRef/@type"/>
    <xsl:value-of select="concat('(',$vRef/@length, ')')"/>
    </xsl:when>
    <xsl:when test="$vRef/@precision">
    <!-- If precision is present... -->
    <xsl:choose>
    <!-- If scale is also present, write out type(precision,scale)
    -->
    <xsl:when test="$vRef/@scale">
    <xsl:value-of select="$vRef/@type"/>
    <xsl:value-of select="concat('(',$vRef/@precision, ',',
    $vRef/@scale,')')"/>
    </xsl:when>
    <!-- Otherwise write out type(precision) -->
    <xsl:eek:therwise>
    <xsl:value-of select="$vRef/@type"/>
    <xsl:value-of select="concat('(', $vRef/@precision, ')')"/>
    </xsl:eek:therwise>
    </xsl:choose>
    </xsl:when>
    <!-- No length,precision,scale - write out type -->
    <xsl:eek:therwise>
    <xsl:value-of select="$vRef/@type"/>
    </xsl:eek:therwise>
    </xsl:choose>
    </xsl:template>

    </xsl:stylesheet>


    When this transformation is applied on your source.xml:

    <database>
    <table id="PROVIDER">
    <field name="KEY" type="decimal" precision="9" scale="0"/>
    <primary-key id="PKPROVIDER" fields="KEY"/>
    </table>
    <table id="PERSON">
    <field name="PROVIDER"/>
    <field name="KEY" type="decimal" precision="9" scale="0"/>
    <primary-key id="PKPERSON" fields="PROVIDER KEY"/>
    <foreign-key id="FKPERSON" fields="PROVIDER" foreign-table="PROVIDER"
    on-delete="cascade" on-update="restrict"/>
    </table>
    </database>

    the wanted result is produced:

    PROVIDER - a table
    KEY decimal(9,0)



    PERSON - a table
    PROVIDER decimal(9,0)
    KEY decimal(9,0)



    =====
    Cheers,

    Dimitre Novatchev.
    http://fxsl.sourceforge.net/ -- the home of FXSL





    "Patrick Reilly" <> wrote in message
    news:2pldb.2026$...
    > I am trying to implement a better method than I already have to document
    > database schemas with XML, and use XSLT to both generate database DDL
    > statements (CREATE TABLE, etc) and to transform to HTML for documentation
    > purposes. In particular I want XSLT to transform to HTML so that my XML
    > documents can be "live", doing the transform in the browser.
    >
    > In the new system a very short XML document looks like:
    >
    > <database>
    > <table id="PROVIDER">
    > <field name="KEY" type="decimal" precision="9" scale="0"/>
    > ...
    > <primary-key id="PKPROVIDER" fields="KEY"/>
    > </table>
    >
    > <table id="PERSON">
    > <field name="PROVIDER"/>
    > <field name="KEY" type="decimal" precision="9" scale="0"/>
    > ...
    > <primary-key id="PKPERSON" fields="PROVIDER KEY"/>
    > <foreign-key id="FKPERSON" fields="PROVIDER" foreign-table="PROVIDER"
    > on-delete="cascade" on-update="restrict"/>
    > </table>
    > </database>
    >
    > Short description would be:
    >
    > PROVIDER - a table
    > PROVIDER.KEY decimal(9,0) (is primary key, so not-null and unique).
    > Primary key is PROVIDER.KEY
    >
    > PERSON - a table
    > PERSON.PROVIDER is foreign key to PROVIDER.KEY, so is same data type.
    > PERSON.KEY decimal(9,0) is unique number within provider.
    > Primary key is PERSON.PROVIDER and PERSON.KEY
    > Foreign key to the PROVIDER table where PERSON.PROVIDER = PROVIDER.KEY
    >
    > Note that PERSON.PROVIDER doesn't have any type,etc attributes. Since
    > PERSON.PROVIDER is actually a foreign key to the PROVIDER.KEY field, the
    > two fields need to have the same data type, and when generating DDL, etc
    > the data type should be looked up from the field that this foreign field
    > references. Therefore PERSON.PROVIDER's data type should be listed as the
    > same as PROVIDER.KEY's type,precision,etc. A "gotcha" is that
    > PERSON.PROVIDER could possibly be listed in more than one foreign-key
    > element, so I'd have to only follow the first one found.
    >
    > I'm trying to write a template (mode="datatype") for a field node which
    > outputs the actual type,precision,length,etc values for a field. In the
    > case of PROVIDER.KEY that's easy, because it is explicitly defined by the
    > attributes for the node. But for PERSON.PROVIDER I can't figure it out. So
    > far my template looks like:
    >
    > <xsl:template match="field" mode="datatype">
    > <xsl:choose>
    > <!-- If type is present, go ahead with the data type -->
    > <xsl:when test="@type">
    > <xsl:choose>
    > <!-- If length present, write out type(length) -->
    > <xsl:when test="@length"><xsl:value-of
    > select="@type"/>(<xsl:value-of select="@length"/>)</xsl:when>
    > <xsl:when test="@precision">
    > <!-- If precision is present... -->
    > <xsl:choose>
    > <!-- If scale is also present, write out type(precision,scale)
    > -->
    > <xsl:when test="@scale"><xsl:value-of
    > select="@type"/>(<xsl:value-of select="@precision"/>,<xsl:value-of
    > select="@scale"/>)</xsl:when>
    > <!-- Otherwise write out type(precision) -->
    > <xsl:eek:therwise><xsl:value-of select="@type"/>(<xsl:value-of
    > select="@precision"/>)</xsl:eek:therwise>
    > </xsl:choose>
    > </xsl:when>
    > <!-- No length,precision,scale - write out type -->
    > <xsl:eek:therwise><xsl:value-of select="@type"/></xsl:eek:therwise>
    > </xsl:choose>
    > </xsl:when>
    > <!-- Otherwise, assume it is a foreign key and use the foreign field
    > instead -->
    > <xsl:eek:therwise>
    > ????????????
    > </xsl:eek:therwise>
    > </xsl:choose>
    > </xsl:template>
    >
    > Any help appreciated.
    >
     
    Dimitre Novatchev, Sep 28, 2003
    #2
    1. Advertisements

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. Angus Parvis

    Java and XSLT, XPath

    Angus Parvis, Aug 10, 2004, in forum: Java
    Replies:
    1
    Views:
    3,000
    Angus Parvis
    Aug 10, 2004
  2. Alastair Cameron
    Replies:
    1
    Views:
    8,180
    SQL Server Development Team [MSFT]
    Jul 8, 2003
  3. Vincent Lefevre

    XPath/XSLT and extended precision

    Vincent Lefevre, Aug 21, 2003, in forum: XML
    Replies:
    4
    Views:
    1,000
    Vincent Lefevre
    Aug 21, 2003
  4. Flyzone

    XSLT: problem with Xpath

    Flyzone, Jan 25, 2005, in forum: XML
    Replies:
    1
    Views:
    451
    Mike King
    Jan 25, 2005
  5. Tjerk Wolterink

    XSLT xpath problem

    Tjerk Wolterink, Jul 14, 2005, in forum: XML
    Replies:
    3
    Views:
    478
    Mukul Gandhi
    Jul 14, 2005
  6. Replies:
    18
    Views:
    3,043
    Joseph Kesselman
    Oct 4, 2006
  7. Pathik
    Replies:
    1
    Views:
    543
  8. Vincent Davis

    lookup xpath (other?) to value in html

    Vincent Davis, Dec 31, 2013, in forum: Python
    Replies:
    0
    Views:
    146
    Vincent Davis
    Dec 31, 2013
Loading...