Wrox Programmer Forums
Go Back   Wrox Programmer Forums > XML > XSLT
Reload this Page Replacing an element value with an incrementing counter's value
|
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 February 18th, 2014, 02:25 PM
ACB ACB is offline
Registered User
  
Join Date: Feb 2014
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default Replacing an element value with an incrementing counter's value
I have a result set exported from MySQL as XML that looks, in general, like this:

Code:
<?xml version="1.0"?>

<resultset statement="select * from CAALBUMIMAGES where AlbumID = 9980121727059 order by ImageFileName ASC LIMIT 0, 1000"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

	<row>
		<field name="AlbumID">9980121727059</field>
		<field name="ImageID">9990246556401</field>
		<field name="ImageFileName">First step</field>
		<field name="ImageExtension">jpg</field>
		<field name="Title" xsi:nil="true" />
		<field name="ShortDescription"></field>
		<field name="Description"></field>
		<field name="AlbumCover">0</field>
	</row>

	<row>
		<field name="AlbumID">9980121727059</field>
		<field name="ImageID">9980246556401</field>
		<field name="ImageFileName">Next step</field>
		<field name="ImageExtension">jpg</field>
		<field name="Title" xsi:nil="true" />
		<field name="ShortDescription"></field>
		<field name="Description"></field>
		<field name="AlbumCover">0</field>
	</row>

	<row>
		<field name="AlbumID">9980121727059</field>
		<field name="ImageID">9970246556401</field>
		<field name="ImageFileName">Finally</field>
		<field name="ImageExtension">jpg</field>
		<field name="Title" xsi:nil="true" />
		<field name="ShortDescription"></field>
		<field name="Description"></field>
		<field name="AlbumCover">0</field>
	</row>
</resultset>
I wrote the following XQuery to create an XML file formatted to use as input to FileZilla:

Code:
xquery version "1.0";
declare namespace xsl="http://www.w3.org/1999/XSL/Transform";

<FileZilla3>
    <Queue>
        <Server>
            <Host>www.hostname.com</Host>
            <Port>21</Port>
            <Protocol>0</Protocol>
            <Type>0</Type>
            <User>username</User>
            <Pass>password</Pass>
            <Logontype>1</Logontype>
            <TimezoneOffset>0</TimezoneOffset>
            <PasvMode>MODE_DEFAULT</PasvMode>
            <MaximumMultipleConnections>0</MaximumMultipleConnections>
            <EncodingType>Auto</EncodingType>
            <BypassProxy>0</BypassProxy>
            <Name>site_name</Name>
            {

for $i in fn:doc("test.xml"
  )/resultset/row
where $i/field/@name="AlbumID" and contains(
    $i/field[@name='AlbumID']/text(),"9980121727059"
  )

return 

	<File>
		<LocalFile>F:\Temp\{ fn:string($i/field[@name="ImageID"]) }.jpg</LocalFile>
		<RemoteFile>{ fn:string($i/field[@name="ImageID"]) }.jpg</RemoteFile>
		<RemotePath>1 0 11 public_html 9 community 14 CommunityAlbum</RemotePath>
		<Download>1</Download>
		<DataType>1</DataType>
	</File>
}
        </Server>
    </Queue>
</FileZilla3>
The output looks like this (at least the bit that matters):
Code:
  <File>
        <LocalFile>C:\Temp\9930122064832.jpg</LocalFile>
        <RemoteFile>9930122064832.jpg</RemoteFile>
        <RemotePath>1 0 11 public_html 9 community 14 CommunityAlbum</RemotePath>
        <Download>1</Download>
        <DataType>1</DataType>
      </File>
      <File>
        <LocalFile>C:\Temp\9940246556403.jpg</LocalFile>
        <RemoteFile>9940246556403.jpg</RemoteFile>
        <RemotePath>1 0 11 public_html 9 community 14 CommunityAlbum</RemotePath>
        <Download>1</Download>
        <DataType>1</DataType>
      </File>
      <File>
        <LocalFile>C:\Temp\9950246556403.jpg</LocalFile>
        <RemoteFile>9950246556403.jpg</RemoteFile>
        <RemotePath>1 0 11 public_html 9 community 14 CommunityAlbum</RemotePath>
        <Download>1</Download>
        <DataType>1</DataType>
      </File>
What I would like to do is change the output for the <LocalFile> element such that instead of using the <field name="ImageID"> value, it substitutes the value from an incrementing counter. That is, I'd like the output to look like this:

Code:
  <File>
        <LocalFile>C:\Temp\1.jpg</LocalFile>
        <RemoteFile>9930122064832.jpg</RemoteFile>
        <RemotePath>1 0 11 public_html 9 community 14 CommunityAlbum</RemotePath>
        <Download>1</Download>
        <DataType>1</DataType>
      </File>
      <File>
        <LocalFile>C:\Temp\2.jpg</LocalFile>
        <RemoteFile>9940246556403.jpg</RemoteFile>
        <RemotePath>1 0 11 public_html 9 community 14 CommunityAlbum</RemotePath>
        <Download>1</Download>
        <DataType>1</DataType>
      </File>
      <File>
        <LocalFile>C:\Temp\3.jpg</LocalFile>
        <RemoteFile>9950246556403.jpg</RemoteFile>
        <RemotePath>1 0 11 public_html 9 community 14 CommunityAlbum</RemotePath>
        <Download>1</Download>
        <DataType>1</DataType>
      </File>
