Solved

Display dynamic XML in HTML table

Posted on 2006-11-16
21
789 Views
Last Modified: 2013-11-19
I need help displaying "dynamic" XML in a "dynamic" table.  I need the table headers to be based on one of the XML CLIENT_NAME field, however, the client name is repeated because the client is given a business priority for each state.  In addition, each team could have MANY or just one client based on what my SQL returns -- hence, the "dynamic" table headers.  Is there a way to code dynamic headers?  Most of application is coded in JavaScript with some ASP.

Here is what I need the table to look like:

            BofA      HomeEq      NCM      Wamu
      Ak      3      3            2      2
      Al      2      2            1      1
      Ga      2      2            1      1
      Ky      
                                                ___________________

            REMOVE CLIENT            ADD CLIENT


Which will be based on XML that looks something like this:

<Results>
<Users>
  <User TEAM_KEY="405" EMPLOYEE_KEY="727" FIPS_STATE_CODE="1" ORGANIZATION_NUMBER="2" CLIENT_NAME="Wells Fargo" PRIORITY="2" CREATE_DATE="6/28/2006 1:37:05 PM" CREATE_EMPLOYEE_KEY="6115" LAST_CHANGE_DATE="6/28/2006 1:37:05 PM" LAST_CHANGE_EMPLOYEE_KEY="6115" />
  <User TEAM_KEY="405" EMPLOYEE_KEY="727" FIPS_STATE_CODE="2" ORGANIZATION_NUMBER="2" CLIENT_NAME="Wells Fargo" PRIORITY="2" CREATE_DATE="6/28/2006 1:37:05 PM" CREATE_EMPLOYEE_KEY="6115" LAST_CHANGE_DATE="6/28/2006 1:37:05 PM" LAST_CHANGE_EMPLOYEE_KEY="6115" />
  <User TEAM_KEY="405" EMPLOYEE_KEY="727" FIPS_STATE_CODE="4" ORGANIZATION_NUMBER="2" CLIENT_NAME="Wells Fargo" PRIORITY="2" CREATE_DATE="6/28/2006 1:37:05 PM" CREATE_EMPLOYEE_KEY="6115" LAST_CHANGE_DATE="6/28/2006 1:37:05 PM" LAST_CHANGE_EMPLOYEE_KEY="6115" />
  <User TEAM_KEY="405" EMPLOYEE_KEY="727" FIPS_STATE_CODE="5" ORGANIZATION_NUMBER="2" CLIENT_NAME="WAMU" PRIORITY="2" CREATE_DATE="6/28/2006 1:37:05 PM" CREATE_EMPLOYEE_KEY="6115" LAST_CHANGE_DATE="6/28/2006 1:37:05 PM" LAST_CHANGE_EMPLOYEE_KEY="6115" />
  <User TEAM_KEY="405" EMPLOYEE_KEY="727" FIPS_STATE_CODE="6" ORGANIZATION_NUMBER="2" CLIENT_NAME="WAMU" PRIORITY="2" CREATE_DATE="6/28/2006 1:37:05 PM" CREATE_EMPLOYEE_KEY="6115" LAST_CHANGE_DATE="6/28/2006 1:37:05 PM" LAST_CHANGE_EMPLOYEE_KEY="6115" />
  <User TEAM_KEY="405" EMPLOYEE_KEY="727" FIPS_STATE_CODE="8" ORGANIZATION_NUMBER="2" CLIENT_NAME="BofA" PRIORITY="2" CREATE_DATE="6/28/2006 1:37:05 PM" CREATE_EMPLOYEE_KEY="6115" LAST_CHANGE_DATE="6/28/2006 1:37:05 PM" LAST_CHANGE_EMPLOYEE_KEY="6115" />
  <User TEAM_KEY="405" EMPLOYEE_KEY="727" FIPS_STATE_CODE="9" ORGANIZATION_NUMBER="2" CLIENT_NAME="BofA" PRIORITY="2" CREATE_DATE="6/28/2006 1:37:05 PM" CREATE_EMPLOYEE_KEY="6115" LAST_CHANGE_DATE="6/28/2006 1:37:05 PM" LAST_CHANGE_EMPLOYEE_KEY="6115" />
</User>
</Users>
</Results>


I hope that makes sense.  Let me know if I need to clarify.  Thanks!
0
Comment
Question by:ksummers
  • 10
  • 10
21 Comments
 
LVL 12

Expert Comment

by:enachemc
Comment Utility
what does the first column represent ?
Is XSLT a solution for you? If yes, post an XML example and the associated desired table, and I'll make the XSLT for you.
0
 

Author Comment

by:ksummers
Comment Utility
The first column are states based on the FIPS_STATE_CODE which I convert in my JavaScript, so you can just use that field in the XSLT.  XSLT is definitely an option for me, but I have not coded with it before.  I can't wait to see your example, so I can familairize myself with it.

