Wrox Programmer Forums
Go Back   Wrox Programmer Forums > XML > XSLT
|
XSLT General questions and answers about XSLT. For issues strictly specific to the book XSLT 1.1 Programmers Reference, please post to that forum instead.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the XSLT section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 3rd, 2014, 11:01 AM
Authorized User
 
Join Date: Mar 2014
Posts: 26
Thanks: 2
Thanked 0 Times in 0 Posts
Default XSLT can't access certain database tables

Hello,

I seem to be having a problem in my XSLT gaining access to certain of my database tables. If I do this:

<xsl:value-of select="count(Table_1)"/>

I get a result of zero, even though I know there's plenty of data in Table_1.

But then, if I do the same for a different table:

<xsl:value-of select="count(Table_2)"/>

I get the results I expect.

I'm not sure where the problem lies--if it's a problem with my XSLT or my database permissions or what--but as I am new to XSLT, I'm not sure how XSLT goes about accessing the database. For example, I don't know if there's a connection string stored in a file somewhere that gets imported into or referenced by the XSLT scripts. If there is, I could see which user is being used to access the database and experiment with that user's table permissions. Or maybe there's a difference in the tables themselves such that there's a different syntax in XSLT for how to access one type of table versus another. I just don't know.

If anyone can think of any insights, or maybe some suggestions of what I could try, or any comments whatsoever, it would surely help.

Thanks.
 
Old April 3rd, 2014, 11:38 AM
mhkay's Avatar
Wrox Author
 
Join Date: Apr 2004
Posts: 4,962
Thanks: 0
Thanked 292 Times in 287 Posts
Default

To explain why these XPath expressions don't do what you expect, we need (a) to see the structure of your source XML, and (b) to know what the static and dynamic context for the expressions is.

It would also be useful to know something of your processing environment. XPath is normally used to access XML documents, not "database tables". Of course it's always possible to present a view of a database table as an XML document, but then we need to understand the mapping.
__________________
Michael Kay
http://www.saxonica.com/
Author, XSLT 2.0 and XPath 2.0 Programmer\'s Reference
 
Old April 9th, 2014, 11:03 AM
Authorized User
 
Join Date: Mar 2014
Posts: 26
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by mhkay View Post
To explain why these XPath expressions don't do what you expect, we need (a) to see the structure of your source XML, and (b) to know what the static and dynamic context for the expressions is.

It would also be useful to know something of your processing environment. XPath is normally used to access XML documents, not "database tables". Of course it's always possible to present a view of a database table as an XML document, but then we need to understand the mapping.
Sorry I haven't responded to this in a few days; been busy.

I'm not sure if the database outputs an XML document for XSLT to read. All I know is that usually I can write the XPath expression and get the value in the database I'm looking for.

All I know about the environment is that it's Windows 7 and the database is SQL Server. The XSLT templates are being read by a third party program. I have no idea how it reads them or how it invokes the execution of XSLT commands or XPATH expressions. All I do is write the XSLT and it works for the most part.

The best I can do is give you one of the templates. See the one below. The particular line that's giving me trouble is commented with "this returns 0 even though there are rows in the table".

Code:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ms="urn:schemas-microsoft-com:xslt" xmlns:dt="urn:schemas-microsoft-com:datatypes">

<xsl:template name="fflightInfo">
	
<xsl:variable name="pt_tr_vehicle" select="PCR/PT_TR_VEHICLE"/>
<xsl:variable name="pt_tr_incident" select="PCR/PT_TR_INCIDENT"/>

