Link to home
Start Free TrialLog in
Avatar of kaushal30
kaushal30

asked on

Transpose XML structure (convert XML into XML)

I have an XML structure that represents a shopping cart's columns and rows. However the incoming XML has Rows as child-elements of each Column element. We are trying to use XSL to display the shopping cart based on this XML.

The current XML (in a database column- MS SQL Server 2000) looks like this:

- <gvaPlannedOrder> (Root element- a shopping cart)
- <Col>
  <Row dataType="string">001</Row>
  <Row dataType="string">001</Row>
  <Row dataType="string">001</Row>
  </Col>
- <Col>
  <Row dataType="string">Part Description A</Row>
  <Row dataType="string">Part Description B</Row>
  <Row dataType="string">Part Description C</Row>
  </Col>
- <Col>
  <Row dataType="string">Part Number AXYZ</Row>
  <Row dataType="string">Part Number BXYZ</Row>
  <Row dataType="string">Part Number CXYZ</Row>
  </Col>
- <Col>
  <Row dataType="string">Additional Description AXYZ</Row>
  <Row dataType="string">Additional Description BXYZ</Row>
  <Row dataType="string">Additional Description CXYZ</Row>
  </Col>
- <Col> (these are the Prices)
  <Row dataType="string">533</Row>
  <Row dataType="number">8.35</Row>
  <Row dataType="string">5000</Row>
  </Col>
- <Col> (item quantities)
  <Row dataType="string">2</Row>
  <Row dataType="string">4</Row>
  <Row dataType="string">1</Row>
  </Col>
- <Col> (total price)
  <Row dataType="number">1066</Row>
  <Row dataType="number">33.4</Row>
  <Row dataType="number">5000</Row>
  </Col>
  </gvaPlannedOrder>

My question is- Is there a way I can transpose the XML structure where the Columns are child elements of the Row elements. i.e. Take the above input XML and convert into a XML structure like this :

 <gvaPlannedOrder>
- <Row id=1>
  <Col dataType="number">001</Col>
  <Col dataType="string">Part Description A</Col>
  <Col dataType="string">Part Number AXYZ</Col>
  <Col dataType="string">Additional Description AXYZ</Col>
  <Col dataType="string">533</Col>
  <Col dataType="string">2</Col>
  <Col dataType="number">1066</Col>
  </Row>
+ <Row id=2>
</Row>
+ <Row id=3>
</Row>
  </gvaPlannedOrder>

It does not matter where the transpose could be available (either as a DB function on SQL server or a XML Dom function or in XSL or any other). Any help would be really appreciated.

The overall goal is to loop through the xml using XSL (for-each) to display the items and render a shopping cart application. So if I have row based XML data (rather than column based), looping and rendering will be much easier (a straight parse). Thanks for all your help.

-kaushal30
Avatar of rdcpro
rdcpro
Flag of United States of America image

You could certainly do this in XSLT, but it's a surprising structure, given it's from SQL Server.   You must have had to jump through a few hoops to get it in that way.  Or, are you saying that entire XML structure is in a *single* column in the DB?  

Would it be possible to post the SQL Query that produces this?  If you are concerned about exposing your DB schema publicly, you can email it to me.  See my profile for my email address.

In the meantime, I'll see if I can put an XSLT together that efficiently transforms this.  But it's clear that the only approach will be based on each column and row's position.

I assume that this:

<Col>
  <Row dataType="string">001</Row>
  <Row dataType="string">001</Row>
  <Row dataType="string">001</Row>
</Col>

identifies the order number?

Regards,
Mike Sharp
I must say that this example particularly illustrates the power of XSLT.  This should do it for you quite efficiently:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
      <xsl:template match="gvaPlannedOrder">
            <xsl:copy>
                  <xsl:for-each select="Col[1]/Row">
                        <xsl:variable name="vPosition" select="position()"/>
                        <Row>
                              <xsl:for-each select="/gvaPlannedOrder/Col/Row[position() mod 3 = $vPosition mod 3]">
                                    <Col dataType="{@dataType}"><xsl:value-of select="."/></Col>
                              </xsl:for-each>
                        </Row>
                  </xsl:for-each>
            </xsl:copy>
      </xsl:template>
