Jump to content
Can XML, XSLT and XSD do the following:
Asked by Boubou
Posted Jan 06 2010 08:52 AM
Context : We must send data extracted from a database so that it may updated. Once updated the data is sent back so that we can update the database. One of the requirements (constraints) is that Excel be used for the data update.
Problem : Currently the solution is to generate an Excel file from the data extract and another Excel file that is used as a "template". The generated Excel file is the one that that will be sent out. Although this works this process takes approximately 7 ~ 8 minutes for each data extract and is extremely resource intensive which is unacceptable. The current solution also requires that Excel be present on the processing server.
Question : The solution I would like to propose is to extract the data into an XML file use XSL file for the transformation and an XSD file to hold the XML schema (Can I use an in-line schema instead? I understand that the xml schema is mandatory if I want to use Excel to open the XML file). The transformation in this case would replace the need for an Excel "template" and would be responsible for formatting the document and creating the controls (textboxes, dropdowns, checkboxes etc...). Once the XML document is updated via Excel, the updates are to be available in the "saved" xml document which is the only thing sent back for the db update.
Is this feasible or I'm I barking up the wrong tree? (I read that Office 2003 recognizes XML as a native document format and therefore what I exposed above should work)
My XML background is somewhat limited and I've never used XSLT but my research into the matter points to XML/XSLT/XSD as the perfect solution to my problem. I understand that the challenge will be in developing the XSL templates needed and that this may be long and complicated but I'm willing to give it a go...
Answered by simonstl
Posted Jan 08 2010 12:15 PM
I'm not completely clear on what the capabilities of your database extraction system are, or how complex your data is.
If the data is strictly a table, then exporting as XML (with an XML Schema) can work well. You'll have a spreadsheet that can import the XML and save it out again. I don't think you'll even likely need XSLT for that.
However, if it's more complicated than a single table, you may need to develop a more complicated solution, one that creates the spreadsheet-specific XML that Excel has understood since Excel 2003 and used as a default format since Excel 2007. Users will need to work in that spreadsheet, avoid straying out of the areas where they're allowed to make changes, and then hand their results over to another process (likely XSLT) that converts that spreadsheet XML into something your database can understand.
There should be enough information in Office 2003 XML to get you started with this, but it's admittedly a much more complicated project than it probably should be, hard to explain thoroughly in less than fifty pages!
Learn more about this topic from Office 2003 XML.
This book explores the relationship between XML and Office 2003, examining how the various products in the Office suite both produce and consume XML. Beginning with an overview of the XML features included in the various Office 2003 components, Office 2003 XML provides quick and clear guidance to anyone who needs to import or export information from Office documents into other systems.