I have been using table structures with the XML as the datasrc.  Basically, I need a table using the XML that I posted here.  I really hope you can help.  Thanks!
0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
Hi ksummers,

let us take this in small steps and start with the XSLT
If that is OK, we can show you how to call the XSLT from your ASP javascript server
and then as a last step we can pass a parameter to the XSLT through the javascript to add or delete clients
(if that is what you want to do)

for testing the XSLT,
download a test license of an IDE, such as www.oxygenxml.com or www.stylusstudio.com
so you can run the XSLT

I assumed that the numbers in the table were counts, if it is not, correct me and I will correct the XSLT

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:key name="user-by-cn" match="User" use="@CLIENT_NAME"/>
    <xsl:key name="user-by-fsc" match="User" use="@FIPS_STATE_CODE"/>
    <xsl:output indent="yes"/>
    <xsl:template match="/">
        <html>
            <body>
                <table border="1">
                    <xsl:apply-templates select="//Users"/>
                </table>
            </body>
        </html>
    </xsl:template>
   
    <xsl:template match="Users">
        <th>
            <xsl:for-each select="User[generate-id() = generate-id(key('user-by-cn', @CLIENT_NAME)[1])]">
                <xsl:sort select="@CLIENT_NAME" order="ascending"/>
                <td>
                    <xsl:value-of select="@CLIENT_NAME"/>
                </td>
            </xsl:for-each>
        </th>
        <xsl:for-each select="User[generate-id() = generate-id(key('user-by-fsc', @FIPS_STATE_CODE)[1])]">
            <xsl:variable name="this_state" select="@FIPS_STATE_CODE"/>
            <tr>
                <td><xsl:value-of select="@FIPS_STATE_CODE"/></td>
                <xsl:for-each select="//User[generate-id() = generate-id(key('user-by-cn', @CLIENT_NAME)[1])]">
                    <xsl:sort select="@CLIENT_NAME" order="ascending"/>
                    <td>
                        <xsl:value-of select="count(key('user-by-cn', @CLIENT_NAME)[@FIPS_STATE_CODE = $this_state])"/>
                    </td>
                </xsl:for-each>
            </tr>
        </xsl:for-each>
         
    </xsl:template>

</xsl:stylesheet>

All this XSLT does is grouping the company names to form the top row
and then grouping the states (allthough in your example each state only comes once)
in order to group the companies again in the inner loop

the grouping mechanism I use is muenchian grouping,
a pattern that you can learn about here:
http://jenitennison.com/xslt/grouping/muenchian.xml

to get started with xslt:
http://www.w3schools.com/xsl/default.asp

Cheers!

Geert
0
 

Author Comment

by:ksummers
Comment Utility
Oh wow, thank you!  I am almost there.  They are not counts though.  The FIPS_CODE looks correct in the first column (basically this a number representation for the state).  You can ignore it, just leave as numbers.  I need the PRIORITY to fill the columns of the table for each state/code.

Thanks!
0
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 500 total points
Comment Utility
ksummers,

that would be like this then

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:key name="user-by-cn" match="User" use="@CLIENT_NAME"/>
    <xsl:key name="user-by-fsc" match="User" use="@FIPS_STATE_CODE"/>
    <xsl:output indent="yes"/>
    <xsl:template match="/">
        <html>
            <body>
                <table border="1">
                    <xsl:apply-templates select="//Users"/>
                </table>
            </body>
        </html>
    </xsl:template>
   
    <xsl:template match="Users">
        <th>
            <xsl:for-each select="User[generate-id() = generate-id(key('user-by-cn', @CLIENT_NAME)[1])]">
                <xsl:sort select="@CLIENT_NAME" order="ascending"/>
                <td>
                    <xsl:value-of select="@CLIENT_NAME"/>
                </td>
            </xsl:for-each>
        </th>
        <xsl:for-each select="User[generate-id() = generate-id(key('user-by-fsc', @FIPS_STATE_CODE)[1])]">
            <xsl:variable name="this_state" select="@FIPS_STATE_CODE"/>
            <tr>
                <td><xsl:value-of select="@FIPS_STATE_CODE"/></td>
                <xsl:for-each select="//User[generate-id() = generate-id(key('user-by-cn', @CLIENT_NAME)[1])]">
                    <xsl:sort select="@CLIENT_NAME" order="ascending"/>
                    <td>
                        <xsl:value-of select="key('user-by-cn', @CLIENT_NAME)[@FIPS_STATE_CODE = $this_state][1]/@PRIORITY"/>
                    </td>
                </xsl:for-each>
            </tr>
        </xsl:for-each>
         
    </xsl:template>

</xsl:stylesheet>
0
 

Author Comment