</xsl:stylesheet>


It produces this result:

<?xml version="1.0" encoding="UTF-8"?>
<gvaPlannedOrder>
      <Row>
            <Col dataType="string">001</Col>
            <Col dataType="string">Part Description A</Col>
            <Col dataType="string">Part Number AXYZ</Col>
            <Col dataType="string">Additional Description AXYZ</Col>
            <Col dataType="string">533</Col>
            <Col dataType="string">2</Col>
            <Col dataType="number">1066</Col>
      </Row>
      <Row>
            <Col dataType="string">001</Col>
            <Col dataType="string">Part Description B</Col>
            <Col dataType="string">Part Number BXYZ</Col>
            <Col dataType="string">Additional Description BXYZ</Col>
            <Col dataType="number">8.35</Col>
            <Col dataType="string">4</Col>
            <Col dataType="number">33.4</Col>
      </Row>
      <Row>
            <Col dataType="string">001</Col>
            <Col dataType="string">Part Description C</Col>
            <Col dataType="string">Part Number CXYZ</Col>
            <Col dataType="string">Additional Description CXYZ</Col>
            <Col dataType="string">5000</Col>
            <Col dataType="string">1</Col>
            <Col dataType="number">5000</Col>
      </Row>
</gvaPlannedOrder>


Frankly, I wouldn't bother transforming the XML from one form to another, then transforming it a second time into HTML.  I'd just use the template I showed you above to do the transform straight to HTML in one step.  Once you see how to pivot the structure, it's pretty simple.

Regards,
Mike Sharp
Avatar of kaushal30
kaushal30

ASKER

Hello Mike, Thanks for the response. I feel I am making some progress into this research.

1. The entire XML (<gvaPlannedOrder>) is indeed a part of a huge XML string that contains several other nodes. And yes that huge XML string is in a single column in a table (of type 'ntext').

2. I do not have access to the VB object that writes the XML string.

3. No that data does not reprsent the order number (sequential number of row). It actually represents the warehouse number for the parts in the shopping cart. Sorry about the confusion. And no, we do not have the information regarding the row number in the XML string. However the number of Row elements will be identical for every Col element. i.e. All Cols will have equal # of rows. And, in order to determine the current # of a row element within a Col element, we could use the position() function in XSL. Would that help?

My goal is to have faster performance and thats why I am trying to get the XML transposed. Because I feel that if I render the XML as is (then I would have to make several additional loops) and the purpose of using XSL will be defeated.

Thanks again,
Kaushal
For example, this produces a nice HTML table with some formatting and doesn't repeat order numbers:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
      <xsl:template match="gvaPlannedOrder">
            <table cellpadding="5px" border="1" style="border-collapse:collapse;">
                  <tbody>
                        <tr>
                              <th align="left">Order Number</th>
                              <th align="left">Item</th>
                              <th align="left">Part Number</th>
                              <th align="left">Description</th>
                              <th align="right">Price Each</th>
                              <th align="right">Quantity</th>
                              <th align="right">Extended</th>
                        </tr>
                  <xsl:for-each select="Col[1]/Row">
                        <xsl:variable name="vPosition" select="position()"/>
                        <tr>
                              <xsl:for-each select="/gvaPlannedOrder/Col/Row[position() mod 3 = $vPosition mod 3]">
                                    <td>
                                          <xsl:if test="position() &gt; 4">
                                                <xsl:attribute name="align">right</xsl:attribute>
                                                <xsl:value-of select="format-number(number(.), '$###,##0.00')"/>
                                          </xsl:if>
                                          <xsl:attribute name="align">right</xsl:attribute>
                                          <xsl:if test="position() &lt; 5 and ($vPosition = 1 or position() &gt; 1)">
                                                <xsl:value-of select="."/>
                                          </xsl:if>
                                    </td>
                              </xsl:for-each>
                        </tr>
                  </xsl:for-each>
                  </tbody>
            </table>
      </xsl:template>
