Link to home
Start Free TrialLog in
Avatar of ksummers
ksummers

asked on

Display dynamic XML in HTML table

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!
Avatar of enachemc
enachemc
Flag of Afghanistan image

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.
Avatar of ksummers
ksummers

ASKER

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!
Avatar of Gertone (Geert Bormans)
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
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!
ASKER CERTIFIED SOLUTION
Avatar of Gertone (Geert Bormans)
Gertone (Geert Bormans)
Flag of Belgium 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
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?
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
https://www.experts-exchange.com/questions/21478250/XSL-question-What's-the-difference-between-a-template-processor-vs-DOMDocument.html

cheers

Geert
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!
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>
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
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.
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
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!
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
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.
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
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!
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
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!
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