by:ksummers
Comment Utility
Geert, fantastic.  Thank you so much!
Can you show me how to pass a parameter to the XSLT through the JavaScript to add or delete clients?
0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
for that you have to use a template processor

you can find a good example of code for using a template processor
with good explanaition of the code in the accepted answer of this question
http://www.experts-exchange.com/Web/Web_Languages/XML/Q_21478250.html

cheers

Geert
0
 

Author Comment

by:ksummers
Comment Utility
Geert, is there a way in XSL to make all the priorities which are "2" in this example to be dropdowns with a selection of other priorities (1, 2, 3, 4 etc)??  Thanks!
0
 

Author Comment

by:ksummers
Comment Utility
Nevermind.  I tried this instead and it seems to work, but now I need to figure out how to make the dropdowns unique to the dynamic data, as well as update the XML for saving in database.

 <xsl:for-each select="//User[generate-id() = generate-id(key('user-by-cn', @CLIENT_NAME)[1])]">
                    <xsl:sort select="@CLIENT_NAME" order="ascending"/>
                    <td>
<select name="selRoles">  
                        <option>
                        <xsl:value-of select="key('user-by-cn', @CLIENT_NAME)[@STATE_ABBREVIATION = $this_state][1]/@PRIORITY"/>
                        </option>
                        <option>0</option>
                        <option>1</option>
                        <option>2</option>
                        <option>3</option>
                        <option>4</option>
                       
                           </select>
                    </td>
                </xsl:for-each>
0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
well,
I could not give too much attention to this earlier, sorry

in order to tell ASP to use the new value, you need a form in the HTML
and each select requires a unique name in order to know in your form processing asp
which database field requires updating

here is how you can do this

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:key name="user-by-cn" match="User" use="@CLIENT_NAME"/>
    <xsl:key name="user-by-fsc" match="User" use="@FIPS_STATE_CODE"/>
    <xsl:output indent="yes"/>
    <xsl:template match="/">
        <html>
            <body>
                <form action="someAction" method="post">
                    <table border="1">
                        <xsl:apply-templates select="//Users"/>
                    </table>
                </form>
            </body>
        </html>
    </xsl:template>
   
    <xsl:template match="Users">
        <th>
            <xsl:for-each select="User[generate-id() = generate-id(key('user-by-cn', @CLIENT_NAME)[1])]">
                <xsl:sort select="@CLIENT_NAME" order="ascending"/>
                <td>
                    <xsl:value-of select="@CLIENT_NAME"/>
                </td>
            </xsl:for-each>
        </th>
        <xsl:for-each select="User[generate-id() = generate-id(key('user-by-fsc', @FIPS_STATE_CODE)[1])]">
            <xsl:variable name="this_state" select="@FIPS_STATE_CODE"/>
            <tr>
                <td><xsl:value-of select="@FIPS_STATE_CODE"/></td>
                <xsl:for-each select="//User[generate-id() = generate-id(key('user-by-cn', @CLIENT_NAME)[1])]">
                    <xsl:sort select="@CLIENT_NAME" order="ascending"/>
                    <td>
                        <xsl:if test="key('user-by-cn', @CLIENT_NAME)[@FIPS_STATE_CODE = $this_state]">
                            <xsl:call-template name="createOptionList">
                                <xsl:with-param name="curVal" select="key('user-by-cn', @CLIENT_NAME)[@FIPS_STATE_CODE = $this_state][1]/@PRIORITY"/>
                                <xsl:with-param name="curState" select="$this_state"/>
                                <xsl:with-param name="curComp" select="@CLIENT_NAME"/>
                            </xsl:call-template>
                        </xsl:if>
                        <xsl:value-of select="key('user-by-cn', @CLIENT_NAME)[@FIPS_STATE_CODE = $this_state][1]/@PRIORITY"/>
                    </td>
                </xsl:for-each>
            </tr>
        </xsl:for-each>
       
    </xsl:template>
   
    <xsl:template name="createOptionList">
        <xsl:param name="curVal"/>
        <xsl:param name="curState"/>
        <xsl:param name="curComp"/>
        <select name="ID-{$curState}-{$curComp}"  >
            <option value="1"  >
                <xsl:if test="$curVal = 1"><xsl:attribute name="selected">selected</xsl:attribute></xsl:if>
                <xsl:text>1</xsl:text></option>
            <option value="2"  >
                <xsl:if test="$curVal = 2"><xsl:attribute name="selected">selected</xsl:attribute></xsl:if>
                <xsl:text>2</xsl:text></option>
            <option value="3"  >
                <xsl:if test="$curVal = 3"><xsl:attribute name="selected">selected</xsl:attribute></xsl:if>
                <xsl:text>3</xsl:text></option>
            <option value="4"  >
                <xsl:if test="$curVal = 4"><xsl:attribute name="selected">selected</xsl:attribute></xsl:if>
                <xsl:text>4</xsl:text></option>
            <option value="5"  >
                <xsl:if test="$curVal = 5"><xsl:attribute name="selected">selected</xsl:attribute></xsl:if>
                <xsl:text>5</xsl:text></option>
            </select>
    </xsl:template>
   
