XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Show tree view Topic
Topic Page 1 2 3 4 5 6 7 8 9 Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
Craig BermanSubject: XSLT, Microsoft Access, and repeating data
Author: Craig Berman
Date: 25 Apr 2008 10:49 AM
Our company is new to Stylus, but we know we need something as too many outside providers are using XML. So, that being said, I hope this question does not taint us as "Buy the XML for Dummies" folks.

We are using the "Mapper" function to create an XSLT to convert a file from XML to be usable in Microsoft Access (First step work here). We've hit the following construct:

<ShortNotes>
<ShortNote>
<Index>1</Index>
<Text>AE=X4H</Text>
</ShortNote>
<ShortNote>
<Index>2</Index>
<Text>US912828HV51</Text>
</ShortNote>
<ShortNote>
<Index>4</Index>
<Text>A#1972767790</Text>
</ShortNote>
</ShortNotes>

The value of index can be 1-8.

The access database has the fields SN1, SN2,.... SN8. What we want to do is have the fields populated based upon the index. If I were writing this in some programming language it would be:

If Index = 1 then SN1= <Text> else
If Index = 2 then SN2 = <Text> else ...... etc

I'm sure that this can be done using XSLT, its just that we are too "green" to have tackled the various constructs. Or perhaps, we simply can't figure out how to do this in the Mapper, but it could be done with putting the propper commands in the XSLT with an editor.

Either way, thanks for any help, and apologies in advance if this question just shows we need to hire some XML gurus around here.

Postnext
(Deleted User) Subject: XSLT, Microsoft Access, and repeating data
Author: (Deleted User)
Date: 29 Apr 2008 06:11 AM
You could create the stylesheet using Mapper, but it would be an overly complex stylesheet (with a lot of xsl:choose to send the data to the correct target element). A smaller stylesheet that should work for you is this one:

<?xml version='1.0'?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">
<root>
<xsl:apply-templates select="ShortNotes/ShortNote"/>
</root>
</xsl:template>


<xsl:template match="ShortNote">
<xsl:element name="SN{Index}">
<xsl:value-of select="Text"/>
</xsl:element>
</xsl:template>

</xsl:stylesheet>

Hope this helps,
Alberto

Postnext
Craig BermanSubject: XSLT, Microsoft Access, and repeating data
Author: Craig Berman
Date: 29 Apr 2008 02:01 PM
Many Many Thanks, I should have been more specific, as this form of construct is very normal from the vendor we receive this data. Here is a piece of the raw data:

<?xml version="1.0" encoding="UTF-8"?>
<BatchFeed xmlns="urn:tradefeed-xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<TradeFeed>
<Common>
<BloombergFirmID>XXXX</BloombergFirmID>
<TransactionNumber>9XXXXX</TransactionNumber>
<SecurityIdentifierFlag>1</SecurityIdentifierFlag>
<SecurityIdentifier>123456785</SecurityIdentifier>
<ShortNotes>
<ShortNote>
<Index>1</Index>
<Text>AE=XYZ</Text>
</ShortNote>
<ShortNote>
<Index>2</Index>
<Text>123456789000</Text>
</ShortNote>
<ShortNote>
<Index>4</Index>
<Text>A#1234567890</Text>
</ShortNote>
</ShortNotes>
<TraderAccountName>12345678</TraderAccountName>
<SalespersonName>XYZ</SalespersonName>
</Common>
</TradeFeed>
</BatchFeed>

The tags <TradeFeed></TradeFeed> repeat for each trade in the file.

Here is a piece of XSL from mapper that does nothing more than create an access table called "TheCommon", with 2 fields - "Firm" and "Trade"... no biggie....

<?xml version='1.0' ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:a="urn:tradefeed-xsd">
<xsl:template match="/">
<TheTrade>
<xsl:for-each select="a:BatchFeed/a:TradeFeed">
<TheCommon>
<Firm>
<xsl:value-of select="a:Common/a:BloombergFirmID"/>
</Firm>
<Trade>
<xsl:value-of select="a:Common/a:TransactionNumber"/>
</Trade>
</TheCommon>
</xsl:for-each>
</TheTrade>
</xsl:template>
</xsl:stylesheet>

It works fine... but what I want is to create an access table called "TheCommon" with 10 fields... "Firm", "Trade", and "SN1"..."SN8", representing ShortNotes by Index.

You gave me the piece of XSL:

<?xml version='1.0'?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">
<root>
<xsl:apply-templates select="ShortNotes/ShortNote"/>
</root>
</xsl:template>


<xsl:template match="ShortNote">
<xsl:element name="SN{Index}">
<xsl:value-of select="Text"/>
</xsl:element>
</xsl:template>

</xsl:stylesheet>


My question is, given the For-Each construction looping through <TradeFeed>, where and how do I fit this into the simple XSL above? This will cover about 90% of the problem for me, and maybe even get us on the way to processing one of these files.

Many Thanks

Posttop
(Deleted User) Subject: XSLT, Microsoft Access, and repeating data
Author: (Deleted User)
Date: 02 May 2008 10:39 AM
Hi Craig,
simply copy the template in your stylesheet and add the <xsl:apply-templates select="ShortNotes/ShortNote"/> after the </Trade> element in your stylesheet

Alberto

 
Topic Page 1 2 3 4 5 6 7 8 9 Go to previous topicPrev TopicGo to next topicNext Topic
Download A Free Trial of Stylus Studio 6 XML Professional Edition Today! Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!  
go

Log In Options

Site Map | Privacy Policy | Terms of Use | Trademarks
Stylus Scoop XML Newsletter:
W3C Member
Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.