This project is read-only.

Database lookup

Aug 20, 2010 at 12:33 PM
Edited Aug 20, 2010 at 12:36 PM

I need some advice on the following:

Traversing a XML document I need to add some data from a SQL table. I want do something like:

<xsl:for-each select="$Stand//DocumentElement/StandRow">

         ...
        <xsl:value-of select="mdo:sql('select UrlWebsite from TeamsPerClub where ID={{Team}};', '$scalar')"/>

 

but this results in a syntax error. I suppose because you cannot nest value-of elements.

How do to this?

Any help is welcome!

Coordinator
Aug 21, 2010 at 11:31 AM

You can use parameters as follows:

<xsl:value-of select="mdo:sql('select UrlWebsite from TeamsPerClub where ID=@id;', '$scalar', '@id', Team)"/>

Coordinator
Aug 21, 2010 at 11:42 AM
Edited Aug 21, 2010 at 12:58 PM

... and one more thing.

If this sample is real application, you should not query database for each row. You better read all UrlWebsite-s into separate variable as follows:

             <xsl:variable name="team-urls" select="mdo:sql('select ID, UrlWebsite from TeamsPerClub', 'url')" />

and then search thie variable instead database.

     <xsl:for-each select="$Stand//DocumentElement/StandRow">
             <xsl:value-of select="$team-urls//url[ID = current()/Team]/UrlWebsite"/>

This minimizes database load.

Aug 22, 2010 at 2:13 PM
Edited Aug 22, 2010 at 2:36 PM

Thanks findy. I'll give it a try this way.

About performance: the TeamsPerClub document will be 180K with 1260 nodes, while I only need a very small portion of the data: about 12 nodes. I think the overhead for the db calls is less than the data load.

Coordinator
Aug 23, 2010 at 6:48 AM

Yes, everything requires a balance. So I'd recommend filtering particular teams by passing IDs to SQL query as XML:

<!-- This SQL can query DB for only a particular set of Teams -->
<xsl:variable name="sql">

  declare @xml xml;
  set @xml = '<root>' + @teams + '</root>';

  select ID, UrlWebsite from TeamsPerClub where ID in (
      select x.value('.', 'nvarchar(max)') from @xml.nodes('//Team') t(x)
  );

</xsl:variable>

<!-- here we build a list of teams we need at the moment.
     assuming that all rows in stand are required.
     Or you can filter rows you ness with XPath's []  -->
<xsl:variable name="teams" select="mdo:text($Stand//DocumentElement/StandRow/Team)"/>

<!-- select small subset for particular teams -->
<xsl:variable name="team-urls" select="mdo:sql($sql, 'url', '@teams', $teams)" />

This approach works good when we have a master-detail or parent-child relationships between object from different sources (i.e. we can't write single SQL select and use JOIN operator). Here I don't know the source of $Stand so I assume that it cant be joined with TeansPerClub on MS SQL side.

 

 

Aug 23, 2010 at 9:32 AM

Clever thinking, findy. I'm new to both XSLT, XMl in SQL and XsltDb so I'm learning a lot!

All rows in Stand are indeed required and this way only one db call is required, instead of 12. For your interest, Stand is a team standing from a football league. I get this data from the local football association and want to publish this on my club's website adding a picture of each team's shirt and a link to each team's website which I have gathered in a database.

Aug 23, 2010 at 11:56 AM

I try to do this but I keep getting <root>SQL execution failed.</root>. I tried to run the script from with SQL2005 with a hardcoded teams list and it worked.  The teams variable fills ok, too. Obviously I'm missing a tiny detail. Here's the code:

<xsl:variable name="sql">

  declare @xml xml;
  set @xml = '<root>' + @teams + '</root>';

  select Team, UrlWebsite from TeamsPerClub where Team in (
      select x.value('.', 'nvarchar(max)') from @xml.nodes('//Team') t(x)
  );

</xsl:variable>

<xsl:variable name="teams" select="mdo:text($Stand//DocumentElement/StandRow/Team)"/>

<xsl:variable name="team-urls" select="mdo:sql($sql, 'url', '@teams', $teams)" />

Aug 23, 2010 at 12:14 PM

Its has something to do with the IN clause. If I replace the IN clause with something else, the query runs ok, syntactically.

Coordinator
Aug 23, 2010 at 1:46 PM

As far as you have an error on MS SQL Server side you can use MS SQL Profiler to determine what exactly is executing. (Btw, XML syntax differs from MS SQL 2005 to MS SQL 2008. I used 2008 one.) Unfortunately, XsltDb has no logging for the moment.

You say you tried hardcoded Teams. Did you use something like this?

 declare @xml xml;
  set @xml = '<root><Team>1</Team><Team>2</Team></root>';

  select Team, UrlWebsite from TeamsPerClub where Team in (
      select x.value('.', 'nvarchar(max)') from @xml.nodes('//Team') t(x)
  );

If code above succeed, you must look at value of $teams variable.

Try the following:

  <xsl:value-of select="$teams" />

This code outputs value of $teams variable so you can see what you pass to the MS SQL.

It seems you are using XsltDb for DNN 4, It eat all SQL errors...

Aug 23, 2010 at 4:11 PM

I tried this in SQL 2005 query window and it runs perfectly:

declare @xml xml;
  set @xml = '<root>' + '<Team>Olympia 3</Team><Team>Nieuwkoop 2</Team><Team>UDO 2</Team><Team>Alkmania 2</Team>' + '</root>';

  select * from TeamsPerClub where TeamsPerClub.Team in (
      select x.value('.', 'nvarchar(max)') from @xml.nodes('//Team') as t(x)
  );

For some reason the set @xml statement looses its values <root> and </root>. Only empty quotes appear in the profiler. I need to investigate further.

Coordinator
Aug 23, 2010 at 4:47 PM

It seems I can understand why... Since I used "<" and ">" in SQL statement I must use CDATA or &lt; and &gt; ...

Here is correct code that declares sql expression:

<xsl:variable name="sql"><![CDATA[

  declare @xml xml;
  set @xml = '<root>' + @teams + '</root>';

  select ID, UrlWebsite from TeamsPerClub where ID in (
      select x.value('.', 'nvarchar(max)') from @xml.nodes('//Team') t(x)
  );

]]></xsl:variable>

Unfortunately, I have no way to test code so errors can appear (and they are). In real life I avoid SQL statements in XsltDb code and use stored procedures instead.

Aug 23, 2010 at 9:48 PM

Ah, ok. That would have taken me ages to discover. Thanks! I'll continue.