XSLT and XPath lookup problem

[Patrick Reilly]

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) --> <xsltherwise><xsl:value-of select="@type"/>(<xsl:value-of select="@precision"/>)</xsltherwise> </xsl:choose> </xsl:when> <!-- No length,precision,scale - write out type --> <xsltherwise><xsl:value-of select="@type"/></xsltherwise> </xsl:choose> </xsl:when> <!-- Otherwise, assume it is a foreign key and use the foreign field instead --> <xsltherwise> ???????????? </xsltherwise> </xsl:choose> </xsl:template>

Any help appreciated.

 

[Dimitre Novatchev]

Use:

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xslutput 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) --> <xsltherwise> <xsl:value-of select="$vRef/@type"/> <xsl:value-of select="concat('(', $vRef/@precision, ')')"/> </xsltherwise> </xsl:choose> </xsl:when> <!-- No length,precision,scale - write out type --> <xsltherwise> <xsl:value-of select="$vRef/@type"/> </xsltherwise> </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