mdo:sql unknown structure

Developer
Mar 3, 2011 at 11:13 AM

Hi Anton,
I'm trying to dump an mdo:sql call without knowing columns at design-time, but cannot find the way: is this possible?

I mean: at design-time I don't know the query and columns to be extracted. In a normal scenario I'd write:

<xsl:for-each select="mdo:sql('select FiledName1,FiledName2 from table', 't')//t">
{{FiledName1}} - {{FiledName2}}
</xsl:for-each>

But if I don't know columns, how can I do this? If I do:

 

<xsl:for-each select="mdo:sql($select, 't')//t">
{{.}}
</xsl:for-each>

I get all values as a single string (FiledName1Value+FiledName2Value), while I'd like to get each field name and value (FiledName1 = FiledName1Value).

Should I write my own sql function?

 

 

 

Coordinator
Mar 3, 2011 at 1:22 PM

Alberto,

XSL/XPath allows you to access columns using it's position

<xsl:variable name="data" select="mdo:sql('select TabID, TabName from dnn_tabs', 'tab')"/>

<xsl:for-each select="$data//tab">
  {{./*[1]}} - {{./*[2]}}<br/>
</xsl:for-each>

You also can build a list of columns using first row or whole XML (as first row can miss some NULLed columns)

<xsl:variable name="columns">
  <xsl:for-each select="$data//tab/*">
    <xsl:if test="not(preceding::tab/*[name(.)=name(current())])">
      <column>{{name(.)}}</column>
    </xsl:if>
  </xsl:for-each>
</xsl:variable>

{h{mdo:html($columns)}}

Developer
Mar 3, 2011 at 3:15 PM

Anton,

I must be sleeping today... I cannot find a way to dynamically extract values, e.g. to make the column index dynamic.

With your second part of code I can extract the table header (name of each column) - thank you -, but I am not able to do the same with rows. I mean I cannot write:

{{./*[1]}} - {{./*[2]}}<br/>

because I do not know how many columns my query will extract. 

So I tried to make this dynamic, that is to loop on columns to dynamically get data fro each column:

<xsl:variable name="dataset" select="mdo:sql($select, 'tab')"/>
 <xsl:variable name="rs">
	 <xsl:copy-of select="$dataset"/>
 </xsl:variable>
 <!-- table header -->
 <table><tr>
 <xsl:for-each select="$dataset//tab/*">
	 <xsl:if test="not(preceding::tab/*[name(.)=name(current())])"><td>{{name(.)}}</td></xsl:if>
 </xsl:for-each>
 </tr>
 <!-- table data -->
 <xsl:for-each select="mdo:node-set($rs)//tab">
 <tr>
 <xsl:for-each select="mdo:sequence(1,3,1)">
	 <xsl:variable name="c" select="current()"></xsl:variable>
	 <td>{{*[number($c)]}}</td>
 </xsl:for-each>
 </tr>
</xsl:for-each>

Here I try to get a dynamic index with mdo:sequence (3 to be changed to the number of columns), but am not able to get the output.

I should buy some good xslt manual I believe, any suggestion?

Developer
Mar 3, 2011 at 4:27 PM

Anton,

I got it :) 

<tbody>
	 <xsl:for-each select="mdo:node-set($rs)//tab">
		 <xsl:variable name="n" select="."></xsl:variable>
		 <tr>
			 <xsl:for-each select="mdo:sequence(1,$numCols,1)">
				 <td>{{$n/*[current()=position()]}}</td>
			 </xsl:for-each>
		 </tr>
	 </xsl:for-each>
 </tbody>

But  I still need a good xslt/xpath manual, because I didn't understand why I cant' write $n/*[current()] ! ;-)

Coordinator
Mar 3, 2011 at 5:05 PM

current() is XSLT current node, not a number. mdo:sequesnce generates stub XML with nodes 1, 2, 3, ... so you have to use number(current()) to get real number.

Note that there are two different current nodes:

  • . (point) - is XPath curent node
  • current() is XSLT current node ( current for-each element, template root XML, ... )

I created one more sample for you that outputs any rowset in a HTML table with column names as headers:

<xsl:variable name="data" select="mdo:sql('select * from {databaseOwner}[{objectQualifier}Tabs]', 'tab')"/>

<xsl:variable name="_columns">
  <xsl:for-each select="$data//tab/*">
    <xsl:if test="not(preceding::tab/*[name(.)=name(current())])">
      <column>{{name(.)}}</column>
    </xsl:if>
  </xsl:for-each>
</xsl:variable>
<xsl:variable name="columns" select="msxsl:node-set($_columns)"/>

<table border="1">
  <tr>
    <xsl:for-each select="$columns/column">
      <td>{{.}}</td>
    </xsl:for-each>
  </tr>
  <xsl:for-each select="$data//tab">
    <xsl:variable name="tab" select="current()"/>
    <tr>
      <xsl:for-each select="$columns/column">
        <td>{{$tab/*[name()=current()]}}</td>
      </xsl:for-each>
    </tr>
  </xsl:for-each>
</table>