Link to home
Start Free TrialLog in
Avatar of flfmmqp
flfmmqp

asked on

XML Table

I am very new to XML and have some knowledge of CSS, HTML and ASP.  I have a piece of software called ArcGIS that has an example that uses XML, XLS and CSS to create a report.  I would like to build on what they are doing but I do not know much about XML.  It looks like the code is creating some some XML on the fly and then adding it to the CSS page to create an HTML file.  I would like to continue to do this.  My problem is that the example assumes their are two columns and I will never know how many columns I have until I do some sort of row count.  I know this is asking a lot but I was wondering if someone could give me a simple example of what I am trying to accomplish.  I need to be able to create the xml table on the fly with the number of columns being the same as the row count plus 1.  The first row will be the column attributes.  

Example - 4 stores(row count of 4)

Store Name   Store1   Store2   Store3   Store4
Sales 04        1000     2000       500       100
Sales 03        5000     3000       750       250
Sales 02        1000     2000       250       1000

I am attaching the current CSS and a little of the VB code from ArcView.    Again I am just looking for a simple example.  I learn best from breaking down code.

VB code
'Get Store Name
        xmlString = xmlString & vbTab & "<Store_Name>" & vbCrLf
        xmlString = xmlString & vbTab & "<name>" & strheader & "</name>" & vbCrLf
           
           
            strStoreName = rstSelection.Fields("Store Name").Value
            'Get columns and values
            xmlString = xmlString & vbTab & "<attribute>" & vbCrLf
            xmlString = xmlString & vbTab & vbTab & "<name>" & "Store Name" & "</name>" & vbCrLf
            xmlString = xmlString & vbTab & vbTab & "<value>" & strStoreName & "</value>" & vbCrLf
            xmlString = xmlString & vbTab & "</attribute>" & vbCrLf

            strGetYear1 = rstSelection.Fields("" & strYear1 & "").Value
            'Get columns and values
            xmlString = xmlString & vbTab & "<attribute>" & vbCrLf
            xmlString = xmlString & vbTab & vbTab & "<name>" & strYear1 & "</name>" & vbCrLf
            xmlString = xmlString & vbTab & vbTab & "<value>" & strGetYear1 & "</value>" & vbCrLf
            xmlString = xmlString & vbTab & "</attribute>" & vbCrLf

  ' Use the xmlpropset to convert the xml to html, using the stylesheet supplied
  ixmlPropSet.SetXml xmlString
  'orig ixmlPropSet.SaveAsFile app.Path & "\style.xsl", "", True, app.Path & "\temp.html"
  ixmlPropSet.SaveAsFile "c:\GISReports\MallsGIS_StyleSheet.xsl", "", True, "c:\GISReports\MallsGISReport.html"


'CSS

