I'm importing from an XML file into SQL Server. Dates are stored like this:
<BIRTH_YEAR> 1943 </BIRTH_YEAR>
<BIRTH_MONTH> 04 </BIRTH_MONTH>
<BIRTH_DAY> 01 </BIRTH_DAY>
Of course I want a transform this to a simple date. This structure is repeated very consistently at various levels (RENEW_, EXPIRE_, etc.). However, the order of presentation (year, month, day) can vary. Here is my XSL solution:
<xsl:template name="date_value">
<xsl:param name="my_element_prefix"/>
<xsl:param name="DD"/>
<xsl:param name="MM"/>
<xsl:param name="YYYY"/>
<!-- Produce a field <BIRTH_DATE> -->
<xsl:element name="{concat($my_element_prefix,'_DATE')}">
<xsl:value-of select="concat($MM, '/', $DD, '/', $YYYY)"/>
</xsl:element>
</xsl:template>
I don't like it very much. I have to prepare the three date-component parameters in order to call the function. I expected to get those values from within the function. That would be accomplished by using $my_element_prefix to specify the source nodes. But using a parameter to build an XPath Expression isn't simple, it turns out.
I could consolidate these values as part of the load into SQL Server. But I'd rather front-load all the transformation on this 230 Mb file that I possibly can.
Your insights on how to approach this?
It looks like your background is in non-XSLT programming. :)
For a solid XSLT solution, you need to change your approach.
It's not entirely clear what you're trying to do. For sake of argument, I make the following assumptions for this sample:
- The
<BIRTH_YEAR>, <BIRTH_MONTH>, <BIRTH_DAY> are contained within a parent element, assumed here to be <PERSON>, and they occur only once in each instance of that parent element.
So given arbitrary data like the following:
<RECORDS>
<PERSON name="A">
<BIRTH_YEAR> 1943 </BIRTH_YEAR>
<BIRTH_MONTH> 04 </BIRTH_MONTH>
<BIRTH_DAY> 01 </BIRTH_DAY>
</PERSON>
<PERSON name="B">
<BIRTH_YEAR> 1957 </BIRTH_YEAR>
<BIRTH_MONTH> 08 </BIRTH_MONTH>
<BIRTH_DAY> 29 </BIRTH_DAY>
</PERSON>
<PERSON name="C">
<BIRTH_YEAR> 1802 </BIRTH_YEAR>
<BIRTH_MONTH> 12 </BIRTH_MONTH>
<BIRTH_DAY> 14 </BIRTH_DAY>
</PERSON>
<PERSON name="D">
<BIRTH_YEAR> 2015 </BIRTH_YEAR>
<BIRTH_MONTH> 04 </BIRTH_MONTH>
<BIRTH_DAY> 30 </BIRTH_DAY>
</PERSON>
</RECORDS>
And applying the following sample XSL snippet:
<xsl:template match="PERSON">
<!-- Copy element itself -->
<xsl:copy>
<!-- Copy all attributes -->
<xsl:copy-of select="@*"/>
<!-- Process birthdate info -->
<BIRTH_DATE>
<xsl:value-of select="normalize-space(BIRTH_MONTH)"/>
<xsl:text>/</xsl:text>
<xsl:value-of select="normalize-space(BIRTH_DAY)"/>
<xsl:text>/</xsl:text>
<xsl:value-of select="normalize-space(BIRTH_YEAR)"/>
</BIRTH_DATE>
</xsl:copy>
</xsl:template>
We would get this sample XML output:
<PERSON name="A"><BIRTH_DATE>04/01/1943</BIRTH_DATE></PERSON>
<PERSON name="B"><BIRTH_DATE>08/29/1957</BIRTH_DATE></PERSON>
<PERSON name="C"><BIRTH_DATE>12/14/1802</BIRTH_DATE></PERSON>
<PERSON name="D"><BIRTH_DATE>04/30/2015</BIRTH_DATE></PERSON>
The full XSLT sheet would work by iterating through each <PERSON> element in turn, passing the <PERSON> XML structure to the template above. Within the context of a single <PERSON> structure, there is just one <BIRTH_YEAR> element, just one <BIRTH_MONTH>, and just one <BIRTH_DAY>, so we can safely select what we want just by specifying the name. If there are multiples of these under a single parent element, things get more complicated. :)
The above is tested to work with XSLT version 1.0, the most commonly supported variety.
I wonder why you cannot do simply something like:
<xsl:template match="BIRTH_YEAR">
<BIRTH_DATE>
<xsl:value-of select="following-sibling::*[1]"/>
<xsl:text>/</xsl:text>
<xsl:value-of select="following-sibling::*[2]"/>
<xsl:text>/</xsl:text>
<xsl:value-of select="."/>
</BIRTH_DATE>
</xsl:template>
or, if you want it to be more generic:
<xsl:template match="*[contains(name(), '_YEAR')]">
<xsl:element name="{substring-before(name(), '_YEAR')}_DATE">
<xsl:value-of select="following-sibling::*[1]"/>
<xsl:text>/</xsl:text>
<xsl:value-of select="following-sibling::*[2]"/>
<xsl:text>/</xsl:text>
<xsl:value-of select="."/>
</xsl:element>
</xsl:template>
If you want a less verbose solution you could replace
<BIRTH_DATE>
<xsl:value-of select="normalize-space(BIRTH_MONTH)"/>
<xsl:text>/</xsl:text>
<xsl:value-of select="normalize-space(BIRTH_DAY)"/>
<xsl:text>/</xsl:text>
<xsl:value-of select="normalize-space(BIRTH_YEAR)"/>
</BIRTH_DATE>
with
<BIRTH_DATE>
<xsl:value-of select="translate(normalize-space(
concat(BIRTH_MONTH, ' ', BIRTH_DAY, ' ', BIRTH_YEAR)), ' ', '/')"/>
</BIRTH_DATE>
Having said that, it pains me to be writing code to produce output that will be misinterpreted by most non-American readers. Are you quite sure that's what you want to do?
Here is the answer I provided to the original question, which the OP intends to delete at any time, so I move the answer here. I do not touch on an XSLT 3.0 solution, and please, be reminded, that dynamic XPath evaluation is not a mandatory feature of XSLT 3.0 and some XSLT processors may choose not to implement it.
<xsl:variable name="MM">
<xsl:value-of select="concat('../', $which_date, '_MONTH')"/>
</xsl:variable>
The above returns a value of ../BIRTH_MONTH.
You want:
<xsl:variable name="MM" select="../*[name()=concat($which_date, '_MONTH')]"/>
Here is a complete transformation:
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:param name="which_date" select="'BIRTH'"/>
<xsl:template match="x">
<xsl:variable name="MM" select=
"../*[name()=concat($which_date, '_MONTH')]"/>
<xsl:value-of select="$MM"/>
</xsl:template>
<xsl:template match="text()"/>
</xsl:stylesheet>
When this transformation is applied on the following XML document (none was provided with the question):
<t>
<x>1</x>
<BIRTH_MONTH>12</BIRTH_MONTH>
</t>
the wanted, correct result is produced:
12
Update: Based on your other similar question, we see that you want a parameterized solution.
Here is one possible parameterized solution:
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output omit-xml-declaration="yes" indent="yes"/>
<xsl:strip-space elements="*"/>
<xsl:param name="pdateComponents" select="'|BIRTH_MONTH|BIRTH_DAY|BIRTH_YEAR|'"/>
<xsl:param name="poutputDateName" select="'BIRTH_DATE'"/>
<xsl:template match="/*">
<xsl:apply-templates select=
"*[*[contains($pdateComponents, concat('|',name(),'|'))]]" mode="dateHolder"/>
</xsl:template>
<xsl:template match="*" mode="dateHolder">
<xsl:copy>
<xsl:copy-of select="@*"/>
<xsl:element name="{$poutputDateName}">
<xsl:apply-templates select=
"*[contains($pdateComponents, concat('|',name(),'|'))]" mode="date">
<xsl:sort select="substring-before($pdateComponents, concat('|',name(),'|'))"/>
</xsl:apply-templates>
</xsl:element>
</xsl:copy>
</xsl:template>
<xsl:template match="*" mode="date">
<xsl:value-of select=
"concat(substring('/', 1 + (position() = 1)), normalize-space())"/>
</xsl:template>
</xsl:stylesheet>
When this XSLT 1.0 transformation is applied on the following XML document:
<RECORDS>
<PERSON name="A">
<BIRTH_YEAR> 1943 </BIRTH_YEAR>
<BIRTH_MONTH> 04 </BIRTH_MONTH>
<BIRTH_DAY> 01 </BIRTH_DAY>
</PERSON>
<PERSON name="B">
<BIRTH_YEAR> 1957 </BIRTH_YEAR>
<BIRTH_MONTH> 08 </BIRTH_MONTH>
<BIRTH_DAY> 29 </BIRTH_DAY>
</PERSON>
<PERSON name="C">
<BIRTH_YEAR> 1802 </BIRTH_YEAR>
<BIRTH_MONTH> 12 </BIRTH_MONTH>
<BIRTH_DAY> 14 </BIRTH_DAY>
</PERSON>
<PERSON name="D">
<BIRTH_YEAR> 2015 </BIRTH_YEAR>
<BIRTH_MONTH> 04 </BIRTH_MONTH>
<BIRTH_DAY> 30 </BIRTH_DAY>
</PERSON>
</RECORDS>
the result is:
<PERSON name="A">
<BIRTH_DATE>04/01/1943</BIRTH_DATE>
</PERSON>
<PERSON name="B">
<BIRTH_DATE>08/29/1957</BIRTH_DATE>
</PERSON>
<PERSON name="C">
<BIRTH_DATE>12/14/1802</BIRTH_DATE>
</PERSON>
<PERSON name="D">
<BIRTH_DATE>04/30/2015</BIRTH_DATE>
</PERSON>
Do note:
- The names of the date components are provided in a global parameter to the transformation -- they are not statically known.
- The name of the output element to contain the date is also provided as the value of another global parameter to the transformation.
- Even the order of the date components, using which to construct the date, is provided in the first component!
Thus, the above supplied parameter results in American dates output.
But if we provide this parameter:
<xsl:param name="pdateComponents" select="'|BIRTH_DAY|BIRTH_MONTH|BIRTH_YEAR|'"/>
then the result of the transformation contains dates in European format:
<PERSON name="A">
<BIRTH_DATE>01/04/1943</BIRTH_DATE>
</PERSON>
<PERSON name="B">
<BIRTH_DATE>29/08/1957</BIRTH_DATE>
</PERSON>
<PERSON name="C">
<BIRTH_DATE>14/12/1802</BIRTH_DATE>
</PERSON>
<PERSON name="D">
<BIRTH_DATE>30/04/2015</BIRTH_DATE>
</PERSON>
