|
|
 next
|
  Subject: 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.
|
|
next
|
Subject: 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
|
|
|
|
top