</xsl:stylesheet>


Regards,
Mike Sharp
Hi Mike,

I have gotten your responses. Please give me some time to digest them. (as I am still little new to all this) And if they suffice what I need I will prompty come back and accept your answers. Thanks again Mike

Kaushal
Oh, I didn't see your last response.  Then modify my last example like:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
      <xsl:template match="gvaPlannedOrder">
            <table cellpadding="5px" border="1" style="border-collapse:collapse;">
                  <tbody>
                        <tr>
                              <th align="left">Order Number</th>
                              <th align="left">Item</th>
                              <th align="left">Part Number</th>
                              <th align="left">Description</th>
                              <th align="right">Price Each</th>
                              <th align="right">Quantity</th>
                              <th align="right">Extended</th>
                        </tr>
                  <xsl:for-each select="Col[1]/Row">
                        <xsl:variable name="vPosition" select="position()"/>
                        <tr>
                              <xsl:for-each select="/gvaPlannedOrder/Col/Row[position() mod 3 = $vPosition mod 3]">
                                    <td>
                                          <xsl:choose>
                                                <xsl:when test="position() &gt; 4">
                                                      <xsl:attribute name="align">right</xsl:attribute>
                                                      <xsl:value-of select="format-number(number(.), '$###,##0.00')"/>
                                                </xsl:when>
                                                <xsl:otherwise>
                                                      <xsl:value-of select="."/>
                                                </xsl:otherwise>
                                          </xsl:choose>
                                    </td>
                              </xsl:for-each>
                        </tr>
                  </xsl:for-each>
                  </tbody>
            </table>
      </xsl:template>
</xsl:stylesheet>


This is going to be the best performance, because you're doing the rendering in one step.  There *might* be a faster XSLT, but I doubt it.

Regards,
Mike Sharp
Forgot:  

<th align="left">Order Number</th>


should be:

<th align="left">Warehouse Number</th>


Regards,
Mike Sharp
Hi Mike,

I sent you a email with some more details. I hope you recieved it.

After sending that email, I changed my template match and I could load the header row, but I still cannot load any details.

I understood that you are trying to iterate through the total # of rows here, Correct?:
<xsl:for-each select="Col[1]/Row">
                    <xsl:variable name="vPosition" select="position()"/>

But how does this loop work? Actually what is the value returned in the []
<tr>
 <xsl:for-each select="/gvaPlannedOrder/Col/Row[position() mod 3 = $vPosition mod 3]">

Thanks.

Kaushal
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
The details don't render because you added a new root element; <Variables>.  The for-each that processes the columns begins with what you had previously said was the root; just add the new root element to the path.  I took out the number formatting code to make it simpler--you can always add it back in.  This is the result:


<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
     <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
     <xsl:template match="gvaPlannedOrder">
          <table cellpadding="5px" border="1" style="border-collapse:collapse;">
               <tbody>
                    <tr>
                         <th align="left">Type</th>
                         <th align="left">Warehouse</th>
                         <th align="left">Item</th>
                         <th align="left">Part Number</th>
                         <th align="left">Description</th>
                         <th align="left">Price 1</th>
                         <th align="right">Price 2</th>
                         <th align="right">Quantity</th>
                         <th align="left">Units</th>
                         <th align="right">Extended</th>
                         <th align="left"></th>
                         <th align="left"></th>
                         <th align="left"></th>
                         <th align="left"></th>
                         <th align="left"></th>
                         <th align="left"></th>
                    </tr>
               <xsl:for-each select="Col[1]/Row">
                    <xsl:variable name="vPosition" select="position()"/>
                    <tr>
                         <xsl:for-each select="/Variables/gvaPlannedOrder/Col/Row[position() mod 3 = $vPosition mod 3]">
                              <td>
                                            <xsl:value-of select="."/>
                              </td>
                         </xsl:for-each>
                    </tr>
               </xsl:for-each>
               </tbody>
          </table>
     </xsl:template>
