Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Lookup tables with variable keys

Posted on 2013-05-24
7
Medium Priority
?
232 Views
Last Modified: 2013-05-26
Is it possible to use a lookup table if the key changes? In the example below, a variable is used as the key, but it's not working because XSLT variables are not really variable.

Here is the XSL:
<lookup:NAICS_Classification_Percentage>
	<FacilityNAICS Classification="ccc" Percentage="60">Two Code Inc. Industrial Heartland 123456</FacilityNAICS>
	<FacilityNAICS Classification="ddd" Percentage="40">Two Code Inc. Industrial Heartland 456789</FacilityNAICS>
</lookup:NAICS_Classification_Percentage>

<xsl:variable name="ThisFacilityNAICS" select="concat(/Report/OrganisationNameEn, ' ', /Report/FacilityNameEn, ' ', /Report/Report_Detail/IdentificationProfile/Facility/NAICS_Codes/NAICS/NAICSCodeEn)"/>

<xsl:value-of select="$ThisFacilityNAICS"/>, <xsl:value-of select="document('')//lookup:NAICS_Classification_Percentage/FacilityNAICS[. = $ThisFacilityNAICS]/@Classification"/>, <xsl:value-of select="document('')//lookup:NPRI_GHGIS_ID/CompanyFacility[. = $ThisFacilityNAICS]/@Percentage"/>

Open in new window


Here is the XML:
<Report>
	<OrganisationNameEn>Two Code Inc.</OrganisationNameEn>
	<FacilityNameEn>Industrial Heartland</FacilityNameEn>
	<Report_Detail>
		<IdentificationProfile>
			<Facility>
				<NAICS_Codes>
                    <NAICS>
                        <NAICSCodeEn>123456</NAICSCodeEn>
                    </NAICS>
                    <NAICS>
                        <NAICSCodeEn>456789</NAICSCodeEn>
                    </NAICS>
				</NAICS_Codes>
				</Facility>
				</IdentificationProfile>
				</Report_Detail>
</Report>

Open in new window

0
Comment
Question by:mariita
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39195663
It is absolutely not clear what you want to do
But I assume you are looking for dealing with a combined key?
Wel, combined keys can easily be achieved by concatening the keys
or by having an "and" inside the predicate.

Can you explain what exactly you want to do?
0
 

Author Comment

by:mariita
ID: 39196459
Sorry, the snippet did not have enough context.

I want to loop through the list of NAICS codes in the XML file, and use a composite key (consisting of Organization, Facility, NAICS) to look up the corresponding Classification and Percentage in the lookup table. Because this facility has more than one NAICS code, the $ThisFacilityNAICS variable cannot be set to a specific value.

<lookup:NAICS_Classification_Percentage>
	<FacilityNAICS Classification="ccc" Percentage="60">Two Code Inc. Industrial Heartland 123456</FacilityNAICS>
	<FacilityNAICS Classification="ddd" Percentage="40">Two Code Inc. Industrial Heartland 456789</FacilityNAICS>
</lookup:NAICS_Classification_Percentage>

<xsl:for-each select="/Report/Report_Detail/IdentificationProfile/Facility/NAICS_Codes/NAICS">
	<xsl:variable name="ThisFacilityNAICS" select="concat(/Report/OrganisationNameEn, ' ', /Report/FacilityNameEn, ' ', /Report/Report_Detail/IdentificationProfile/Facility/NAICS_Codes/NAICS/NAICSCodeEn)"/>
	<NAICSCode>
		<NAICSClassification>
			<xsl:value-of select="document('')//lookup:NAICS_Classification_Percentage/FacilityNAICS[. = $ThisFacilityNAICS]/@Classification"/>
		</NAICSClassification>
		<Code>
			<xsl:value-of select="NAICSCodeEn"/>
		</Code>
		<ActivityPercentage>
			<xsl:value-of select="document('')//lookup:NAICS_Classification_Percentage/FacilityNAICS[. = $ThisFacilityNAICS]/@Percentage"/>
		</ActivityPercentage>
	</NAICSCode>
</xsl:for-each>

Open in new window

0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39196686
just change the lookup

      <FacilityNAICS Classification="ccc" Percentage="60" organisation="Two Code Inc." facility="Industrial Heartland " NAICS="123456"/>

and go for

FacilityNAICS[@organisation = ...][@facility = ...][@NAICS = ...]/@Classification

no need to compound in the lookup, it is better to keep them seperated
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mariita
ID: 39196721
I tried that but it's not working.

<lookup:NAICS_Classification_Percentage>
	<FacilityNAICS Org="Two Code Inc." Facility="Industrial Heartland " NAICS="123456" Classification="ccc" Percentage="60" />
	<FacilityNAICS Org="Two Code Inc." Facility="Industrial Heartland " NAICS="456789" Classification="ddd" Percentage="40" />
</lookup:NAICS_Classification_Percentage>

<xsl:for-each select="/Report/Report_Detail/IdentificationProfile/Facility/NAICS_Codes/NAICS">
	<xsl:value-of select="document('')//lookup:NAICS_Classification_Percentage/FacilityNAICS[@Org = /Report/OrganisationNameEn][@Facility = /Report/FacilityNameEn][@NAICS = NAICSCodeEn]/@Classification"/>, 
	<xsl:value-of select="document('')//lookup:NAICS_Classification_Percentage/FacilityNAICS[@Org = /Report/OrganisationNameEn][@Facility = /Report/FacilityNameEn][@NAICS = NAICSCodeEn]/@Percentage"/>
</xsl:for-each>

Open in new window

0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39197035
that is a bit tricky (call it the higher science of XSLT :-)

[@Org = /Report/OrganisationNameEn]

because of the document('') the context in teh predicate is the XSLT file, no longer the source XML file. If you need to do that, you need to bind the source to a variable first

<xsl:variable name="source" select="."/>
at the top level of your stylesheet

then you can do this
[@Org = $source/Report/OrganisationNameEn]
0
 

Author Comment

by:mariita
ID: 39197398
How about the third predicate? How would I specify the context?

[@NAICS = NAICSCodeEn]
0
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 2000 total points
ID: 39197405
Hi, I am just giving suggestions, so I have not looked into your stylesheet in detail (will only have time tomorrow, will visit family soon)

As soon as you start looking inside the lookup table, the context will be set to that lookup table, so outside the source XML
In order to get the old context back, you need storage in a variable
You can store the current context at every level

just store away (at the right context) the NAICSCodeEn value as $this-NAICSCodeEn
and use it in the lookup [@NAICS = $this-NAICSCodeEn]
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Problem How to write an Xquery that works like a SQL outer join, providing placeholders for absent data on the outer side?  I give a bit more background at the end. The situation expressed as relational data Let’s work through this.  I’ve …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question