Skip to main content

SELECT.XSLT Example – Using multiple data-sources

Zoltán Mikolás

This example shows you several of the techniques you can use to access data from multiple sources on the Actions stack of data sources and combine them freely into a completely new Xml structure.

Input Data Sources

The example assumes the following 3 data sources have been loaded onto the data-stack.

Data Source 1 – Named 'PRODUCTS'

Shown below in Xml-format, but it may as well be loaded in the Table-format (e.g. from an Excel sheet by use of the SELECT.EXCEL Command).

<root>
<Product id="101">
<SKU>AudiA4</SKU>
<Title>Audi A4 Limousine</Title>
<Manufacturer>Audi</Manufacturer>
</Product>
<Product id="102">
<SKU>AudiA6</SKU>
<Title>Audi A6 Avant</Title>
<Manufacturer>Audi</Manufacturer>
</Product>
<Product id="103">
<SKU>VolvoXC70</SKU>
<Title>Volvo XC70 Wagon</Title>
<Manufacturer>Volvo</Manufacturer>
</Product>
</root>

Data Source 2 – Named 'MANUFACTURERS'

Shown below in Xml-format, but it may as well be loaded in the Table-format (e.g. from an Excel sheet by use of the SELECT.EXCEL Command).

<root>
<Manufacturer>
<Name>Audi</Name>
<Country>Germany</Country>
<Types>Cars</Types>
<Founded>1909</Founded>
</Manufacturer>
<Manufacturer>
<Name>Volvo</Name>
<Country>Sweden</Country>
<Types>Cars</Types>
<Founded>1927</Founded>
</Manufacturer>
</root>

Data Source 3 – Named 'COUNTRIES'

Shown below in Xml-format, but it may as well be loaded in the Table-format (e.g. from an Excel sheet by use of the SELECT.EXCEL Command).

<Data>
<Country>
<Name>Germany</Name>
<Continent>Europe</Continent>
<Size>Large</Size>
</Country>
<Country>
<Name>Denmark</Name>
<Continent>Europe</Continent>
<Size>Even Smaller</Size>
</Country>
<Country>
<Name>Sweden</Name>
<Continent>Europe</Continent>
<Size>Small</Size>
</Country>
</Data>

The Xslt-script

An example Xslt-script is shown below. It uses all of the above 3 data sources and combines them into another Xml-structure.

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<xsl:stylesheet version="1.0" xmlns:xsl=" 
"xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl PerfionActions"
xmlns:PerfionActions="urn:actions-library-perfion-com"
>
<xsl:output method="xml" indent="yes"/>

<!--Create variables to reference the named DataSources in Action Mapping-->
<xsl:variable name="ProductsDS" select="PerfionActions:GetDataSource('PRODUCTS')/*"/>
<xsl:variable name="MfgsDS" select="PerfionActions:GetDataSource('MANUFACTURERS')/*"/>
<xsl:variable name="CountriesDS" select="PerfionActions:GetDataSource('COUNTRIES')/*"/>

<!-- **************************************************************************
This is where it all begins. Below template matches a default empty element.
Therefore apply templates on the referenced DataSources here.-->
<xsl:template match="*">
<Data>
<xsl:apply-templates select="$ProductsDS" mode="ProductsTemplate"/>
<xsl:apply-templates select="$MfgsDS" mode="MfgTemplate"/>
<xsl:apply-templates select="$CountriesDS" mode="AsIs"/>
</Data>
</xsl:template>

<!--Template to generate a list of all Products, and some select info
about them (incl. lookup info from Manufacturers)-->
<xsl:template match="*" mode="ProductsTemplate">
<Product id="{@id}">
<!--Output some information about the Products-->
<PerfionID>
<xsl:value-of select="@id"/>
</PerfionID>

<xsl:apply-templates select="node()" mode="AsIs"/>

<!--Output some information about the Manufacturers (via Lookup in other datasource)-->
<xsl:variable name="CurrentMfg" select="$MfgsDS[ Name = current()/Manufacturer ]"/>
<ManufacturerInfo>
<Name>
<xsl:value-of select="Manufacturer"/>
</Name>
<Founded>
<xsl:value-of select="$CurrentMfg/Founded"/>
</Founded>
<Country>
<xsl:value-of select="$CurrentMfg/Country"/>
</Country>
<Country_DirectLookup>
<xsl:value-of select="$MfgsDS[ Name = current()/Manufacturer ]/Country"/>
</Country_DirectLookup>
</ManufacturerInfo>

<!--Output some information about the Manufacturers
(via calling another template to do the detailed work)-->
<ManufacturerbyTemplate>
<xsl:call-template name="MfgBlock">
<xsl:with-param name="currentMfg" select="$MfgsDS[ Name = current()/Manufacturer ]"/>
</xsl:call-template>
</ManufacturerbyTemplate>

<!--Output information by direct Lookup of a VALUE via Perfion Extension
Library (this only works for DataTable DataSources)-->
<ManufacturerAtt1 country="
{PerfionActions:GetDataSourceRowValue('MANUFACTURERS',Manufacturer,'Country')}"/>

<!--Output information by direct Lookup of complete ROW via Perfion Extension
Library (this only works for DataTable DataSources)-->
<xsl:variable name="MfgRow" 
select="PerfionActions:GetDataSourceRow('MANUFACTURERS',Manufacturer)/*"/>
<ManufacturerAtts name="{$MfgRow/Name}" country="{$MfgRow/Country}" founded="{$MfgRow/Founded}"/>
</Product>
</xsl:template>