<?xml version="1.0"?>
<xsl:stylesheet  version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/">
  <html>
  <HEAD>
 
  <STYLE>
    BODY {  color:#FB;}
    <!-- container box -->
    .boxDef   {font-size:12; background-color:#FF; border:'1.5pt solid #6495ED'; position:relative; top:4;}
   
   
    <!-- categories -->
    .category  {font-size:12; color:#191970; font-family:Verdana,sans-serif; line-height:135%;
                font-weight:bold; cursor:hand; margin:0.15in 0.15in 0.15in 0.15in}
    .categorySel  {font-size:12; color:#6495ED; font-family:Verdana,sans-serif; line-height:135%;
                   font-weight:bold; cursor:hand; margin:0.15in 0.15in .0.15in 0.15in}
                   
    <!-- attributes -->
    .attribute  {font-size:12; color:#2E8B57; font-weight:bold; cursor:hand}
    .attributeSel {font-size:12;color:#006000; font-weight:bold; cursor:hand}

    <!-- Store_Names -->
    .Store_Name  {font-size:12; font-family:Verdana,sans-serif; line-height:135%;
               color:#191970; margin:0.15in 0.15in 0.15in 0.15in}  
 
 
title      { font: 10pt Arial; color: #000000; text-decoration: none; line-height: 12pt;}
body      { font: 10pt Arial; color: #000000; text-decoration: none; line-height: 12pt;}
table      { font: 8pt Arial; color: #000000; text-decoration: none; line-height: 10pt;}
 
 
  </STYLE>

  <SCRIPT LANGUAGE="JScript">
  <xsl:comment>
  <![CDATA[

  //changes the color of the tabs or headings that you can click
  //when the mouse hovers over them
  function doHilite()  {
    var e = window.event.srcElement;
    if (e.className == "category") {
      e.className = "categorySel";
    }
    else if (e.className == "categorySel") {
        e.className = "category";
    }
    else if (e.className == "attribute") {
        e.className = "attributeSel";
    }
    else if (e.className == "attributeSel") {
        e.className = "attribute";
    }
    window.event.cancelBubble = true;
  }
 
   //centers the thumbnail
  function position() {
    var e;
    e = document.all("thumbnail");
    if (e != null) {
      b = document.body;
      w1 = b.clientWidth - 80;
      w2 = w1 - thumbnail.width;
      var margin = Math.floor(w2 * .5) + 30;
      thumbnail.style.visibility = "hidden";
      thumbnail.style.marginLeft = margin;
      thumbnail.style.visibility = "visible";
    }
  }
 
  //hide or show the text assoicated with the heading that was clicked
  function hideShowGroup(e)  {
    var theGroup = e.children[0];
    if (theGroup.style.display == "none") {
      theGroup.style.display="block";
    }
    else {
      theGroup.style.display="none";
    }
    window.event.cancelBubble = true;
  }
  ]]>
  </xsl:comment>
  </SCRIPT>
  </HEAD>
 
  <body STYLE="BODY" onload="position();" onresize="position();">
 
   <IMG ID="thumbnail" ALIGN="absmiddle" STYLE="height:50;
            border:'2 outset #FFFFFF'; position:relative"
            SRC="fds logo.bmp">
   </IMG>

    <!-- Define the box which will contain the contents -->
    <DIV ID="Group" CLASS="boxDef" height="95%">

    <!-- Categories -->
    <xsl:for-each select="identifyData/ftrCategory">
   
      <DIV CLASS="category" onmouseover="doHilite()" onmouseout="doHilite()"
        onclick="hideShowGroup(this)"><xsl:value-of select="MallName"/>
     
      <!-- Store_Names -->
      <xsl:for-each select="Store_Name">
     
        <DIV CLASS="Store_Name" STYLE="display:block">
       
        <!-- Attributes -->
        <xsl:variable name="ftrName" select="name"/>
            <DIV CLASS="attribute" onmouseover="doHilite()" onmouseout="doHilite()"
               onclick="hideShowGroup(this)"><xsl:value-of select="$ftrName"/>

              <table border="1" width="90%">
                
                <xsl:for-each select="attribute">
                      
                      <!-- Attributes -->
                      <tr>
                      <xsl:variable name="var" select="name"/>
                      <xsl:value-of select="$var"/>
                      
                      <xsl:variable name="val" select="value"/>
              <xsl:choose>      
                  <xsl:when test="substring($var, 1, 5) = 'hyper'">
                    <xsl:variable name="link" select="concat('http://', $val)"/>
                    <a><xsl:attribute name = "href">
                           <xsl:value-of select="$link"/>
                           </xsl:attribute>
                           <xsl:value-of select="$val"/>
                        </a>
                  </xsl:when>
                  <xsl:otherwise>
                    <td><xsl:value-of select="$val"/></td>
                  </xsl:otherwise>
              </xsl:choose>  
              </tr>
             
                </xsl:for-each> <!-- select="attribute" -->
              </table>
            </DIV>
      </DIV>
          </xsl:for-each> <!-- select="Store_Name" -->
        </DIV>
    </xsl:for-each> <!-- select="identifyData/ftrCategory" -->
  </DIV>
  </body>
 
</html>
</xsl:template>
</xsl:stylesheet>
Avatar of rdcpro
rdcpro
Flag of United States of America image

I have just such an example. It builds a table based on an abitrary number of columns.  I set this with a  combo box so you can play with it.  But you can use a variable that counts the number of columns needed like:

<xsl:variable name="numCols" select="count(Xpath/to/row/element)"/>

I'm not sure what you mean by "rows" but the idea is that you set a variable to count whatever nodes you're wanting to display as columns.  The example is located at:

http://rdcpro.com/Members/rdcpro/snippets/xpathgrouping/

If you post a sample of the XML you want to use, and some info about what element maps to the columns, I'll work up a better example.

By the way, it's not called a CSS, but rather it's an XSLT.  There is a little CSS embedded in that XSLT, but the stylesheet is an XSLT.


Regards,
Mike Sharp
Oh, I guess it's <Attribute> that governs the column?  Then instead of:

 <!-- Sets the number of columns in the table -->
 <xsl:param name="pNumCells" select="3"/>

your XSLT will have:

<xsl:variable name="pNumCells" select="count(/StoreName/Attribute)"/>

This will allow you to use the XPath approach to building the number of columns you need.  This assumes that StoreName is the root element.  If not, then you need to modify the XPath as needed.

Regards,
Mike Sharp
Avatar of flfmmqp
flfmmqp

ASKER

Thanks for the response Mike.  I will try out your suggestions right now.  
Avatar of Daydreams
Hi flfmmqp,

As far as your style declarations:

BODY {  color:#FB;}  <--the hex code should be 6 digits.
 background-color:#FF;  <--the hex code may be 3 digits if it is, for example: #ffffff can be #fff.

font-size:12;  <-- you need to add a unit to your value, such as 12px, 12em etc.

border:'1.5pt solid #6495ED';  <--you can remove the quotes

Also, you have specified the body element twice.

Avatar of flfmmqp

ASKER

Do you guys have any suggestions a good XML book to purchase that would be good for what I am trying to accomplish.
Avatar of flfmmqp

ASKER

rdcpro:

Well what I am tring to do is come up with something like the table below.  Where the table structure is created in the XSLT (?) and then from my VB code I create the values and then merge it with the XSLT.  I hope that makes sense.

Example - 4 stores(colum count of 4)(this count varies though)

Store Name   Store1   Store2   Store3   Store4
Sales 04        1000     2000       500       100
Sales 03        5000     3000       750       250
Sales 02        1000     2000       250       1000

This is where I am but I am having all kinds of problems.  Below is the code from VB and the XSLT


'Code from VB
Private Sub CreateSimpleStyleSheet()
   Dim xmlPropSet As New esriGeoDatabase.XmlPropertySet
  Dim ixmlPropSet As esriGeoDatabase.IXmlPropertySet2
  Set ixmlPropSet = xmlPropSet

 
  ' Create a string to write the xml into
  Dim xmlString As String
 
            xmlString = xmlString & "<root><row><pg>" & "  Store Name" & "</pg>" & vbCrLf
            xmlString = xmlString & "<date>" & "Store 1" & "</date>" & vbCrLf
            'xmlString = xmlString & "<2004>" & "100mil" & "</2004>" & vbCrLf
            xmlString = xmlString & "<tix>" & "150mil" & "</tix></row>" & vbCrLf
           
           
            xmlString = xmlString & "<row><pg>" & "  Store Name" & "</pg>" & vbCrLf
            xmlString = xmlString & "<date>" & "Store 2" & "</date>" & vbCrLf
            'xmlString = xmlString & "<2004>" & "200Mil" & "</2004>" & vbCrLf
            xmlString = xmlString & "<tix>" & "250mil" & "</tix></row></root>" & vbCrLf


  ' Use the xmlpropset to convert the xml to html, using the stylesheet supplied
  ixmlPropSet.SetXml xmlString
  'orig ixmlPropSet.SaveAsFile app.Path & "\style.xsl", "", True, app.Path & "\temp.html"
  ixmlPropSet.SaveAsFile "C:\GISReports\SimpleTable.xslt", "", True, "c:\GISReports\MallsGISReport.html"

  ' Navigate to the html file just created
  WebBrowser1.Navigate2 "c:\GISReports\MallsGISReport.html"
  'FlashFeature m_pActiveView, pClosestFeature
End Sub

'Code from XSLT

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output method="html" version="1.0" indent="yes"/>
      <xsl:key name="kPGColumn" match="row" use="pg"/>
      <xsl:key name="kDateRow" match="row" use="date"/>
      <xsl:key name="k2004" match="row" use="2004"/>
      <xsl:template match="root">
            <Table border="1" cellpadding="4" style="border-collapse:collapse;">
                  <tbody>
                        <tr>
                              <td>&#160;</td>
                              <xsl:apply-templates select="row[generate-id() = generate-id(key('kPGColumn', pg))]" mode="head1"/>
                        </tr>
                        <xsl:apply-templates select="row[generate-id() = generate-id(key('kDateRow', date))]" mode="bodyRow"/>
                  </tbody>
            </Table>
      </xsl:template>
      
      <xsl:template match="row" mode="head1">
            <th colspan="1"><xsl:value-of select="pg"/></th>
      </xsl:template>

      <xsl:template match="row" mode="bodyRow">
            <tr>
                  <td><xsl:value-of select="date"/></td>
                  <xsl:apply-templates select="parent::root/row[pg = key('kPGColumn', pg)/pg][date = current()/date]" mode="bodyCell"/>
            </tr>
      </xsl:template>

      <xsl:template match="row" mode="bodyCell">
            <td><xsl:value-of select="tix"/></td>
      </xsl:template>
      
      <xsl:template match="row" mode="bodyCell">
            <td><xsl:value-of select="2004"/></td>
      </xsl:template>

      
</xsl:stylesheet>
Avatar of flfmmqp

ASKER

Okay I have simplified everything.  

Right now it looks like this:

Dillard            Kenwood TWC        100mil 150mil
Lazarus           Kenwood Laz          200mil 220mil
Bloomingdales Kenwood Bloomies  300Mil 350mil

I want it to look like this:
Dillard                  Lazarus           Bloomindales
Kenwood TWC      Kenwood Laz   Kenwood Bloomies
100mil                 200mil             300Mil  
150mil                 220mil             350mil

But I want it to look like this



'VB
Private Sub CreateSimpleStyleSheet()
   Dim xmlPropSet As New esriGeoDatabase.XmlPropertySet
  Dim ixmlPropSet As esriGeoDatabase.IXmlPropertySet2
  Set ixmlPropSet = xmlPropSet

  ' Create a string to write the xml into
  Dim xmlString As String
 
            xmlString = xmlString & "<root><row><Anchor>" & "Dillard" & "</Anchor>" & vbCrLf
            xmlString = xmlString & "<StoreName>" & "  Kenwood TWC" & "</StoreName>" & vbCrLf
            xmlString = xmlString & "<Year1>" & "100mil" & "</Year1>" & vbCrLf
            xmlString = xmlString & "<Year2>" & "150mil" & "</Year2></row>" & vbCrLf
           
           
             xmlString = xmlString & "<row><Anchor>" & "Lazarus" & "</Anchor>" & vbCrLf
           xmlString = xmlString & "<StoreName>" & "  Kenwood Laz" & "</StoreName>" & vbCrLf
            xmlString = xmlString & "<Year1>" & "200mil" & "</Year1>" & vbCrLf
            xmlString = xmlString & "<Year2>" & "220mil" & "</Year2></row>" & vbCrLf
           
           
             xmlString = xmlString & "<row><Anchor>" & "Bloomingdales" & "</Anchor>" & vbCrLf
           xmlString = xmlString & "<StoreName>" & "  Kenwood Bloomies" & "</StoreName>" & vbCrLf
            xmlString = xmlString & "<Year1>" & "300Mil" & "</Year1>" & vbCrLf
            xmlString = xmlString & "<Year2>" & "350mil" & "</Year2></row></root>" & vbCrLf


  ' Use the xmlpropset to convert the xml to html, using the stylesheet supplied
  ixmlPropSet.SetXml xmlString
  'orig ixmlPropSet.SaveAsFile app.Path & "\style.xsl", "", True, app.Path & "\temp.html"
  ixmlPropSet.SaveAsFile "C:\GISReports\SimpleTable.xslt", "", True, "c:\GISReports\MallsGISReport.html"

  ' Navigate to the html file just created
  WebBrowser1.Navigate2 "c:\GISReports\MallsGISReport.html"
  'FlashFeature m_pActiveView, pClosestFeature
End Sub


'XLST
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output method="html" version="1.0" indent="yes"/>
      <xsl:key name="kPGColumn" match="row" use="pg"/>
      <xsl:key name="kDateRow" match="row" use="date"/>
      <xsl:template match="row">
      <xsl:param name="pNumCells" select="3"/>
            <Table border="1" cellpadding="4" style="border-collapse:collapse;">
                  <tbody>
                              <td><xsl:value-of select="Anchor"/></td>
                              <td><xsl:value-of select="StoreName"/></td>
                              <td><xsl:value-of select="Year1"/></td>
                              <td><xsl:value-of select="Year2"/></td>
                        <xsl:apply-templates select="row[generate-id() = generate-id(key('kDateRow', date))]" mode="bodyRow"/>
                  </tbody>
            </Table>
      </xsl:template>
</xsl:stylesheet>


Can you save me some time, and post the actual XML you're trying to transform?

Avatar of flfmmqp

ASKER

This is where I am now and will be till tomorrow morning.  Any help you can give would be greatly appreciated.

I believe what is going on here is that the "xmlstring" is mixing with the Stylesheet to create the html page.  So that is the closest actual xmlthat I have.  

Private Sub CreateSimpleStyleSheet()
   Dim xmlPropSet As New esriGeoDatabase.XmlPropertySet
  Dim ixmlPropSet As esriGeoDatabase.IXmlPropertySet2
  Set ixmlPropSet = xmlPropSet

 
  ' Create a string to write the xml into
  Dim xmlString As String
  Dim xcount As Integer
 
'xmlString = xmlString & "<count><rowcount><rowcount>3</rowcount></count>" & vbCrLf
 
xcount = 1
  Do Until xcount = 4
   strStoreName = "Dillard" & xcount
    strAnchor = "Anchor" & xcount
    If xcount = 1 Then
        xmlString = xmlString & "<root><row><" & strAnchor & ">" & strStoreName & "</" & strAnchor & ">"
    Else
       xmlString = xmlString & "<" & strAnchor & ">" & strStoreName & "</" & strAnchor & ">"
       'MsgBox "<" & strAnchor & ">" & strStoreName & "</" & strAnchor & ">"
    End If
  xcount = xcount + 1
  Loop
xmlString = xmlString & "</row>"
           
           
xcount = 1
  Do Until xcount = 4
   strStoreName = "Store" & xcount
    strStore = "Store" & xcount
    If xcount = 1 Then
        xmlString = xmlString & "<row><" & strStore & ">" & strStoreName & "</" & strStore & ">"
    Else
       xmlString = xmlString & "<" & strStore & ">" & strStoreName & "</" & strStore & ">"
       'MsgBox "<" & strStore & ">" & strStoreName & "</" & strStore & ">"
    End If
  xcount = xcount + 1
  Loop
xmlString = xmlString & "</row>"
           
            'xmlString = xmlString & "<root><row><Anchor>" & strStoreName & "</Anchor>" & vbCrLf
            'xmlString = xmlString & "<Anchor1>" & strStoreName & "</Anchor1>" & vbCrLf
            'xmlString = xmlString & "<Anchor2>" & strStoreName & "</Anchor2></row>" & vbCrLf
           
           
            'xmlString = xmlString & "<row><StoreName>" & "  Kenwood TWC" & "</StoreName>" & vbCrLf
            'xmlString = xmlString & "<StoreName>" & "  Kenwood Laz" & "</StoreName>" & vbCrLf
            'xmlString = xmlString & "<StoreName>" & "  Kenwood Bloomies" & "</StoreName></row>" & vbCrLf
           
           
            xmlString = xmlString & "<row><Year1>" & "100mil" & "</Year1>" & vbCrLf
            xmlString = xmlString & "<Year1>" & "200mil" & "</Year1>" & vbCrLf
            xmlString = xmlString & "<Year1>" & "300Mil" & "</Year1></row>" & vbCrLf
           
            xmlString = xmlString & "<row><Year2>" & "150mil" & "</Year2>" & vbCrLf
            xmlString = xmlString & "<Year2>" & "220mil" & "</Year2>" & vbCrLf
            xmlString = xmlString & "<Year2>" & "350mil" & "</Year2></row></root>" & vbCrLf
           


  ' Use the xmlpropset to convert the xml to html, using the stylesheet supplied
  ixmlPropSet.SetXml xmlString
  'orig ixmlPropSet.SaveAsFile app.Path & "\style.xsl", "", True, app.Path & "\temp.html"
  ixmlPropSet.SaveAsFile "C:\GISReports\SimpleTable.xslt", "", True, "c:\GISReports\MallsGISReport.html"

  ' Navigate to the html file just created
  WebBrowser1.Navigate2 "c:\GISReports\MallsGISReport.html"
  'FlashFeature m_pActiveView, pClosestFeature
End Sub

'*****************************
'XSLT
'*****************************
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output method="html" version="1.0" indent="no"/>
      <xsl:key name="kAnchorColumn" match="row" use="Anchor"/>
      <xsl:template match="row">            
            <Table border="1">
                  <tbody>
                  <TR><TD><xsl:value-of select="Anchor1"/></TD>
                  <TD><xsl:value-of select="Anchor2"/></TD>
                  <TD><xsl:value-of select="Anchor3"/></TD></TR>
                  
                  <TR><TD><xsl:value-of select="Store1"/></TD>
                  <TD><xsl:value-of select="Store2"/></TD>
                  <TD><xsl:value-of select="Store3"/></TD></TR>
                  </tbody>
            </Table>
      </xsl:template>

</xsl:stylesheet>
ASKER CERTIFIED SOLUTION
Avatar of rdcpro
rdcpro
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
which by the way is based on:

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="storenametable.xslt"?>
<root>
      <row>
            <Anchor>Dillard</Anchor>
            <StoreName>Kenwood TWC</StoreName>
            <Year1>100mil</Year1>
            <Year2>150mil</Year2>
      </row>
      <row>
            <Anchor>Lazarus</Anchor>
            <StoreName>Kenwood Laz</StoreName>
            <Year1>200mil</Year1>
            <Year2>220mil</Year2>
      </row>
      <row>
            <Anchor>Bloomingdales</Anchor>
            <StoreName>Kenwood Bloomies</StoreName>
            <Year1>300Mil</Year1>
            <Year2>350mil</Year2>
      </row>
</root>


and produces:

Dillard                  Lazarus           Bloomindales
Kenwood TWC      Kenwood Laz   Kenwood Bloomies
100mil                 200mil             300Mil  
150mil                 220mil             350mil
Avatar of flfmmqp

ASKER

I had to modify it a little bit (which is expected) but that seems to have done the trick.

Thanks for your help.  I would have given you points if I could have.