Re: [xsl] I need tools XML/XSL/XSD for generation os SQL JOIN...Again:

Subject: Re: [xsl] I need tools XML/XSL/XSD for generation os SQL JOIN...Again:
From: Jeni Tennison <jeni@xxxxxxxxxxxxxxxx>
Date: Thu, 1 Nov 2001 10:34:16 +0000
Hi Cristobal,

> I need urgently tools ( XML/XSL/XSD) for generate automaticaly JOINs
> of varias table schema BD..

It looks as though you have the XML. An XML Schema file probably won't
help you here. But you could use XSLT to create the XML that you want
as the output.

> 1) A file  XML (XSD preference) must exists for definitions of tables and
> relations ship's  (PKs,FKs...)

You can get hold of TablasRelaciones.XML from within the XSLT using
the document() function:

  document('TablasRelaciones.XML')

The main kind of access that you require from this file is to retrieve
the Tabla elements according to their Id attribute. To make this
easier, you should create a key that indexes the Tabla elements as
follows:

<xsl:key name="Tablas" match="Tabla" use="@Id" />

If you do that then you can use the key() function to retrieve Tabla
elements by Id. For example, to get the Tabla element for
'LOOKUP_ALUMNOS_SEL' you would do:

  key('Tablas', 'LOOKUP_ALUMNOS_SEL')

The only thing that you have to be a bit careful about is that the
context node when you use the key() function has to be in the
TablasRelaciones.XML document, so depending on the situation you might
have to use an xsl:for-each to change the current node:

  <xsl:variable name="Id" select="..." />
  <xsl:for-each select="document('TablasRelaciones.XML')">
    ... key('Tablas', $Id) ...
  </xsl:for-each>

> 2) My input is an XML file with the tables target

The document element of your source XML file is an Argums element, so
I'd create a template matching that element:

<xsl:template match="Argums">
  ...
</xsl:template>

The File element child of the Argums element gives you the name of the
file containing the relationships between the tables. That's the one
that you want to retrieve with the document() function. I'd set up a
variable to hold its root node, as follows (the second argument to the
document() function ensures the file reference is resolved relative to
the source document rather than the stylesheet):

<xsl:template match="Argums">
  <xsl:variable name="TablaDefs" select="document(File, .)" />
  ...
</xsl:template>

>From this file, you want to retrieve the Tabla elements whose Ids are
those listed in the child Tabla elements of the Argums element. First
make a variable to hold the Tabla Ids:

<xsl:template match="Argums">
  <xsl:variable name="TablaDefs" select="document(File, .)" />
  <xsl:variable name="TablaIds" select="Tabla" />
  ...
</xsl:template>

Then change the context to the $Tablas document and retrieve all the
relevant Tabla element using the key() function, putting them into a
$Tablas variable:

<xsl:template match="Argums">
  <xsl:variable name="TablaDefs" select="document(File, .)" />
  <xsl:variable name="TablaIds" select="Tabla" />
  <xsl:for-each select="TablaDefs">
    <xsl:variable name="Tablas" select="key('Tablas', $TablaIds)" />
    ...
  </xsl:for-each>
</xsl:template>

Then you want to create various static output, which you can do
directly with literal result elements:

<xsl:template match="Argums">
  <xsl:variable name="Tablas" select="document(File, .)" />
  <xsl:variable name="TablaIds" select="Tabla" />
  <xsl:for-each select="TablaDefs">
    <xsl:variable name="Tablas" select="key('Tablas', $TablaIds)" />
    <SQL Tipo="JOIN">
      <SELECT />
      <FROM>
        ...
      </FROM>
      <WHERE>
        ...
      </WHERE>
    </SQL>
  </xsl:for-each>
</xsl:template>

The content of the FROM element is a list of the Tabla Ids and IdNums.
You can create this by iterating over the content of the $Tablas
variable and accessing their Id and IdNums attributes, as follows:

  <xsl:for-each select="$Tablas">
    <Tabla>
      <xsl:value-of select="@Id" />
      <xsl:text> </xsl:text>
      <xsl:value-of select="concat('T', @IdNum)" />
    </Tabla>
  </xsl:for-each>

The content of the WHERE element is a bit more complex. For each Tabla
in $Tablas, you need to look at its child FK elements and see whether
the Id of the referenced Tabla is the same as the Id of one of the
Tablas in $Tablas. If so, then you need to generate the line based on
the Col element. You can do this with the following:

  <xsl:for-each select="$Tablas/FK">
    <xsl:variable name="Id" select="concat('T', ../@IdNum)" />
    <xsl:variable name="ForeignTabla"
                  select="$Tablas[@Id = current()/Tabla/@Id]" />
    <xsl:if test="$ForeignTabla">
      <Join>
        <xsl:value-of select="$Id" />.<xsl:value-of select="Col" />
        <xsl:text> = </xsl:text>
        <xsl:value-of select="concat('T', $ForeignTabla/@IdNum)" />
        <xsl:text>.</xsl:text>
        <xsl:value-of select="Col/@FK" />
      </Join>
    </xsl:if>
  </xsl:for-each>

> 4) NB. Is posible that in my input an intermediate table,necesary
> for the JOIN (with Foreing's Keys) don't exits.

I'm afraid I don't understand what this means. If you give an example,
it might be clearer, but hopefully the above has given you enough
ideas about how to solve your problem to let you do it yourself.

I hope that helps,

Jeni

---
Jeni Tennison
http://www.jenitennison.com/


 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list


Current Thread