Xml for txt file records

Question

I am having a scenario to push the text records to db to track some information on an individual.

My text file contains the data as shown below:

logonID|agentName|modify|exception|start|stop|externalID        
14051286759|Jacks, Monica|1373477063|Break|01:45|02:00|USWMAJ43
14051286759|Jacks, Monica|1373477063|Break|06:10|06:25|USWMAJ43
14051286759|Jacks, Monica|1373477063|Lunch|03:45|04:30|USWMAJ43
14051286759|Jacks, Monica|1373477063|Open|00:00|01:45|USWMAJ43
14051286759|Jacks, Monica|1373477063|Open|02:00|03:45|USWMAJ43
14051286759|Jacks, Monica|1373477063|Open|04:30|06:10|USWMAJ43
14051286759|Jacks, Monica|1373477063|Open|06:25|08:30|USWMAJ43

Now i need to create an XML for Break,Lunch and open(number of open for a record may vary about 5 to 6 entries) as:

<info>
<break>
<break_1>01:45-02:00</break_1>
<break_2>06:10-06:25</break_1>
</break>
<lunch>03:45-04:30</lunch>
<open>
<open_1>00:00-01:45</open_1>
<open_2>02:00-03:45</open_2>
<open_3>04:30-06:10</open_3>
<open_4>06:25-08:30</open_4>
</open>
</info>

Thanks in Advance

What have you tried to achieve your solution? You are more likely to get some help if you state your attempts rather than a development request. — Matthew Warman, Aug 05 '13 at 08:25
Actually i was inserting the whole txt records as it was in file...But while retrieving and displaying the details of an individual its was difficult for me.. Any help would be helpful... — user1093452, Aug 05 '13 at 08:28
Start by having a look at this [answer](http://stackoverflow.com/questions/8205089/is-xslt-good-approach-to-convert-text-to-xml-structure#8207251), which explains about converting text to xml. — Matthew Warman, Aug 05 '13 at 08:55

score 0 · Accepted Answer · edited May 23 '17 at 12:11

XSLT

To use the xslt you pass the file location to pText parameter, for example file:///C:/data.txt :

<xsl:stylesheet version="2.0"
        xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output omit-xml-declaration="yes" indent="yes"/>
    <xsl:param name="pText" />
    <xsl:variable name="smallcase" select="'abcdefghijklmnopqrstuvwxyz'"/>
    <xsl:variable name="uppercase" select="'ABCDEFGHIJKLMNOPQRSTUVWXYZ'"/>
    <xsl:variable name="vText" select="unparsed-text($pText)"/>
    <xsl:variable name="vLines" select="tokenize($vText, '\r?\n')[position() > 1][normalize-space()]"/>
    <xsl:template match="/">
        <info>
            <xsl:for-each-group select="$vLines" group-by="tokenize(.,'\|')[4]">
                <xsl:variable name="vKey" select="translate(current-grouping-key(), $uppercase, $smallcase)"/>
                <xsl:element name="{$vKey}">
                    <xsl:for-each select="current-group()">
                        <xsl:variable name="vValues" select="tokenize(.,'\|')"/>
                        <xsl:variable name="vPos" select="position()"/>
                        <xsl:element name="{$vKey}_{$vPos}">
                            <xsl:value-of select="concat($vValues[5],'-',$vValues[6])"/>
                        </xsl:element>
                    </xsl:for-each>
                </xsl:element>
            </xsl:for-each-group>
        </info>
    </xsl:template>
</xsl:stylesheet>

Input:

logonID|agentName|modify|exception|start|stop|externalID        
14051286759|Jacks, Monica|1373477063|Break|01:45|02:00|USWMAJ43
14051286759|Jacks, Monica|1373477063|Break|06:10|06:25|USWMAJ43
14051286759|Jacks, Monica|1373477063|Lunch|03:45|04:30|USWMAJ43
14051286759|Jacks, Monica|1373477063|Open|00:00|01:45|USWMAJ43
14051286759|Jacks, Monica|1373477063|Open|02:00|03:45|USWMAJ43
14051286759|Jacks, Monica|1373477063|Open|04:30|06:10|USWMAJ43
14051286759|Jacks, Monica|1373477063|Open|06:25|08:30|USWMAJ43

Output:

<info>
    <break>
        <break_1>01:45-02:00</break_1>
        <break_2>06:10-06:25</break_2>
    </break>
    <lunch>
        <lunch_1>03:45-04:30</lunch_1>
    </lunch>
    <open>
        <open_1>00:00-01:45</open_1>
        <open_2>02:00-03:45</open_2>
        <open_3>04:30-06:10</open_3>
        <open_4>06:25-08:30</open_4>
    </open>
</info>

C#

Reference 1 and Reference 2

XsltArgumentList argsList = new XsltArgumentList();
argsList.AddParam("pText", "", "file:///C:/data.txt");
XPathDocument myXPathDoc = new XPathDocument(myXmlFile) ;
XslCompiledTransform myXslTrans = new XslCompiledTransform();
myXslTrans.Load(myStyleSheet);
XmlTextWriter myWriter = new XmlTextWriter("result.html",null);
myXslTrans.Transform(myXPathDoc,argsList,myWriter);

Xml for txt file records

1 Answers1

XSLT

C#