XLS namespace declaration for EXCEL

[bartekR]

Hi

I am a newbie to XSL therefore my question may not be very challenging to you guys but i would appreciate your help.

I have an XML file :
-----------------------------------------------------------
<?xml-stylesheet href="XSLforGilts.XSL" type="text/xsl"?>

<?xml version="1.0"?>


<GiltValuation>

<GiltInfo SecID="0221522" SecName="CONVERSION 9%" SecPrice="114.6088"/>
<GiltInfo SecID="0668594" SecName="STANDARD CHT.BK. 1999 6 3/4" SecPrice="100.6407"/>
<GiltInfo SecID="0676359" SecName="JTI(UK) FINANCE PLC 1999 6 5/8" SecPrice="101.2412"/>
<GiltInfo SecID="0786742" SecName="SCOTTISH MORTGAGE IT DEB G/R" SecPrice="168.5772"/>
<GiltInfo SecID="0836540" SecName="INTEG.ACCM.SVS. 2000 6.48%" SecPrice="104.3163"/>
<GiltInfo SecID="0892188" SecName="TREASURY STOCK 8%" SecPrice="117.2444"/>
<GiltInfo SecID="0893114" SecName="TREASURY STOCK 8 3/4" SecPrice="130.7322"/>
<GiltInfo SecID="0999799" SecName="TREASURY STOCK 8%" SecPrice="131.6309"/>
<GiltInfo SecID="123914" SecName="LIFFE-LONG GILT MAR 2008 (GBP" SecPrice="108.98"/>
<GiltInfo SecID="3088069" SecName="TREASURY STOCK 5%" SecPrice="103.5824"/>

</GiltValuation>

-------------------------------------------------------
and corresponding XSL (XSLforGilts.XSL)
which is used to extract securities where price is >100
--------------------------------------------------------
<?xml version="1.0" ?>
- <xsl:stylesheet version="1.0" xmlns:xsl="

http://www.w3.org/1999/XSL/Transform">

- <xsl:template match="GiltInfo[@SecPrice>110]">
- <p>
- <b>
<xsl:value-of select="@SecName" />
</b>
<xsl:value-of select="@SecPrice" />
</p>
</xsl:template>
</xsl:stylesheet>

-----------------------------------------------------

XSL does the trick for me - when i open my XML with internet explorer browser it shows me the names and prices of all securities matching criterion price>100.
However my true aim is to be able to import that extracted XML into excel.

I know that i will probably need to change the namespace declaration in XSL but at the moment i can't get the syntax right. Could anyone help me here by droping me a working line of code to replace this :

<xsl:stylesheet version="1.0" xmlns:xsl="

http://www.w3.org/1999/XSL/Transform">

Thanks in advance

 

[k5tm]

A good place to start is with Microsoft documentation:

http://office.microsoft.com/en-us/excel/HA102063961033.aspx

You might wish to create an XML Schema (XSD) for your document rather than use the XSLT. See the Working with XML maps section of the Microsoft document referenced above.

An XSD for the example XML input document you show would be quite simple. This might do:

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema"[/URL] elementFormDefault="qualified">
  <xs:element name="GiltValuation">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" ref="GiltInfo"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="GiltInfo">
    <xs:complexType>
      <xs:attribute name="SecID" use="required" type="xs:integer"/>
      <xs:attribute name="SecName" use="required"/>
      <xs:attribute name="SecPrice" use="required" type="xs:decimal"/>
    </xs:complexType>
  </xs:element>
</xs:schema>

Tom Morrison

http://www.liant.com

 

[bartekR]

thanks k5tm

The reason why i am using xsl is that i want to decide WHAT TO SHOW (rather than how) from xml file content.
Can you do this via XSD?

I have tried a slightly different approach to achieve the same thing (ie extract Security names & prices from xml where price>110 by using xsl stylesheet).

I have removed the reference to xsl from my xml file and tried MSXML transformNodeToObject method as per below:

Dim xmlobj As New DOMDocument
Dim xslobj As New DOMDocument
Dim sXMLFIle As String
Dim sXSLFile As String
Dim xmlResult As New DOMDocument
Dim xmlTostring As String

sXMLFIle = ActiveWorkbook.Path & "\" & "070308GiltVal.XML"
sXSLFile = "C:\Users\Acer User\Documents\EXcelDevelopment\XSLforGilts.xsl"

'Load xml & xsl file
xmlobj.Load sXMLFIle
xslobj.Load sXSLFile

'creates modified xml dom doc
xmlobj.transformNodeToObject xslobj, xmlResult
'MsgBox xmlResult.XML

'to string
'xmlToString = xmlobj.transformNode(xslobj)
'MsgBox x

xmlResult.Save (ActiveWorkbook.Path & "\" & "ModifiedXML.xml")

This runs without error but what i am getting is an empty xml file. Is there anything wrong with my XSL?
It works perfectly with a browser..

Slowly loosing faith in myself..

 

[tsuji]

The main problem is that your stylesheet is not outputting well-formed xml document. In fact, the parsing error is not controlled and is latent. This minimally revised stylesheet will perform. (You can add some space between the output value to make it more readable.)
[tt]
<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="[ignore]

http://www.w3.org/1999/XSL/Transform

[/ignore]">
<xslutput method="html" encoding="utf-8" omit-xml-declaration="yes" />
<xsl:template match="/">
<html>
<body>
<xsl:apply-templates select="/GiltValuation/GiltInfo" />
</body>
</html>
</xsl:template>
<xsl:template match="GiltInfo[@SecPrice>110]">
<p>
<b>
<xsl:value-of select="@SecName" />
</b>
<xsl:value-of select="@SecPrice" />
</p>
</xsl:template>
</xsl:stylesheet>
[/tt]

 

[bartekR]

thanks tsuji

HTML output works perfectly

The last thing i wanted to do is to get an output in xml format.

I have tried to edit your xsl stylesheet syntax and got into trouble again:
-----------------------------------------------------------
<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="

http://www.w3.org/1999/XSL/Transform">

<xslutput method="xml" encoding="utf-8" indent="yes"/>

<xsl:template match="/GiltValuation/GiltInfo[@SecPrice>130]">

<GiltInfo>

<SecName> <xsl:value-of select="@SecName"/> </SecName>
<SecPrice> <xsl:value-of select="@SecPrice"/> </SecPrice>

</GiltInfo>


</xsl:template>
</xsl:stylesheet>

----------------------------------------------------------
This again picks up the securities with prices>130 but obviously won't create me a well formed xml as root is missing and i am not quite sure how to nest it in the above xls syntax. Can you help here?

(hope i am not being a pain)

 

[tsuji]

Like this.
[tt]
<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="

http://www.w3.org/1999/XSL/Transform">

<xslutput method="xml" encoding="utf-8" indent="yes"/>
[blue]
<xsl:template match="/">
<root> <!-- or some name like GiltValuation -->
<xsl:apply-templates select="*" />
</root>
</xsl:template>
[/blue]
<xsl:template match="[highlight]G[/highlight]iltValuation/GiltInfo[@SecPrice>130]">

<GiltInfo>

<SecName> <xsl:value-of select="@SecName"/> </SecName>
<SecPrice> <xsl:value-of select="@SecPrice"/> </SecPrice>

</GiltInfo>


</xsl:template>
</xsl:stylesheet>
[/tt]
Do you get the essence of the matter?

 

[bartekR]

Thanks again, i think i am slowly getting a gist of these transformations.

Your suggestions saved me a lot of time. Cheers