</xsl:stylesheet>


Regards,
Mike Sharp
Hi Mike,

Here is the xsl I have, but I don't seem to render details. What am I doing wrong ?

Also I had to point the template match to the root ["/"], only then I can see the header.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
     <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
     <xsl:template match="/">
          <table cellpadding="5px" border="1" style="border-collapse:collapse;">
               <tbody>
                    <tr>
                         <th align="left">Type</th>
                         <th align="left">Warehouse</th>
                         <th align="left">Item</th>
                         <th align="left">Part Number</th>
                         <th align="left">Description</th>
                         <th align="left">Price 1</th>
                         <th align="right">Price 2</th>
                         <th align="right">Quantity</th>
                         <th align="left">Units</th>
                         <th align="right">Extended</th>
                         <th align="left"></th>
                         <th align="left"></th>
                         <th align="left"></th>
                         <th align="left"></th>
                         <th align="left"></th>
                         <th align="left"></th>
                    </tr>
               <xsl:for-each select="Variables/gvaPlannedOrder/Col[1]/Row">
                    <td>Entering loop?<xsl:value-of select="."/> </td>
                    <xsl:variable name="vPosition" select="position()"/>
                    <tr>
                         <xsl:for-each select="Variables/gvaPlannedOrder/Col/Row[position() mod 3 = $vPosition mod 3]">
                              <td>
                                   <xsl:value-of select="."/>
                              </td>
                         </xsl:for-each>
                    </tr>
               </xsl:for-each>
               </tbody>
          </table>
     </xsl:template>
</xsl:stylesheet>


Here is the XML against which I am testing the above XSL:

<Variables><gvaPlannedOrder><Col><Row dataType="string">Order</Row><Row dataType="string">Order</Row><Row dataType="string">Order</Row></Col><Col><Row dataType="string">001</Row><Row dataType="string">001</Row><Row dataType="string">001</Row></Col><Col><Row dataType="string">KVW-IN TRANSIT</Row><Row dataType="string">KVW-IN TRANSIT</Row><Row dataType="string">KVW-IN TRANSIT</Row></Col><Col><Row dataType="string">KWW15</Row><Row dataType="string">2000101</Row><Row dataType="string">KWW15</Row></Col><Col><Row dataType="string">WOOD CASEMENT UNIT </Row><Row dataType="string">INT VERT MULL 39-1/2" </Row><Row dataType="string">WOOD CASEMENT UNIT </Row></Col><Col><Row dataType="string">533</Row><Row dataType="number">8.35</Row><Row dataType="string">533</Row></Col><Col><Row dataType="string">533</Row><Row dataType="string">8.35</Row><Row dataType="string">533</Row></Col><Col><Row dataType="string">2</Row><Row dataType="string">2</Row><Row dataType="string">2</Row></Col><Col><Row dataType="string">EA</Row><Row dataType="string">EA</Row><Row dataType="string">EA</Row></Col><Col><Row dataType="number">1066</Row><Row dataType="number">16.7</Row><Row dataType="number">1066</Row></Col><Col><Row dataType="string">1</Row><Row dataType="string">1</Row><Row dataType="string">1</Row></Col><Col><Row dataType="string"></Row><Row dataType="string">0</Row><Row dataType="string"></Row></Col><Col><Row dataType="string"></Row><Row dataType="string"></Row><Row dataType="string"></Row></Col><Col><Row dataType="string"></Row><Row dataType="string"></Row><Row dataType="string"></Row></Col><Col><Row dataType="string">E50A26DD17F24625891E814333ACCFB3</Row><Row dataType="string"></Row><Row dataType="string">742E8C58-5357-46C0-B72C-540F82DBC11A</Row></Col><Col><Row dataType="number">2</Row><Row dataType="string">0</Row><Row dataType="number">2</Row></Col></gvaPlannedOrder></Variables>
You left out the leading "/" in

 <xsl:for-each select="/Variables/gvaPlannedOrder/Col/Row[position() mod 3 = $vPosition mod 3]">

