Solved

Display dynamic XML in HTML table

Posted on 2006-11-16
21
802 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 10
21 Comments
 
LVL 12

Expert Comment

by:enachemc
ID: 17963541
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
ID: 17966172
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
ID: 17968540
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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

Author Comment

by:ksummers
ID: 17968804
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
ID: 17968839
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
ID: 17981272
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
ID: 17981702
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
ID: 18039939
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
ID: 18047398
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
ID: 18048018
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
 

Author Comment

by:ksummers
ID: 18048151
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
ID: 18048393
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
ID: 18048552
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
ID: 18048658
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
ID: 18048725
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
ID: 18048970
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
ID: 18049183
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
ID: 18049261
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
ID: 18049284
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
ID: 18049379
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
ID: 18049405
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Get the parent node - XMLTYPE 9 137
XML Removal- Powershell 4 40
XML Document XPath with Namespaces 3 40
Force XMLSEQUENCE to return empty tags for null values. 10 45
This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

739 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