Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

XML from Excel2002 meets XSLT

Posted on 2003-02-28
3
Medium Priority
?
341 Views
Last Modified: 2013-11-19
I have a problem transforming an XML file with an XSLT. The XML file was generated by using save as XML in Excel2002.

I can transform the files listed below if I change the namespace from 'http://www.w3.org/1999/XSL/Transform' to 'http://www.w3.org/TR/WD-xsl'. But I do not wish to use the 'http://www.w3.org/TR/WD-xsl' namespace because I will need xsl:key elements in my XSLT and they're not allowed.

Does anybody know how I can use the 'http://www.w3.org/1999/XSL/Transform' namespace to transform the given XML? (or maybe if there is another solution: using another namespace, using xsl:key elements with namespace 'http://www.w3.org/TR/WD-xsl')

I am becoming desperate on this one, thx in advance.

MY XSLT FILE (SIMPLE VERSION):
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="xml"/>
   <xsl:template match="/">
      <Test>
        <Test1><xsl:value-of select="Workbook/DocumentProperties/Author"/></Test1>
      </Test>
   </xsl:template>
</xsl:stylesheet>

XML FILE FROM EXCELL:
<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>HFagard</Author>
  <LastAuthor>HFagard</LastAuthor>
  <Created>2003-02-27T14:18:59Z</Created>
  <Version>10.4219</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <DownloadComponents/>
  <LocationOfComponents HRef="file:///\\"/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>8445</WindowHeight>
  <WindowWidth>15195</WindowWidth>
  <WindowTopX>0</WindowTopX>
  <WindowTopY>45</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"
   x:FullRows="1">
   <Row>
    <Cell><Data ss:Type="String">valueA1</Data></Cell>
    <Cell><Data ss:Type="String">valueB1</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">valueA2</Data></Cell>
    <Cell><Data ss:Type="String">valueB2</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>1</ActiveRow>
     <ActiveCol>1</ActiveCol>
     <RangeSelection>R1C1:R2C2</RangeSelection>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>
0
Comment
Question by:H_3k
  • 3
3 Comments
 
LVL 9

Expert Comment

by:sparkplug
ID: 8041282
Hi,

The 'http://www.w3.org/1999/XSL/Transform' namespace is only supported by version 3 of MSXML.

IE5 and IE5.5 come with versions 2 and 2.5 of MSXML.

To allow these browsers to work with MSXML3 you can install it in replace mode: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsdk30/htm/xmconinstallingmsxml30inreplacemode.asp.

Note you can not install MSXML 4.0 in replace mode.

Better still install IE6 which uses MSXML3 by default.

Alternatively you can do the transform in javascript in which case you can select which version of MSXML to use:

<html>
<body><script type="text/javascript">// Load XML
var xml = new ActiveXObject("Microsoft.XMLDOM")
xml.async = false
if (!xml.load("myxmlfile.xml"))
    alert('Error parsing xml: ' + xml.parseError);

// Load XSL
var xsl = new ActiveXObject("Microsoft.XMLDOM")
xsl.async = false
if (!xsl.load("myxslfile.xsl"))
    alert('Error parsing xsl: ' + xsl.parseError);

// Transform
document.write(xml.transformNode(xsl))</script>

</body>
</html>

Hope this helps,

>S'Plug<




0
 
LVL 9

Expert Comment

by:sparkplug
ID: 8041286
Sorry that last bit should have read:

<html>
<body><script type="text/javascript">// Load XML
var xml = new ActiveXObject("MSXML2.DOMDocument.3.0")
xml.async = false
if (!xml.load("myxmlfile.xml"))
   alert('Error parsing xml: ' + xml.parseError);

// Load XSL
var xsl = new ActiveXObject("MSXML2.DOMDocument.3.0")
xsl.async = false
if (!xsl.load("myxslfile.xsl"))
   alert('Error parsing xsl: ' + xsl.parseError);

// Transform
document.write(xml.transformNode(xsl))</script>

</body>
</html>
0
 
LVL 9

Accepted Solution

by:
sparkplug earned 450 total points
ID: 8041367
Doh! Just reread your question. I think the answer you are looking for is:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office">
<xsl:output method="xml"/>
  <xsl:template match="/">
     <Test>
       <Test1><xsl:value-of select="ss:Workbook/o:DocumentProperties/o:Author"/></Test1>
     </Test>
  </xsl:template>
</xsl:stylesheet>

As the XML file uses namespaces you have to specify these in the XSL document. You can use any prefix you like for the namespaces as long as they are is used consistently

>S'Plug<
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
The viewer will learn how to count occurrences of each item in an array.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

564 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