<table width="100%" xsl:use-attribute-sets="table_light_border" style="font-size: 11pt; font-family: verdana;">

	<thead>
		<TH align="left" colspan="11"  bgcolor="#C9C9C9">
			<font xsl:use-attribute-sets="font_title">				
				FLIGHT INFORMATION
			</font>
		</TH>
	</thead>

	<tbody>
		<TR align="left" valign="top">
		<xsl:for-each select="$lookup//LU_TR_LEVEL3[@FLIGHTINFO=1 and @ROW=1]">
			<xsl:sort select="@ORDERID"/>
			
			<TD width="25%" style="border-bottom: 0px solid #FFFFFF;">
				<B><xsl:value-of select="current()/@CAPTION"/>:</B>
				<BR/>
				
				<xsl:choose>
					<!-- vehicle type -->
					<xsl:when test="current()/@LEVEL3ID=154">
						<xsl:value-of select="$lookup//LU_TR_VEHICLETYPE[@VEHICLETYPEID=$pt_tr_vehicle/@VEHICLETYPEID]/@CAPTION"/>
					</xsl:when>
					<!-- primary role of unit -->
					<xsl:when test="current()/@LEVEL3ID=157">
					<xsl:choose>
						<xsl:when test="count($pt_tr_incident) != 0"> <!-- this returns 0 even though there are rows in the table -->
							<xsl:for-each select="$pt_tr_incident"> <!-- there should only be one anyway -->
								<xsl:value-of select="$lookup//LU_TR_SERVICELEVEL[@SERVICELEVELID=current()/@SERVICELEVELID]/@CAPTION"/>
							</xsl:for-each>
						</xsl:when>
						<xsl:otherwise>
							<xsl:value-of select="$noInfo"/>
						</xsl:otherwise>
					</xsl:choose>
					</xsl:when>
					<!-- base -->
					<xsl:when test="current()/@LEVEL3ID=158">
						<xsl:variable name="stationid" select="$pt_tr_vehicle/@STATIONID"/>
						<xsl:value-of select="$lookup//LU_TR_VEHICLEBASE[@VEHICLEBASEID=$stationid]/@CAPTION"/>
					</xsl:when>
					<xsl:otherwise>
						<xsl:value-of select="$noInfo"/>
					</xsl:otherwise>
				</xsl:choose>
				
			</TD>

		</xsl:for-each>
		</TR>
		
	</tbody>
</table>
	
<table width="100%" xsl:use-attribute-sets="table_light_border" style="font-size: 11pt; font-family: verdana;">
		
	<tbody>
		
		<TR align="left" valign="top">
		<xsl:for-each select="$lookup//LU_TR_LEVEL3[@FLIGHTINFO=1 and @ROW=2]">
			<xsl:sort select="@ORDERID"/>
			
			<TD width="33%" style="border-top: 0px solid #FFFFFF;">
				<B><xsl:value-of select="current()/@CAPTION"/>:</B>
				<BR/>
				
				<xsl:choose>
					<!-- destination facility / sending facility -->
					<xsl:when test="current()/@LEVEL3ID = 102001 or current()/@LEVEL3ID = 102002">
						<xsl:choose>
							<xsl:when test="count($pt_tr_vehicle/@STATIONID) != 0">
								<xsl:value-of select="$lookup//LU_TR_INSTITUTION[@INSTITUTIONID=$pt_tr_vehicle/@STATIONID]/@INSTITUTIONNAME"/> <!-- this doesn't work! STATIONID does NOT match the right INSTITUTIONID -->
							</xsl:when>
							<xsl:otherwise>
								<xsl:value-of select="$noInfo"/>
							</xsl:otherwise>
						</xsl:choose>
					</xsl:when>
					<!-- sending facility telephone -->
					<xsl:when test="current()/@LEVEL3ID = 102003">
						<xsl:value-of select="$noInfo"/> <!-- don't know where to get this in DB -->
					</xsl:when>
					<!-- unrecognized LEVEL3ID -->
					<xsl:otherwise>
						<xsl:value-of select="$noInfo"/>
					</xsl:otherwise>
				</xsl:choose>
			</TD>
			
		</xsl:for-each>
		</TR>
		
	</tbody>
	
</table>

</xsl:template>

</xsl:stylesheet>
 
Old April 9th, 2014, 11:13 AM
mhkay's Avatar
Wrox Author
 
Join Date: Apr 2004
Posts: 4,962
Thanks: 0
Thanked 292 Times in 287 Posts
Default

Try doing something like

Code:
<xsl:template match="/">
  <xsl:copy-of select="."/>
</xsl:template>
to display the input document. Without seeing the input document, we haven't a hope of working out what's wrong with your stylesheet.
__________________
Michael Kay
http://www.saxonica.com/
Author, XSLT 2.0 and XPath 2.0 Programmer\'s Reference
 
Old April 9th, 2014, 12:48 PM
Authorized User
 
Join Date: Mar 2014
Posts: 26
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by mhkay View Post
Try doing something like

Code:
<xsl:template match="/">
  <xsl:copy-of select="."/>
</xsl:template>
to display the input document. Without seeing the input document, we haven't a hope of working out what's wrong with your stylesheet.
I'm not sure I understand. Where do you want me to put this?
 
