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">Addition al Description AXYZ</Row>
<Row dataType="string">Addition al Description BXYZ</Row>
<Row dataType="string">Addition al Description CXYZ</Row>
</Col>
- <Col> (these are the Prices)
<Row dataType="string">533</Row >
<Row dataType="number">8.35</Ro w>
<Row dataType="string">5000</Ro w>
</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</Ro w>
<Row dataType="number">33.4</Ro w>
<Row dataType="number">5000</Ro w>
</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">Addition al Description AXYZ</Col>
<Col dataType="string">533</Col >
<Col dataType="string">2</Col>
<Col dataType="number">1066</Co l>
</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
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">Addition
<Row dataType="string">Addition
<Row dataType="string">Addition
</Col>
- <Col> (these are the Prices)
<Row dataType="string">533</Row
<Row dataType="number">8.35</Ro
<Row dataType="string">5000</Ro
</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</Ro
<Row dataType="number">33.4</Ro
<Row dataType="number">5000</Ro
</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">Addition
<Col dataType="string">533</Col
<Col dataType="string">2</Col>
<Col dataType="number">1066</Co
</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
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/C ol/Row[pos ition() mod 3 = $vPosition mod 3]">
<Col dataType="{@dataType}"><xs l: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">Addition al Description AXYZ</Col>
<Col dataType="string">533</Col >
<Col dataType="string">2</Col>
<Col dataType="number">1066</Co l>
</Row>
<Row>
<Col dataType="string">001</Col >
<Col dataType="string">Part Description B</Col>
<Col dataType="string">Part Number BXYZ</Col>
<Col dataType="string">Addition al Description BXYZ</Col>
<Col dataType="number">8.35</Co l>
<Col dataType="string">4</Col>
<Col dataType="number">33.4</Co l>
</Row>
<Row>
<Col dataType="string">001</Col >
<Col dataType="string">Part Description C</Col>
<Col dataType="string">Part Number CXYZ</Col>
<Col dataType="string">Addition al Description CXYZ</Col>
<Col dataType="string">5000</Co l>
<Col dataType="string">1</Col>
<Col dataType="number">5000</Co l>
</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
<?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/C
<Col dataType="{@dataType}"><xs
</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">Addition
<Col dataType="string">533</Col
<Col dataType="string">2</Col>
<Col dataType="number">1066</Co
</Row>
<Row>
<Col dataType="string">001</Col
<Col dataType="string">Part Description B</Col>
<Col dataType="string">Part Number BXYZ</Col>
<Col dataType="string">Addition
<Col dataType="number">8.35</Co
<Col dataType="string">4</Col>
<Col dataType="number">33.4</Co
</Row>
<Row>
<Col dataType="string">001</Col
<Col dataType="string">Part Description C</Col>
<Col dataType="string">Part Number CXYZ</Col>
<Col dataType="string">Addition
<Col dataType="string">5000</Co
<Col dataType="string">1</Col>
<Col dataType="number">5000</Co
</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
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
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:col lapse;">
<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/C ol/Row[pos ition() mod 3 = $vPosition mod 3]">
<td>
<xsl:if test="position() > 4">
<xsl:attribute name="align">right</xsl:at tribute>
<xsl:value-of select="format-number(numb er(.), '$###,##0.00')"/>
</xsl:if>
<xsl:attribute name="align">right</xsl:at tribute>
<xsl:if test="position() < 5 and ($vPosition = 1 or position() > 1)">
<xsl:value-of select="."/>
</xsl:if>
</td>
</xsl:for-each>
</tr>
</xsl:for-each>
</tbody>
</table>
</xsl:template>
</xsl:stylesheet>
Regards,
Mike Sharp
<?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:col
<tbody>
<tr>
<th align="left">Order Number</th>
<th align="left">Item</th>
<th align="left">Part Number</th>
<th align="left">Description</
<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/C
<td>
<xsl:if test="position() > 4">
<xsl:attribute name="align">right</xsl:at
<xsl:value-of select="format-number(numb
</xsl:if>
<xsl:attribute name="align">right</xsl:at
<xsl:if test="position() < 5 and ($vPosition = 1 or position() > 1)">
<xsl:value-of select="."/>
</xsl:if>
</td>
</xsl:for-each>
</tr>
</xsl:for-each>
</tbody>
</table>
</xsl:template>
</xsl:stylesheet>
Regards,
Mike Sharp
ASKER
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
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:col lapse;">
<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/C ol/Row[pos ition() mod 3 = $vPosition mod 3]">
<td>
<xsl:choose>
<xsl:when test="position() > 4">
<xsl:attribute name="align">right</xsl:at tribute>
<xsl:value-of select="format-number(numb er(.), '$###,##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
<?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:col
<tbody>
<tr>
<th align="left">Order Number</th>
<th align="left">Item</th>
<th align="left">Part Number</th>
<th align="left">Description</
<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/C
<td>
<xsl:choose>
<xsl:when test="position() > 4">
<xsl:attribute name="align">right</xsl:at
<xsl:value-of select="format-number(numb
</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
<th align="left">Order Number</th>
should be:
<th align="left">Warehouse Number</th>
Regards,
Mike Sharp
ASKER
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/C ol/Row[pos ition() mod 3 = $vPosition mod 3]">
Thanks.
Kaushal
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/C
Thanks.
Kaushal
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:col lapse;">
<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/gvaPlan nedOrder/C ol/Row[pos ition() 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
<?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:col
<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 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/gvaPlan
<td>
<xsl:value-of select="."/>
</td>
</xsl:for-each>
</tr>
</xsl:for-each>
</tbody>
</table>
</xsl:template>
</xsl:stylesheet>
Regards,
Mike Sharp
ASKER
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:col lapse;">
<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/gvaPlann edOrder/Co l[1]/Row">
<td>Entering loop?<xsl:value-of select="."/> </td>
<xsl:variable name="vPosition" select="position()"/>
<tr>
<xsl:for-each select="Variables/gvaPlann edOrder/Co l/Row[posi tion() 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><gvaPlannedOrde r><Col><Ro w dataType="string">Order</R ow><Row dataType="string">Order</R ow><Row dataType="string">Order</R ow></Col>< Col><Row dataType="string">001</Row ><Row dataType="string">001</Row ><Row dataType="string">001</Row ></Col><Co l><Row dataType="string">KVW-IN TRANSIT</Row><Row dataType="string">KVW-IN TRANSIT</Row><Row dataType="string">KVW-IN TRANSIT</Row></Col><Col><R ow dataType="string">KWW15</R ow><Row dataType="string">2000101< /Row><Row dataType="string">KWW15</R ow></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</Ro w><Row dataType="string">533</Row ></Col><Co l><Row dataType="string">533</Row ><Row dataType="string">8.35</Ro w><Row dataType="string">533</Row ></Col><Co l><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</Ro w><Row dataType="number">16.7</Ro w><Row dataType="number">1066</Ro w></Col><C ol><Row dataType="string">1</Row>< Row dataType="string">1</Row>< Row dataType="string">1</Row>< /Col><Col> <Row dataType="string"></Row><R ow dataType="string">0</Row>< Row dataType="string"></Row></ Col><Col>< Row dataType="string"></Row><R ow dataType="string"></Row><R ow dataType="string"></Row></ Col><Col>< Row dataType="string"></Row><R ow dataType="string"></Row><R ow dataType="string"></Row></ Col><Col>< Row dataType="string">E50A26DD 17F2462589 1E814333AC CFB3</Row> <Row dataType="string"></Row><R ow dataType="string">742E8C58 -5357-46C0 -B72C-540F 82DBC11A</ Row></Col> <Col><Row dataType="number">2</Row>< Row dataType="string">0</Row>< Row dataType="number">2</Row>< /Col></gva PlannedOrd er></Varia bles>
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:col
<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 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/gvaPlann
<td>Entering loop?<xsl:value-of select="."/> </td>
<xsl:variable name="vPosition" select="position()"/>
<tr>
<xsl:for-each select="Variables/gvaPlann
<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><gvaPlannedOrde
You left out the leading "/" in
<xsl:for-each select="/Variables/gvaPlan nedOrder/C ol/Row[pos ition() mod 3 = $vPosition mod 3]">
Regards,
Mike Sharp
<xsl:for-each select="/Variables/gvaPlan
Regards,
Mike Sharp
ASKER
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("Micro soft.XMLDO M")
xml.async = false
xml.Loadxml(Server.MapPath ("/" & WEB_NAME & "/PlannedOrder/Cart2.xml") )
'Load XSL into a DOM
set xsl = Server.CreateObject("Micro soft.XMLDO M")
xsl.async = false
xsl.load (Server.MapPath("/" & WEB_NAME & "/PlannedOrder/PlannedOrde r.xsl"))
'Transform file
Response.Write (xml.transformNode(xsl))
Set xml = nothing
Set xsl = nothing
Response.End
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("Micro
xml.async = false
xml.Loadxml(Server.MapPath
'Load XSL into a DOM
set xsl = Server.CreateObject("Micro
xsl.async = false
xsl.load (Server.MapPath("/" & WEB_NAME & "/PlannedOrder/PlannedOrde
'Transform file
Response.Write (xml.transformNode(xsl))
Set xml = nothing
Set xsl = nothing
Response.End
ASKER
I tried several things, but I don't know why, anything within the for-loop does not seem to get rendered.
Any other ideas ?
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("Msxml 2.DomDocum ent")
xml.async = false
xml.Load(Server.MapPath("/ " & WEB_NAME & "/PlannedOrder/Cart2.xml") )
'Load XSL into a DOM
set xsl = Server.CreateObject("Msxml 2.DomDocum ent")
xsl.async = false
xsl.load (Server.MapPath("/" & WEB_NAME & "/PlannedOrder/PlannedOrde r.xsl"))
'Transform file directly to Response Stream
xml.transformNodeToObject( xsl, Response)
Set xml = nothing
Set xsl = nothing
Response.End
Regards,
Mike Sharp
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("Msxml
xml.async = false
xml.Load(Server.MapPath("/
'Load XSL into a DOM
set xsl = Server.CreateObject("Msxml
xsl.async = false
xsl.load (Server.MapPath("/" & WEB_NAME & "/PlannedOrder/PlannedOrde
'Transform file directly to Response Stream
xml.transformNodeToObject(
Set xml = nothing
Set xsl = nothing
Response.End
Regards,
Mike Sharp
ASKER
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.
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("Msxml 2.DomDocum ent.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
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("Msxml
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
ASKER
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.
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
Regards,
Mike Sharp
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