</xsl:stylesheet>

The only thing I do here is creating an option list
with a "selected" on the already selected value
and within each "select" I have an ID that is a combination of the state and the company to know the position in the matrix

cheers

Geert
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:ksummers
Comment Utility
But is this updating the XML?  How can I see it?

Also, how do I extract the select with ASP to update the database with the XML?

Sorry, I am just not seeing how this updates the XML on the fly.
0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
This doesn't update the XML on the fly
This creates a form that gives you a bunch of parameters in a ASP
In your form processing ASP you can then update the XML, by using the parameters,
or in this case, even better since you have all the information passed back,
regenerate the XML from scratch

CAn you do form processing in ASP
you can find some examples here
http://www.w3schools.com/asp/asp_ref_request.asp

cheers

Geert
0
 

Author Comment

by:ksummers
Comment Utility
i was afraid of having to create the xml from scratch again, but i should be able to do it in the ASP form processing.  I will need to add a save button, etc and separate asp not to be displayed.  

I guess where I am confused is how in the world am I supposed to know the name of the form variables if they are created from the dynamic xml??  I see how you make the parameters in the XSL, but how do I know their names and access them in the ASP???

Thanks!
0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
well, you will get a request parameter
from this <select>

               <td><select name="ID-6-WAMU">
                     <option value="1">1</option>
                     <option value="2" selected>2</option>
                     <option value="3">3</option>
                     <option value="4">4</option>
                     <option value="5">5</option></select>2
               </td>

eg. when you change that to 3
the request parameter "ID-6-WAMU" = "3"

from the parameter you can derive (using some substring methods)
state = 6
company = WAMU

so you can generate a tag
<User FIPS_STATE_CODE="6" CLIENT_NAME="WAMU" PRIORITY="3" />
The other attributes will likely follow from defaults

I added the ID- as a start for the select ID
this could help you to iterate over all the request parameters that start with ID
to trigger the creation of an element

cheers

Geert
0
 

Author Comment

by:ksummers
Comment Utility
But how will I know the name of the parameter "ID-6-WAMU"?  How can I request that when it was created from the XML?

Is there a list of the form variables I can iterate thru somehow after hitting the submit button?  I just don't see how I can request the parameter in the ASP without knowing the name of the parameter.
0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
I gave each select a name
<select name="ID-6-WAMU">
If you select in this cell the value 3
pushing the submit button will post a set of namevalue pairs as a request object to the server
something like
ID-6-WAMU=3
all you have to do is get this namevalue pairs from the request object and use them
That is why I gave all the selects a name and why I put everything in a form
cheers
0
 

Author Comment

by:ksummers
Comment Utility
I understand that.  LOL
What I am saying is, since the data is dynamic it's not always going to be ID-6-WAMU.  I can't just hardcode request parameter ID-6-WAMU since it COULD be ID-6-BOFA or ID-6-SUNTRUST or ID-6-WELLSFARGO.

What I am trying to understand/asking, is there a way to find out these parameter namevalues before requesting them in the ASP??

Thanks!
0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
No need to hardcode anything

every user in the original XML
has a corresponding cell in the table
Each cell in the table has a select with a name based on the state and the company
If you POST the form
all the selects will pass a value with the request (whether you changed the value or not)
In the request you will receive the values for every select in the form,
so in extensio you receive all the information that originally was in the XML, as a name value pair
of course you have to remap
the part before the first hyphen is a dummy
the part between the two hyphens is the state code
the part after the last hyphen is the company name
So you actually get triples from the request form
(6, BOFA, 2)
(6, SUNTRUST, 3)
(6, WELLSFARGO, 2)
You can easily construct the XML, iterating over the triples
So you can build this dynamically, no need for hardcoding anything

cheers

Geert
Geert
0
 

Author Comment

by:ksummers
Comment Utility
Maybe I am confused about how the form information is being passed.  Can you give me an ASP example of how it is doing a request on the form data?  Like request('ID-6-WAMU')???

Thanks!
0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
it is as simple as this

company=Request.Form("ID-6-WAMU")
and company will contain the result, eg. 2

you need to find a way to iterate over all that have an "ID-" in front of them.
I am not much of an ASP developer,
so I think you should just find some code through google,
I will give it a shot now

cheers
0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
To fetch data from XML with multiple root nodes 6 61
XML error 2 41
How to silent print from safari browser 6 134
Problem to event 3 49
Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

744 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now