Old April 9th, 2014, 01:07 PM
mhkay's Avatar
Wrox Author
 
Join Date: Apr 2004
Posts: 4,962
Thanks: 0
Thanked 292 Times in 287 Posts
Default

I feel I'm trying to help someone remotely to do brain surgery when they aren't quite sure where to find the brain.

Sorry, I don't think this is going to work. I dont think you understand your system well enough to apply any advice I can give you.
__________________
Michael Kay
http://www.saxonica.com/
Author, XSLT 2.0 and XPath 2.0 Programmer\'s Reference
 
Old April 11th, 2014, 02:37 PM
Authorized User
 
Join Date: Mar 2014
Posts: 26
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Are you reeeally not going to help me?

I replaced my main template with:

<xsl:template match="/">
<xsl:copy-of select="."/>
</xsl:template>

and it produced an empty report.

Now, mind you, I'm using a third party software to read the XSLT and to output the report. It has its own report previewer. I'm not sure if this is the standard way of producing XSLT reports or not. But in any case, the report it produces is blank with the above template.

PS - Sorry if teaching me feels like pulling teeth.
 
Old April 11th, 2014, 02:44 PM
mhkay's Avatar
Wrox Author
 
Join Date: Apr 2004
Posts: 4,962
Thanks: 0
Thanked 292 Times in 287 Posts
Default

OK, change it to:

Code:
<xsl:template match="/">
<a><xsl:copy-of select="."/></a>
</xsl:template>
If it now says <a/>, then your input really is empty (which makes your stylesheet pretty pointless). If it's still blank, then you're not executing this code (which means I'm not the only one who hasn't a clue what you are doing).
__________________
Michael Kay
http://www.saxonica.com/
Author, XSLT 2.0 and XPath 2.0 Programmer\'s Reference
 
Old April 11th, 2014, 03:47 PM
Authorized User
 
Join Date: Mar 2014
Posts: 26
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Again, blank report (not even </a>).
 
Old April 11th, 2014, 04:54 PM
Authorized User
 
Join Date: Mar 2014
Posts: 26
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Ok, here's what I did. I replaced my main report template with this:

Code:
<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" 
	xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
	xmlns:ms="urn:schemas-microsoft-com:xslt"
	xmlns:dt="urn:schemas-microsoft-com:datatypes" 
	xmlns:med="urn:my-scripts"
	xmlns:med2="urn:Extensions"
>
	<xsl:variable name="pt_vi_setitemrel" select="PCR/PT_VI_SETITEMREL"/>
	<xsl:variable name="pc" select="PCR/VW_PI_PATIENTCONTACTS"/>
	 <!-- this should be visible anywhere: http://www.dopscripts.com/doc/varsscope.html -->
	
	<xsl:template match="/">
	

	
		<HTML>
			
			<HEAD>
				<META CHARSET="UTF-8" />
				<STYLE>
				</STYLE>
			</HEAD>
			
			<BODY>
			
				<xsl:value-of select="count($pt_vi_setitemrel)"/> | <xsl:value-of select="count($pc)"/>
			
			</BODY>
		</HTML>
	</xsl:template>

</xsl:stylesheet>
This prints out: 0 | 4

which reads: 0 rows in the PT_VI_SETITEMREL table and 4 in the VW_PI_PATIENTCONTACTS table.

What this tells me is that there isn't really anything wrong with the XSLT. If it can grab 4 rows from the VW_PI_PATIENTCONTACTS table, there shouldn't be any reason (in terms of the XSLT syntax) why it can't do the same for the PT_VI_SETITEMREL table (which, as I said, has rows).

So the only thing I'm left with is a database user access issue--that is, I suspect that it's a problem with the database user not having sufficient permissions to access the PT_VI_SETITEMREL table.

Would you agree?





Similar Threads
Thread Thread Starter Forum Replies Last Post
left join on multiple tables in MS access database 2007 sql code mukta Access 2 April 11th, 2012 07:27 AM
Selecting Two Tables in Access Database hollertrek General .NET 12 December 7th, 2007 12:29 AM
creating tables within tables in access??? carswelljr Access 3 August 23rd, 2006 01:21 PM
XSLT and tables surgeon XSLT 1 July 3rd, 2005 03:36 AM
Deleting tables in Access Database, if they exist mckly Pro VB Databases 1 August 25th, 2004 11:10 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.