Need to convert a XML based on a unique ID along with Date change

Lipsita · November 28th, 2015, 01:43 PM

Hi,
Input File:
<?xml version="1.0" encoding="UTF-8"?>
<DATA>
<RECORDS>
<Group>
<ID>19358</ID>
<Name>JSMITH</Name>
<Grp>MANAGER</Grp>
<FName>Alex</FName>
<LName>Johnson</LName>
</Group>
<Group>
<ID>19359</ID>
<Name>JAMES</Name>
<Grp>LEAD</Grp>
<FName>JAMES</FName>
<LName>HARPER</LName>
</Group>
<RECORDS>
<LOGIN>
<User>
<ID>19358</ID>
<Last>14/02/2013 10:15:00 AM</Last>
</User>
<User>
<ID>19359</ID>
<Last>14/01/2013 11:19:00 AM</Last>
</User>
</LOGIN>
</DATA>

Need OutPut as below:

<?xml version="1.0" encoding="UTF-8"?>
<DATA>
<RECORDS>
<Group>
<ID>19358</ID>
<Name>JSMITH</Name>
<Grp>MANAGER</Grp>
<FName>Alex</FName>
<LName>Johnson</LName>
<Last>14/02/2013 9:15:00</Last>
</Group>
<Group>
<ID>19359</ID>
<Name>JAMES</Name>
<Grp>LEAD</Grp>
<FName>JAMES</FName>
<LName>HARPER</LName>
<Last>14/01/2013 10:19:00</Last>
</Group>
<RECORDS>

</LOGIN>
</DATA>

Requirement: Need to convert XML based on unique ID "ID" along with subtracting 1 hr from time as the input is in Europe time(convert it to GMT and also 24 hrs time). Please help

mhkay · November 28th, 2015, 01:54 PM