<!--Template to generate contents about a Manufacturer (passed in as parameter)-->
<xsl:template name="MfgBlock">
<xsl:param name="currentMfg" />
<Name>
<xsl:value-of select="$currentMfg/Name"/>
</Name>
<Country>
<xsl:value-of select="$currentMfg/Country"/>
</Country>
<AllMfgProducts><xsl:variable name="AllProductsFromMfg" 
select="$ProductsDS[ Manufacturer = $currentMfg/Name ]"/>
<xsl:apply-templates select="$AllProductsFromMfg/SKU" mode="AsIs"/>
</AllMfgProducts>
</xsl:template>

<!--Template to generate a list of all Manufacturers, and some select info about them-->
<xsl:template match="*" mode="MfgTemplate">
<Mfg name="{Name}">
<xsl:apply-templates select="node()" mode="AsIs"/>
<CountryInfo>
<xsl:variable name="CurrentCountry" select="$CountriesDS[ Name = current()/Country ]"/>
<xsl:apply-templates select="$CurrentCountry/*" mode="AsIs"/>
</CountryInfo>
<AllMfgProducts>
<xsl:variable name="AllProductsFromMfg" 
select="$ProductsDS[ Manufacturer = current()/Name ]"/>
<xsl:apply-templates select="$AllProductsFromMfg/SKU" mode="AsIs"/>
</AllMfgProducts>
</Mfg>
</xsl:template>

<!--Output whatever matches as it is-->
<xsl:template match="@* | node()" mode="AsIs">
<xsl:copy>
<xsl:apply-templates select="@* | node()" mode="AsIs"/>
</xsl:copy>
</xsl:template>

</xsl:stylesheet>

The Output result

The output-result of applying the above Xslt-script to the above 3 input-sources is shown below.

<Data>
<Product id="101">
<PerfionID>101</PerfionID>
<SKU>AudiA4</SKU>
<Title>Audi A4 Limousine</Title>
<Manufacturer>Audi</Manufacturer>
<ManufacturerInfo>
<Name>Audi</Name>
<Founded>1909</Founded>
<Country>Germany</Country>
<Country_DirectLookup>Germany</Country_DirectLookup>
</ManufacturerInfo>
<ManufacturerbyTemplate>
<Name>Audi</Name>
<Country>Germany</Country>
<AllMfgProducts>
<SKU>AudiA4</SKU>
<SKU>AudiA6</SKU>
</AllMfgProducts>
</ManufacturerbyTemplate>
<ManufacturerAtt1 country="Germany" />
<ManufacturerAtts name="Audi" country="Germany" founded="1909" />
</Product><Product id="102">
<PerfionID>102</PerfionID>
<SKU>AudiA6</SKU>
<Title>Audi A6 Avant</Title>
<Manufacturer>Audi</Manufacturer>
<ManufacturerInfo>
<Name>Audi</Name>
<Founded>1909</Founded>
<Country>Germany</Country>
<Country_DirectLookup>Germany</Country_DirectLookup>
</ManufacturerInfo>
<ManufacturerbyTemplate>
<Name>Audi</Name>
<Country>Germany</Country>
<AllMfgProducts>
<SKU>AudiA4</SKU>
<SKU>AudiA6</SKU>
</AllMfgProducts>
</ManufacturerbyTemplate>
<ManufacturerAtt1 country="Germany" />
<ManufacturerAtts name="Audi" country="Germany" founded="1909" />
</Product><Product id="103">
<PerfionID>103</PerfionID>
<SKU>VolvoXC70</SKU>
<Title>Volvo XC70 Wagon</Title>
<Manufacturer>Volvo</Manufacturer>
<ManufacturerInfo>
<Name>Volvo</Name>
<Founded>1927</Founded>
<Country>Sweden</Country>
<Country_DirectLookup>Sweden</Country_DirectLookup>
</ManufacturerInfo>
<ManufacturerbyTemplate>
<Name>Volvo</Name>
<Country>Sweden</Country>
<AllMfgProducts>
<SKU>VolvoXC70</SKU>
</AllMfgProducts>
</ManufacturerbyTemplate>
<ManufacturerAtt1 country="Sweden" />
<ManufacturerAtts name="Volvo" country="Sweden" founded="1927" />
</Product>
<Mfg name="Audi">
<Name>Audi</Name>
<Country>Germany</Country>
<Types>Cars</Types>
<Founded>1909</Founded>
<CountryInfo>
<Name>Germany</Name>
<Continent>Europe</Continent>
<Size>Large</Size>
</CountryInfo>
<AllMfgProducts>
<SKU>AudiA4</SKU>
<SKU>AudiA6</SKU>
</AllMfgProducts>
</Mfg>
<Mfg name="Volvo">
<Name>Volvo</Name>
<Country>Sweden</Country>
<Types>Cars</Types>
<Founded>1927</Founded>
<CountryInfo>
<Name>Sweden</Name>
<Continent>Europe</Continent>
<Size>Small</Size>
</CountryInfo>
<AllMfgProducts>
<SKU>VolvoXC70</SKU>
</AllMfgProducts>
</Mfg>
<Country>
<Name>Germany</Name>
<Continent>Europe</Continent>
<Size>Large</Size>
</Country>
<Country>
<Name>Denmark</Name>
<Continent>Europe</Continent>
<Size>Even Smaller</Size>
</Country>
<Country>
<Name>Sweden</Name>
<Continent>Europe</Continent>
<Size>Small</Size>
</Country>
</Data>

Related articles

Comments

0 comments

Please sign in to leave a comment.