gerop Posted October 5, 2007 Share Posted October 5, 2007 Hello,I'm still very new to XML and XSLT.I'm trying to load a XML file into Excel but am not getting the results I want. Sometimes there are ClientTransactions where there are more than one set of Query and Response. When I open this in Excel, for each line of data, the first two columns span 2 or more rows. I want to put each ClientTransaction into one row of data only - even when there are more then one Query/Response. 'AmpHash013Semicolon' is actually "&_#_013_;" (minus the _) but I couldn't put that in as it doesn't show. Is it some sort of character for line break maybe? I've tried removing them from the XML but it doesn't help. Please give me some pointers on how to solve this.Thanks. <?xml version="1.0" encoding="UTF-8"?><?xml-stylesheet type="text/xsl" href="format_dcs.xsl"?><siplog><ClientTransaction Type="Execute"> <Timestamp>20070903-050031.751</Timestamp> <Query>FECK |TSMH2596Y03SEPKULKCH |TK2329901016635C1@ABCD/GUIDOMR |TK2329902016636C1@ABCD/JENNIFERMRS</Query> <Response> 2329901016635 ABCD/GUIDOMR TICKET NOT FOUND'AmpHash013Semicolon'</Response></ClientTransaction><ClientTransaction Type="Execute"> <Timestamp>20070903-050042.927</Timestamp> <Query>FECK |TSMH0612L03SEPSINKUL |TK6292100263333C2@MIN/ABCGMR</Query> <Response> 6292100263333 MIN/ABCGMR TICKET NOT FOUND'AmpHash013Semicolon'</Response></ClientTransaction><ClientTransaction Type="Execute"> <Timestamp>20070903-050048.156</Timestamp> <Query>FECK |TSMH2565T03SEPMYYKUL |TK2322403430247C1@KIKI/EDMUNDMR</Query> <Response> ALL PROCESSED'AmpHash013Semicolon'</Response></ClientTransaction><ClientTransaction Type="Execute"> <Timestamp>20070903-050114.935</Timestamp> <Query>FET/ 2322402394800</Query> <Response> ELECTRONIC TICKET FILE'AmpHash013Semicolon' 2322402394800 NAME-LAI/SIKITENGMS'AmpHash013Semicolon' PNR-JX7338'AmpHash013Semicolon' ISSUED 20AUG07/MHWWWIB'AmpHash013Semicolon' STATUS'AmpHash013Semicolon' 01 KULBKI MH 2616 S 03SEP 1400 OK SAPOWMY 20K CHECK-IN'AmpHash013Semicolon'</Response> <Query>FETB |NSO |CN1 |TRSDCS DELETE</Query> <Response> ELECTRONIC TICKET FILE'AmpHash013Semicolon' 2322402394800 NAME-LAI/SIKITENGMS'AmpHash013Semicolon' PNR-JX7338'AmpHash013Semicolon' ISSUED 20AUG07/MHWWWIB'AmpHash013Semicolon' STATUS'AmpHash013Semicolon' 01 KULBKI MH 2616 S 03SEP 1400 OK SAPOWMY 20K OPEN'AmpHash013Semicolon'</Response></ClientTransaction><ClientTransaction Type="Execute"> <Timestamp>20070903-050125.888</Timestamp> <Query>FECK |TSMH2709Y03SEPSDKBKI |TK2322409398195C2@DAIM/LLLEGI</Query> <Response> ALL PROCESSED'AmpHash013Semicolon'</Response></ClientTransaction> This is xslt code I'm using. <?xml version="1.0" encoding="UTF-8"?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"><!-- Output as an HTML file. --><xsl:output method="html"/><!-- For SIP DCS Transactions --><xsl:template match="/"> <html> <body> <h3 align="center">SIP DCS TRANSACTIONS</h3> <br /> <p> <b> Number of Transactions : </b> <xsl:value-of select="count(siplog/ClientTransaction)" /> <br /> <b> Number of Errors : </b> <xsl:value-of select="count(siplog/ClientTransaction/ExceptionRaised)" /> </p> <table border="1" frame="box"> <colgroup> <col width="0*" /> <col width="0*" /> <col width="300"/> <col width="400"/> <tr bgcolor="#A63DDF"> <th align="center">Timestamp(UTC)</th> <th align="center">Exception</th> <th align="center">Query</th> <th align="center">Response</th> </tr> <xsl:for-each select="siplog/ClientTransaction"> <tr> <td align="left" valign="top"><xsl:value-of select="Timestamp"/></td> <td align="left" valign="top"><xsl:value-of select="ExceptionRaised/@RC"/></td> <td align="left" valign="top"> <ul> <xsl:for-each select="Query"> <li><xsl:value-of select="current()" /></li> </xsl:for-each> </ul> </td> <td align="left" valign="top"> <ul> <xsl:for-each select="Response"> <li><xsl:value-of select="current()" /></li> </xsl:for-each> </ul> </td> </tr> </xsl:for-each> </colgroup> </table> </body> </html></xsl:template></xsl:stylesheet> Link to comment Share on other sites More sharing options...
boen_robot Posted October 5, 2007 Share Posted October 5, 2007 Errr... you do know that your stylesheet is supposed to generate XHTML output, right? Not really a thing for Excel (though I suppose it's exportable).If it's not going to mess up the query itself, you can perform whitespace normalization to remove the entity and all extra whitespace, like so: <li><xsl:value-of select="normalize-space(.)" /></li> That should deal with queries and responses that use more then a single line. As for when you have more then a single set of query and response... It may be an Excel problem. The only thing you may do is write the queries and responses in plain text, rather then on list items and hope for the best.BTW, </colgroup> shuold come right after <col width="400"/>. Link to comment Share on other sites More sharing options...
gerop Posted October 8, 2007 Author Share Posted October 8, 2007 Errr... you do know that your stylesheet is supposed to generate XHTML output, right? Not really a thing for Excel (though I suppose it's exportable).If it's not going to mess up the query itself, you can perform whitespace normalization to remove the entity and all extra whitespace, like so:<li><xsl:value-of select="normalize-space(.)" /></li> That should deal with queries and responses that use more then a single line. As for when you have more then a single set of query and response... It may be an Excel problem. The only thing you may do is write the queries and responses in plain text, rather then on list items and hope for the best.BTW, </colgroup> shuold come right after <col width="400"/>. ya Not sure how else to get the xml file into excel form thoughOk thanks ! I'll try it out Link to comment Share on other sites More sharing options...
aalbetski Posted October 9, 2007 Share Posted October 9, 2007 you could also use XSLT to create a CSV text file. Excel will recognize the format and parse the cells automatically. Link to comment Share on other sites More sharing options...
Reg Edit Posted October 11, 2007 Share Posted October 11, 2007 Errr... you do know that your stylesheet is supposed to generate XHTML output, right? Not really a thing for Excel ya Not sure how else to get the xml file into excel form thoughboen_robot's point was that your particular xsl is written to generate html output, which is more suitable for a web page than Excel, as it includes presentation and layout information.Excel will in fact open your original xml file (once the closing </siplog> tag is added). So the task for your xsl is to transform that xml not into html, but into a different xml (or csv) structure that suits your needs when opening it in Excel (namely, handling the query/response pairs).Your data contains one or more query/response pairs per transaction. You would like each transaction on a single row in Excel. You don't say what you want to happen to the multiple queries and responses - possible options include (1) concatenating all query/response pairs in one cell, (2) showing only the first query/response pair or (3) ignoring all query/response pairs. (However, depending on how you will use the data in Excel, it might be more meaningful to keep a row for each query/response pair but repeat the transaction id in the first cell.) All of these (and more) could be done with the right xsl transformation.The simplest transformation is (3) above and I have included the xsl for this below so you can use it as a starting point:<?xml version="1.0" encoding="UTF-8"?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:output method="xml" /> <xsl:template match="/"> <ClientTransactions> <xsl:for-each select="siplog/ClientTransaction"> <ClientTransaction> <Type> <xsl:value-of select="@Type"/> </Type> <Timestamp> <xsl:value-of select="Timestamp"/> </Timestamp> </ClientTransaction> </xsl:for-each> </ClientTransactions> </xsl:template></xsl:stylesheet> This produces the following xml output, which Excel will open as one transaction per row: <?xml version="1.0" encoding="utf-8"?><ClientTransactions> <ClientTransaction> <Type>Execute</Type> <Timestamp>20070903-050031.751</Timestamp> </ClientTransaction> <ClientTransaction> <Type>Execute</Type> <Timestamp>20070903-050042.927</Timestamp> </ClientTransaction> <ClientTransaction> <Type>Execute</Type> <Timestamp>20070903-050048.156</Timestamp> </ClientTransaction> <ClientTransaction> <Type>Execute</Type> <Timestamp>20070903-050114.935</Timestamp> </ClientTransaction> <ClientTransaction> <Type>Execute</Type> <Timestamp>20070903-050125.888</Timestamp> </ClientTransaction></ClientTransactions> Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.