What have you tried and where did you get stuck? (Without knowing where you got stuck, it's very hard to help you, short of writing the whole code for you, which is not something I do.)

And are you using XSLT 1.0 or 2.0?

Getting the data from the LOGIN record is a standard join operation and is easily achieved using the key() function.

Processing the date and time is easy enough in XSLT 2.0: use analyze-string (regular expressions) to convert the date/time to standard YYYY-MM-DDTHH:MM:SS format, then use adjust-dateTime-to-timezone to adjust the timezone. In XSLT 1.0, it's much more laborious.

Lipsita · November 28th, 2015, 02:11 PM

Hi,
Thanks for your reply.
I am able to convert the XML based on unique I'd.
Need help with the date part. Using substring I am subtracting one hr from the time but unable to convert it to 24 hr time as when the time is 1.41 pm its getting changed to 0.41 PM which is incorrevt. I am using XSLT 1.0. Let me know if you would need the code to suggest further.
Regards,
Lipsita

Lipsita · November 28th, 2015, 02:26 PM

Hi,
I am using XSLT 2.0 Please find the code below and help me refine it so that the date can be changed to 24 hr format and also gets converted from Europe time to GMT(subtract 1 hr)

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" indent="yes" />
<xsl:template match="/">

<DATA>

<xsl:for-each select="DATA/RECORDS/Group">
<xsl:variable name="ID1">
<xsl:value-of select="ID"/>
</xsl:variable>
<Group>
<ID>
<xsl:value-of select="$ID1"/>
</ID>
<Name>
<xsl:value-of select="Name"/>
</Name>
<Group>
<xsl:value-of select="Grp"/>
</Group>
<FName>
<xsl:value-of select="FName"/>
</FName>
<LName>
<xsl:value-of select="LName"/>
</LName>

<Last>

<xsl:call-template name="XXX">
<xsl:with-param name="ID1" select="$ID1"/>
</xsl:call-template>

</Last>

</Group>
</xsl:for-each>

</DATA>

</xsl:template>

<xsl:template name="XXX">
<xsl:param name="ID1"/>

<xsl:for-each select="//User">
<xsl:choose>
<xsl:when test="$ID1=ID">
<xsl:variable name="date" select="substring-before(Last, ' ')"/>
<xsl:variable name="M" select="substring-before($date, '/')"/>
<xsl:variable name="D-Y" select="substring-after($date, '/')"/>
<xsl:variable name="D" select="substring-before($D-Y, '/')"/>
<xsl:variable name="Y" select="substring-after($D-Y, '/')"/>
<xsl:variable name="time" select="substring-after(Login, ' ')"/>
<xsl:variable name="h" select="substring-before($time, ':')"/>
<xsl:variable name="m-s" select="substring-after($time, ':')"/>
<xsl:variable name="m" select="substring-before($m-s, ':')"/>
<xsl:variable name="s" select="substring-after($m-s, ':')"/>
<xsl:value-of select="concat($D, '/', $M, '/', $Y, ' ', xs:integer($h) -1, ':', $m, ':', $s)"/>
</xsl:when>
</xsl:choose>

</xsl:for-each>
</xsl:template>

</xsl:stylesheet>

mhkay · November 28th, 2015, 08:38 PM

I would normally use regex to parse and reformat the date but there's nothing wrong with using the substring functions and as you have to manipulate the AM/PM stuff it may be easier that way. The logic of AM/PM is just messy, there's no getting around it. I always forget the rules, if I ever knew them, for how midnight and noon are written in this system. Apart from the noon/midnight problem, you've basically got to divide it into four cases:

01:00 - 11:59 AM => 01:00 - 11:59
12:01 - 12:59 AM => 00:01 - 00:59
01:00 - 11:59 PM => 13:00 - 23:59
12:01 - 12:59 PM => 12:01 - 12:59

I don't know any way to do that other than writing a load of verbose code.

Lipsita · November 30th, 2015, 07:57 AM

Hi Michael,

Thank you for the reply. As i am very new to XSLT,Appreciate if you could help me changing the existing code including the part to convert it to 24 hr format and also handling the subtraction of 1 hr.

Regards,

Lipsita

mhkay · November 30th, 2015, 08:02 AM

Sorry, I can't write code for people or give personal tuition. You might find people with more time available on StackOverflow.

Martin Honnen · November 30th, 2015, 08:59 AM

Here is some code to use XSLT 2.0 to parse the date format into an xs: dateTime, to adjust the time zone and to show how to format it back to the original dateTime:

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  xmlns:mf="http://example.com/mf"
  exclude-result-prefixes="xs mf" 
  version="2.0">
 
<xsl:function name="mf:parse-date" as="xs:dateTime">
  <xsl:param name="input" as="xs:string"/>
  <xsl:sequence select="mf:parse-date($input, '')"/>
</xsl:function>

<xsl:function name="mf:parse-date" as="xs:dateTime">
  <xsl:param name="input" as="xs:string"/>
  <xsl:param name="timezone" as="xs:string"/>
  <xsl:analyze-string select="$input" regex="([0-9]+)/([0-9]+)/([0-9]+) ([0-9]+):([0-9]+):([0-9]+) (PM|AM)">
    <xsl:matching-substring>
        <xsl:variable name="day" select="xs:integer(regex-group(1))"/>
        <xsl:variable name="month" select="xs:integer(regex-group(2))"/>
        <xsl:variable name="year" select="xs:integer(regex-group(3))"/>
        <xsl:variable name="hours" as="xs:integer">
            <xsl:choose>
                <xsl:when test="regex-group(7) = 'PM' and regex-group(4) != '12'">
                    <xsl:sequence select="12 + xs:integer(regex-group(4))"/>
                </xsl:when>
                <xsl:otherwise><xsl:sequence select="xs:integer(regex-group(4))"/></xsl:otherwise>
            </xsl:choose>
        </xsl:variable>
        <xsl:variable name="minutes" select="xs:integer(regex-group(5))"/>
        <xsl:variable name="seconds" select="xs:integer(regex-group(6))"/>
        <xsl:variable name="dateTime" select="xs:dateTime(concat($year, '-', format-number($month, '00'), '-', format-number($day, '00'), 'T', format-number($hours, '00'), ':', format-number($minutes, '00'), ':', format-number($seconds, '00'), $timezone))" />
        <xsl:sequence select="$dateTime"/>
    </xsl:matching-substring>
  </xsl:analyze-string>
</xsl:function>

<xsl:template match="/">
  <xsl:variable name="sample-inputs" select="'14/02/2013 10:15:00 AM', '14/02/2013 10:15:00 PM'"/>
  <xsl:variable name="sample-dates" select="for $input in $sample-inputs return mf:parse-date($input, '+01:00')"/>
  <xsl:variable name="converted-dates" select="for $date in $sample-dates return adjust-dateTime-to-timezone($date, xs:dayTimeDuration('PT0H'))"/>
  <xsl:value-of select="$converted-dates"/>
  <xsl:text>
</xsl:text>
  <xsl:value-of select="for $date in $converted-dates return format-dateTime($date, '[D01]/[M01]/[Y0001] [H01]:[m01]:[s01]')"/>
</xsl:template>

</xsl:stylesheet>

Lipsita · December 1st, 2015, 02:26 PM

Hi,
I was successful in writing code for the req.

Input File:
<?xml version="1.0" encoding="UTF-8"?>
<DATA>
<RECORDS>
<Group>
<ID>19358</ID>
<Name>JSMITH</Name>
<Grp>MANAGER</Grp>
<FName>Alex</FName>
<LName>Johnson</LName>
</Group>
<Group>
<ID>19359</ID>
<Name>JAMES</Name>
<Grp>LEAD</Grp>
<FName>JAMES</FName>
<LName>HARPER</LName>
</Group>
<RECORDS>
<LOGIN>
<User>
<ID>19358</ID>
<Last>14/02/2013 10:15:00 AM</Last>
</User>
<User>
<ID>19359</ID>
<Last>14/01/2013 11:19:00 AM</Last>
</User>
</LOGIN>
</DATA>

Need OutPut as below:

<?xml version="1.0" encoding="UTF-8"?>
<DATA>
<RECORDS>
<Group>
<ID>19358</ID>
<Name>JSMITH</Name>
<Grp>MANAGER</Grp>
<FName>Alex</FName>
<LName>Johnson</LName>
<Last>14/02/2013 9:15:00</Last>
</Group>
<Group>
<ID>19359</ID>
<Name>JAMES</Name>
<Grp>LEAD</Grp>
<FName>JAMES</FName>
<LName>HARPER</LName>
<Last>14/01/2013 10:19:00</Last>
</Group>
<RECORDS>

</LOGIN>
</DATA>

XSLT code:

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xsl:output method="xml" indent="yes" />
<xsl:template match="/">
<DATA>
<xsl:for-each select="DATA/RECORDS/Group">
<xsl:variable name="ID1">
<xsl:value-of select="ID"/>
</xsl:variable>

<Group>
<ID>
<xsl:value-of select="$ID1"/>
</ID>

<Group>
<xsl:value-of select="Group"/>
</Group>

<FName>
<xsl:value-of select="FName"/>
</FName>
<LName>
<xsl:value-of select="LName"/>
</LName>

<Last>

<xsl:call-template name="XXX">
<xsl:with-param name="ID1" select="$ID1"/>
</xsl:call-template>
</Last>

</Group>
</xsl:for-each>
<xsl:variable name="nodes">
<xsl:value-of select="count(//Group)"/>
</xsl:variable>
<RecordCount>
<xsl:value-of select="$nodes"/>
</RecordCount>
</DATA>

</xsl:template>

<xsl:template name="XXX">
<xsl:param name="ID1"/>

<xsl:for-each select="//User">
<xsl:choose>
<xsl:when test="$ID1=ID">

<xsl:variable name="date" select="substring-before(//Last, ' ')"/>
<xsl:variable name="M" select="substring-before($date, '/')"/>
<xsl:variable name="D-Y" select="substring-after($date, '/')"/>
<xsl:variable name="D" select="substring-before($D-Y, '/')"/>
<xsl:variable name="Y" select="substring-after($D-Y, '/')"/>

<xsl:variable name="time-ampm" select="substring-after(//Last, ' ')"/>
<xsl:variable name="time" select="substring-before($time-ampm, ' ')"/>
<xsl:variable name="ampm" select="substring-after($time-ampm, ' ')"/>
<xsl:variable name="h" select="substring-before($time, ':')"/>
<xsl:variable name="m-s" select="substring-after($time, ':')"/>
<xsl:variable name="m" select="substring-before($m-s, ':')"/>
<xsl:variable name="s" select="substring-after($m-s, ':')"/>

<xsl:variable name="hh">
<xsl:choose>
<xsl:when test="$ampm = 'PM'">
<xsl:value-of select="format-number($h + 12, '00')"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="format-number($h, '00')"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>

<xsl:value-of select="concat( $D,'/', $M, '/', $Y, ' ', xs:integer($hh) -1, ':', $m, ':', $s)"/>

</xsl:when>
</xsl:choose>

</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

But still i am getting error as "Error: Failed to compile stylesheet. 1 error detected"

I feel the error is in the code section:

<xsl:variable name="hh">
<xsl:choose>
<xsl:when test="$ampm = 'PM'">
<xsl:value-of select="format-number($h + 12, '00')"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="format-number($h, '00')"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>

<xsl:value-of select="concat( $D,'/', $M, '/', $Y, ' ', xs:integer($hh) -1, ':', $m, ':', $s)"/>

Can anyone suggest what can be the issue??

mhkay · December 1st, 2015, 02:44 PM

Try to avoid writing

Code:

<xsl:variable name="USERNAME1">
   <xsl:value-of select="UserName"/>                    
</xsl:variable>

when you could write

Code:

<xsl:variable name="USERNAME1" select="UserName"/>

It's not just a question of verbosity. You're creating a temporary tree with a document node and a text node, which is an expensive operation, and all you actually need is a reference to the original node.