Regards,
Mike Sharp
Mike,

Still nothing below header is showing up. All I see is the header row. I have the forward leaning slash as the template match value and in front of both the for-each statements' select value, like you have above.

Could it be because of the way I am loading the XSL and XML on the server: ?

      Dim XML
      Dim XSL
      
      'Load XML into a DOM
      set xml = Server.CreateObject("Microsoft.XMLDOM")
      xml.async = false
      xml.Loadxml(Server.MapPath("/" & WEB_NAME & "/PlannedOrder/Cart2.xml"))

      'Load XSL into a DOM
      set xsl = Server.CreateObject("Microsoft.XMLDOM")
      xsl.async = false
      xsl.load (Server.MapPath("/" & WEB_NAME & "/PlannedOrder/PlannedOrder.xsl"))

      'Transform file
      Response.Write (xml.transformNode(xsl))

      Set xml = nothing
      Set xsl = nothing      
      
      Response.End
I tried several things, but I don't know why, anything within the for-loop does not seem to get rendered.

Any other ideas ?
It's possible that the server-side code you're using is the problem.  There are two problems (with a potential third one)

1. You're using an *ancient* version of the parser.  
2.  Also, you're using LoadXML, which loads a string, not a file.  
3.  You're transforming to a string, which sooner or later will come back and bite you with an encoding problem.

So Change the ASP code to be (I've made several changes here!):

 Dim XML
     Dim XSL
     
     'Load XML into a DOM
     set xml = Server.CreateObject("Msxml2.DomDocument")
     xml.async = false
     xml.Load(Server.MapPath("/" & WEB_NAME & "/PlannedOrder/Cart2.xml"))

     'Load XSL into a DOM
     set xsl = Server.CreateObject("Msxml2.DomDocument")
     xsl.async = false
     xsl.load (Server.MapPath("/" & WEB_NAME & "/PlannedOrder/PlannedOrder.xsl"))

     'Transform file directly to Response Stream
     xml.transformNodeToObject(xsl, Response)

     Set xml = nothing
     Set xsl = nothing    
     Response.End

Regards,
Mike Sharp
Hello Mike,

Thanks for all your help. I appreciate your patience with me. I should have caught that LoadXML vs. Load. I am able to now render the page and will start working on formatting the HTML.

FYI, It takes about 3.8 seconds to load the xml/xsl and display the html (for a gvaPlannedOrder with 16 Cols and 45 Rows). I feel that if the data was Row based, this traversing would take much lesser time. But 3.8 is much better than what we had initially.

Thanks again Mike,
Kaushal Vyas
PS: And please do let me know if you know about any good XML/XSL tutiorals. I have already finished some of the w3schools tutioral.
You might want to download Roger Costello's XSLT tutorial.  I haven't gone into it in great detail, but his Schema tutorial is quite good and very detailed.  You can find it at:

http://xfront.com

Also, if you're still using the old parser, you'll get much faster results with MSXML 4.  3.8 seconds sounds like a LONG time to me.   Is the XML very large?  You use MSXML 4 with:

    set xml = Server.CreateObject("Msxml2.DomDocument.4.0")
 
You can also gain a little time by pre-compiling and caching the XSLT, though in your case it's not a particularly complex XSLT, and the savings would be in the tens of milliseconds range.  See:

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

Regards,
Mike Sharp

I had a question related to keeping a counting value:

Is there a way we can count the "Extprice" (in the above xsl) of each line item into a variable within the same loop we loop for the number of rows ? I want to have the total order value(sum of all Ext. prices for each row) and show it at the end of the loop.

No, variables are immutable.  You can do this recursively using called templates and instantiating new variables for each call, but it's not necessary.  Just create the subtotal all at the same time, using the XPath sum() function.

Regards,
Mike Sharp