ross_petersen Posted March 25, 2007 Share Posted March 25, 2007 Hi folksFirst of all, I think that this site is so brilliant!!!I have a sample Access 2003 DB that has a query that I have outputted as xml.The result of the query is as follows: Purch Order ID Purch Order Date SupplierName Supplier Debtor Ref Purch Order Det ID Prod Name Supplier Ref Qty Unit Name Unit Price1 17/03/2007 Southern Veterinarian Supplies countryvets001 1 injection needles 10 pack injneedl10pk 3 each $20.002 18/03/2007 Hobart Office Supplies countryv01 2 biros 5 pack biros5pk 2 each $3.502 18/03/2007 Hobart Office Supplies countryv01 3 photocopy paper A4 PCPA4 3 reams $5.00the xml is as folllows:<?xml version="1.0" encoding="UTF-8" ?> - <dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="qryPurchOrdersSuppliersAndDetails.xsd" generated="2007-03-26T05:50:52">- <qryPurchOrdersSuppliersAndDetails> <PurchOrderID>1</PurchOrderID> <PurchOrderDate>2007-03-17T00:00:00</PurchOrderDate> <SupplierName>Southern Veterinarian Supplies</SupplierName> <SupplierDebtorRef>countryvets001</SupplierDebtorRef> <PurchOrderDetailID>1</PurchOrderDetailID> <ProductName>injection needles 10 pack</ProductName> <SupplierRef>injneedl10pk</SupplierRef> <Qty>3</Qty> <UnitName>each</UnitName> <UnitPrice>20</UnitPrice> </qryPurchOrdersSuppliersAndDetails>- <qryPurchOrdersSuppliersAndDetails> <PurchOrderID>2</PurchOrderID> <PurchOrderDate>2007-03-18T00:00:00</PurchOrderDate> <SupplierName>Hobart Office Supplies</SupplierName> <SupplierDebtorRef>countryv01</SupplierDebtorRef> <PurchOrderDetailID>2</PurchOrderDetailID> <ProductName>biros 5 pack</ProductName> <SupplierRef>biros5pk</SupplierRef> <Qty>2</Qty> <UnitName>each</UnitName> <UnitPrice>3.5</UnitPrice> </qryPurchOrdersSuppliersAndDetails>- <qryPurchOrdersSuppliersAndDetails> <PurchOrderID>2</PurchOrderID> <PurchOrderDate>2007-03-18T00:00:00</PurchOrderDate> <SupplierName>Hobart Office Supplies</SupplierName> <SupplierDebtorRef>countryv01</SupplierDebtorRef> <PurchOrderDetailID>3</PurchOrderDetailID> <ProductName>photocopy paper A4</ProductName> <SupplierRef>PCPA4</SupplierRef> <Qty>3</Qty> <UnitName>reams</UnitName> <UnitPrice>5</UnitPrice> </qryPurchOrdersSuppliersAndDetails> </dataroot>I have been trying to arrange the xml with xslt so it would appear as a series of conventional Purchase Orders (i.e. the data related to the one side of the db (PurchOrderID, PurchOrderDate, SupplierName, SupplierDebtorRef) at the top of the screen) and the other data related to the many side in a table with a column for each field.The xsl file is as follows (note that I have not incl some helper functions):<?xml version="1.0"?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:fx="#fx-functions" exclude-result-prefixes="msxsl fx"> <xsl:output method="html" version="4.0" indent="yes" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/> <xsl:template match="//dataroot" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <html> <head> <META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=UTF-8"/> <title>qryPurchOrdersSuppliersAndDetails</title> <style type="text/css"></style> <h1 style="TEXT-ALIGN: center; WIDTH: 20.0cm">COUNTRY VETS</h1> <h1 style="TEXT-ALIGN: center; WIDTH: 20.0cm">PURCHASE ORDER</h1> </head> <body link="#0000ff" vlink="#800080"> <table border="0" bgcolor="#ffffff" cellspacing="10" cellpadding="3" id="CTRL1"> <colgroup> <col style="TEXT-ALIGN: right; WIDTH: 2.989cm"/> <col style="TEXT-ALIGN: center; WIDTH: 2.989cm"/> </colgroup> <tbody id="CTRL2"> <xsl:for-each select="qryPurchOrdersSuppliersAndDetails"> <tr> <td> <div align="center"> <strong>Purch Order ID:</strong> </div> </td> <td> <xsl:value-of select="PurchOrderID"/> </td> </tr> <p></p> <table border="1" bgcolor="#ffffff" cellspacing="0" cellpadding="0" id="CTRL3"> <colgroup> <col style="TEXT-ALIGN: right; WIDTH: 2.38cm"/> <col style="TEXT-ALIGN: right; WIDTH: 2.989cm"/> <col style="WIDTH: 5.767cm"/> <col style="WIDTH: 3.465cm"/> <col style="TEXT-ALIGN: right; WIDTH: 2.38cm"/> <col style="WIDTH: 2.38cm"/> <col style="WIDTH: 2.38cm"/> <col style="TEXT-ALIGN: right; WIDTH: 2.38cm"/> <col style="WIDTH: 7.037cm"/> <col style="TEXT-ALIGN: right; WIDTH: 2.38cm"/> </colgroup> <tbody id="CTRL4"> <tr> <td> <div align="center"> <strong>Purch Order ID</strong> </div> </td> <td> <div align="center"> <strong>Purch Order Date</strong> </div> </td> <td> <div align="center"> <strong>SupplierName</strong> </div> </td> <td> <div align="center"> <strong>Supplier Debtor Ref</strong> </div> </td> <td> <div align="center"> <strong>Purch Order Det ID</strong> </div> </td> <td> <div align="center"> <strong>Prod Name</strong> </div> </td> <td> <div align="center"> <strong>Supplier Ref</strong> </div> </td> <td> <div align="center"> <strong>Qty</strong> </div> </td> <td> <div align="center"> <strong>Unit Name</strong> </div> </td> <td> <div align="center"> <strong>Unit Price</strong> </div> </td> </tr> </tbody> <tbody id="CTRL5"> <!-- <xsl:for-each select="qryPurchOrdersSuppliersAndDetails"> --> <!-- Cache the current node incase the a field is formatted --> <xsl:value-of select="fx:CacheCurrentNode(.)"/> <tr> <td> <xsl:value-of select="PurchOrderID"/> </td> <td> <xsl:value-of select="fx:FormatFromXSL('PurchOrderDate', '', '', '', 7)"/> </td> <td> <xsl:value-of select="SupplierName"/> </td> <td> <xsl:value-of select="SupplierDebtorRef"/> </td> <td> <xsl:value-of select="PurchOrderDetailID"/> </td> <td> <xsl:value-of select="ProductName"/> </td> <td> <xsl:value-of select="SupplierRef"/> </td> <td> <xsl:value-of select="Qty"/> </td> <td> <xsl:value-of select="UnitName"/> </td> <td> <xsl:value-of select="fx:FormatFromXSL('UnitPrice', 'Currency', 'auto', '3081', 6)"/> </td> </tr> <!-- </xsl:for-each> --> </tbody> </table> </xsl:for-each> </tbody> </table> </body> </html> </xsl:template></xsl:stylesheet>The result of applying this xsl file is as follows (note that I added a line referencing the xsl file):COUNTRY VETSPURCHASE ORDERPurch Order ID: 1 Purch Order ID Purch Order Date SupplierName Supplier Debtor Ref Purch Order Det ID Prod Name Supplier Ref Qty Unit Name Unit Price 1 17/03/2007 Southern Veterinarian Supplies countryvets001 1 injection needles 10 pack injneedl10pk 3 each $20.00 Purch Order ID:2 Purch Order ID Purch Order Date SupplierName Supplier Debtor Ref Purch Order Det ID Prod Name Supplier Ref Qty Unit Name Unit Price 2 18/03/2007 Hobart Office Supplies countryv01 2 biros 5 pack biros5pk 2 each $3.50 Purch Order ID:2 Purch Order ID Purch Order Date SupplierName Supplier Debtor Ref Purch Order Det ID Prod Name Supplier Ref Qty Unit Name Unit Price 2 18/03/2007 Hobart Office Supplies countryv01 3 photocopy paper A4 PCPA4 3 reams $5.00I dont know if it is readable or not, but basically where there is more than one line in the query with the same PurchOrderID that part gets repeated and them I have the table part below that related to the particular row from the qry or xml file.What I am trying to achieve is simply the one side is just specified once and the corresp many part(s) is just under it.Hope that makes sense and many thanks in advanceRegardsRoss Link to comment Share on other sites More sharing options...
boen_robot Posted March 25, 2007 Share Posted March 25, 2007 Next time, copy the source of the XML, not what IE sees. I mean, right now, the "-" signs are making duplicating the issue a bit harder (though still possible).The XSLT is also truly very unreable, scince there are many presentational attributes in the XHTML output part. What are those fx functions anyway? Link to comment Share on other sites More sharing options...
ross_petersen Posted March 25, 2007 Author Share Posted March 25, 2007 Next time, copy the source of the XML, not what IE sees. I mean, right now, the "-" signs are making duplicating the issue a bit harder (though still possible).Fair enough - will do in future.The XSLT is also truly very unreable, scince there are many presentational attributes in the XHTML output part. What are those fx functions anyway? Well, there are a bucketload of dynamically generated functions.eg Function Format(varValue, strFormat, iNumDecimals, LCID, nType)This function just formats a given value according to whether it is a date or a number or string etc.Function Avg(strExpr) - this fn jsut returns an average Link to comment Share on other sites More sharing options...
boen_robot Posted March 25, 2007 Share Posted March 25, 2007 O...k... so.. tell me again what exactly are you trying to achieve? A table I'm guessing, but with what on each column/row? Should there be more then one table? I would write a new one for you from scratch, as this one seems useless. Link to comment Share on other sites More sharing options...
ross_petersen Posted March 25, 2007 Author Share Posted March 25, 2007 O...k... so.. tell me again what exactly are you trying to achieve? A table I'm guessing, but with what on each column/row? Should there be more then one table? I would write a new one for you from scratch, as this one seems useless.Thanks boen_robotHere is the xml file (from Notepad not IE)<?xml version="1.0" encoding="UTF-8"?><?xml-stylesheet type="text/xsl" href="PurchOrdersSuppliersAndDetailsModified.xsl"?><dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="qryPurchOrdersSuppliersAndDetails.xsd" generated="2007-03-26T05:50:52"><qryPurchOrdersSuppliersAndDetails><PurchOrderID>1</PurchOrderID><PurchOrderDate>2007-03-17T00:00:00</PurchOrderDate><SupplierName>Southern Veterinarian Supplies</SupplierName><SupplierDebtorRef>countryvets001</SupplierDebtorRef><PurchOrderDetailID>1</PurchOrderDetailID><ProductName>injection needles 10 pack</ProductName><SupplierRef>injneedl10pk</SupplierRef><Qty>3</Qty><UnitName>each</UnitName><UnitPrice>20</UnitPrice></qryPurchOrdersSuppliersAndDetails><qryPurchOrdersSuppliersAndDetails><PurchOrderID>2</PurchOrderID><PurchOrderDate>2007-03-18T00:00:00</PurchOrderDate><SupplierName>Hobart Office Supplies</SupplierName><SupplierDebtorRef>countryv01</SupplierDebtorRef><PurchOrderDetailID>2</PurchOrderDetailID><ProductName>biros 5 pack</ProductName><SupplierRef>biros5pk</SupplierRef><Qty>2</Qty><UnitName>each</UnitName><UnitPrice>3.5</UnitPrice></qryPurchOrdersSuppliersAndDetails><qryPurchOrdersSuppliersAndDetails><PurchOrderID>2</PurchOrderID><PurchOrderDate>2007-03-18T00:00:00</PurchOrderDate><SupplierName>Hobart Office Supplies</SupplierName><SupplierDebtorRef>countryv01</SupplierDebtorRef><PurchOrderDetailID>3</PurchOrderDetailID><ProductName>photocopy paper A4</ProductName><SupplierRef>PCPA4</SupplierRef><Qty>3</Qty><UnitName>reams</UnitName><UnitPrice>5</UnitPrice></qryPurchOrdersSuppliersAndDetails></dataroot>Now for example, taking PurchOrderID = 2 there are two records. <qryPurchOrdersSuppliersAndDetails><PurchOrderID>2</PurchOrderID><PurchOrderDate>2007-03-18T00:00:00</PurchOrderDate><SupplierName>Hobart Office Supplies</SupplierName><SupplierDebtorRef>countryv01</SupplierDebtorRef><PurchOrderDetailID>2</PurchOrderDetailID><ProductName>biros 5 pack</ProductName><SupplierRef>biros5pk</SupplierRef><Qty>2</Qty><UnitName>each</UnitName><UnitPrice>3.5</UnitPrice></qryPurchOrdersSuppliersAndDetails><qryPurchOrdersSuppliersAndDetails><PurchOrderID>2</PurchOrderID><PurchOrderDate>2007-03-18T00:00:00</PurchOrderDate><SupplierName>Hobart Office Supplies</SupplierName><SupplierDebtorRef>countryv01</SupplierDebtorRef><PurchOrderDetailID>3</PurchOrderDetailID><ProductName>photocopy paper A4</ProductName><SupplierRef>PCPA4</SupplierRef><Qty>3</Qty><UnitName>reams</UnitName><UnitPrice>5</UnitPrice></qryPurchOrdersSuppliersAndDetails> Now what I am hoping to achieve is that each page should have two sections.The first section (at the top of the page) should display the following data & corresp labels but NOT IN a table format but just distributed vertically (with the corresp label beside the data)<PurchOrderID>2</PurchOrderID><PurchOrderDate>2007-03-18T00:00:00</PurchOrderDate><SupplierName>Hobart Office Supplies</SupplierName><SupplierDebtorRef>countryv01</SupplierDebtorRef>The second section (just below the first section) should display the following data & corresp labels IN a table format (column for each field and row for each record)<PurchOrderDetailID>2</PurchOrderDetailID><ProductName>biros 5 pack</ProductName><SupplierRef>biros5pk</SupplierRef><Qty>2</Qty><UnitName>each</UnitName><UnitPrice>3.5</UnitPrice><PurchOrderDetailID>3</PurchOrderDetailID><ProductName>photocopy paper A4</ProductName><SupplierRef>PCPA4</SupplierRef><Qty>3</Qty><UnitName>reams</UnitName><UnitPrice>5</UnitPrice>I hope this makes sense. Basically, the first section comes from the one side of the query in the DB, while the second section comes from the many side of the query in the DB.Many thanksBy the way, if you have any questions I will be on our roof doing repairs for a couple of hours till I have a break).Kind regardsRoss Link to comment Share on other sites More sharing options...
boen_robot Posted April 12, 2007 Share Posted April 12, 2007 Well, I tryed for some time to make something, but I failed. But even if I was successfull, the XSLT file wouldn't have been flexible enough to handle changes to the DB structure. If you rename a certain column, the names of most elements will also change, adding a new one makes matters worse, etc.Besides xsl:key, there are 4 grouping methods demonstrated at xmlpitstop.com which could be used in XSLT 1.0. If you have XSLT 2.0 processor, you could also use the new goruping methods available there, but I haven't got the time to study either of them yet. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now