Is there a way to do that in my XQuery code?
 
Old February 18th, 2014, 03:02 PM
Friend of Wrox
  
Join Date: Nov 2007
Posts: 1,243
Thanks: 0
Thanked 245 Times in 244 Posts
Default
I think
Code:
let $rows := fn:doc("test.xml")/resultset/row[field/@name="AlbumID" and contains(field[@name='AlbumID'],"9980121727059")]
for $row at $pos in $rows
return

    <File>
        <LocalFile>F:\Temp\{$pos}.jpg</LocalFile>
        <RemoteFile>{ fn:string($row/field[@name="ImageID"]) }.jpg</RemoteFile>
        <RemotePath>1 0 11 public_html 9 community 14 CommunityAlbum</RemotePath>
        <Download>1</Download>
        <DataType>1</DataType>
    </File>
might do but this is an XSLT forum anyway and my XQuery skills suck so there might be more elegant ways of doing this.
__________________
Martin Honnen
Microsoft MVP (XML, Data Platform Development) 2005/04 - 2013/03
My blog
 
Old February 18th, 2014, 03:35 PM
ACB ACB is offline
Registered User
  
Join Date: Feb 2014
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default
I think it's pretty close, but I'm getting an error relating to the "contains" clause that says "No context item defined to evaluate 'field[(@name = "AlbumID")]". If I drop the "contains" clause it would work as long as my MySQL export is done case by case per the AlbumID value.

Without the "contains" clause on an XML file that contains only the rows relevant to the AlbumID I'm searching for I get this (exactly what I was looking for):

Code:
      <File>
        <LocalFile>F:\Temp\1.jpg</LocalFile>
        <RemoteFile>9990246556401.jpg</RemoteFile>
        <RemotePath>1 0 11 public_html 9 community 14 CommunityAlbum</RemotePath>
        <Download>1</Download>
        <DataType>1</DataType>
      </File>
      <File>
        <LocalFile>F:\Temp\2.jpg</LocalFile>
        <RemoteFile>9980246556401.jpg</RemoteFile>
        <RemotePath>1 0 11 public_html 9 community 14 CommunityAlbum</RemotePath>
        <Download>1</Download>
        <DataType>1</DataType>
      </File>
      <File>
        <LocalFile>F:\Temp\3.jpg</LocalFile>
        <RemoteFile>9970246556401.jpg</RemoteFile>
        <RemotePath>1 0 11 public_html 9 community 14 CommunityAlbum</RemotePath>
        <Download>1</Download>
        <DataType>1</DataType>
      </File>
Thanks very much for the help. In future, is there another forum where I should submit XQuery questions if the XSLT forum is not the correct one?
 
Old February 18th, 2014, 04:32 PM
mhkay's Avatar
Wrox Author
  
Join Date: Apr 2004
Posts: 4,962
Thanks: 0
Thanked 292 Times in 287 Posts
Default
Your "where" clause can be reduced to:

Code:
where $i/field[@name='AlbumID'] = "9980121727059"
__________________
Michael Kay
http://www.saxonica.com/
Author, XSLT 2.0 and XPath 2.0 Programmer\'s Reference
The Following User Says Thank You to mhkay For This Useful Post:
ACB (February 18th, 2014)
 
Old February 19th, 2014, 05:57 AM
Friend of Wrox
  
Join Date: Nov 2007
Posts: 1,243
Thanks: 0
Thanked 245 Times in 244 Posts
Default
As for posting further XQuery questions, there is an active mailing list http://x-query.com/mailman/listinfo/talk dedicated to that language.
And these days I think StackOverflow is a good place to find help with programming related questions, see http://stackoverflow.com/questions/tagged/xquery.
In the Wrox forums I am not sure, the XML forum is more general than this XSLT forum.

As for the error you say you got with my suggestion, I don't see why you would get it as I removed the "where" clause and put the condition into a predicate in square brackets where the context is defined by the expression preceding the predicate. That was necessary anyway as I needed the `at $pos` to work in the filtered sequence of rows and not on all rows.
__________________
Martin Honnen
Microsoft MVP (XML, Data Platform Development) 2005/04 - 2013/03
My blog
 
Old February 19th, 2014, 11:55 AM
ACB ACB is offline
Registered User
  
Join Date: Feb 2014
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default
Thanks for the suggestions for forums related to XQuery.

As for the error, I don't understand why it would happen either. However, as Michael Kay noted, that additional condition was superfluous anyway. I will wait until a situation comes up where it's really required before I fight that battle.

Thanks again for the help.





Similar Threads
Thread Thread Starter Forum Replies Last Post
xsl:apply-templates replacing a descendent element CanOfBees XSLT 6 August 27th, 2012 11:09 AM
Identify element based on the element data/content ROCXY XSLT 7 September 8th, 2011 02:48 AM
Incrementing attribute value Pankaj C XSLT 4 October 31st, 2007 05:40 AM
deleting or replacing a single element hasdy XSLT 3 March 14th, 2006 12:28 PM
incrementing value of a variable akibaMaila VB.NET 2002/2003 Basics 4 July 5th, 2005 12:04 PM




Contact Us - Wrox - Privacy Statement - Top

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