SELECT.XSLT Example – Using multiple data-sources

  • Created

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>

Was this article helpful?

0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.