| Author |
Topic |
|
Feline
Starting Member
5 Posts |
 Posted - 2002-10-17 : 06:44:37
|
| right I have quesion about which you think is best and which you think is fastest.I have a complex XML query to produce an invoice the XML will be stored in the database to archive the invoice however I have a question. should I use a set of sp's to return the parts of the XML as can't get the following format any other way ( or have I mist something)<hoilday> <customer name="fred" number="123"/> <person id="1"> <bed id="1"/> <transport id="1"/> </person> <person id="2"> <bed id="2"/> <transport id="2"/> </person></hoilday>V simplfid but still simlaror Xpath with templates the templates way looks easer to maintain but I have to run 2 query one to get and one put back as I can't see how to do the multi step i.e. retrive and insert in one go wit the template. Also if accessing for ASP.NET would it not be quicker to go with a datareader rather than the http access methods.Thanks in advanceALF |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-10-17 : 08:03:45
|
| I don't think its a good idea to store a database structure in a database ... you should break the invoice xml into a table(s). Then create the XML if you need to send it to a template for viewing or whatever.I have used a technique where a vendor of ours sends us invoices in XML format which I then import into our financial system using XML in SQL Server 2000 ... works very nice as it does not limit the amount of data or number of batches i can do per import ... I've also gone the other way, taking data from the database and producing an XML document that is then transformed for viewing by the client...Maybe you would want to reconsider your design, as storing XML makes it very hard to work with in a stored procedure (assuming you are using a text/ntext field since varchar(8000) is usually not enough) |
 |
|
|
Feline
Starting Member
5 Posts |
Posted - 2002-10-17 : 08:18:54
|
| I think I've not explaind my slef that well. I'm storeing the data in normal database format but when I print an invoice I need a copy of the invoice for archive perposes hence I never need to do a query on the archived data excted the print date and issue number hence you have a table like:XMLInvoice ntextDatePrited datetimeissue intthe main problem is which tecnic I use retrive the xml and then archive it. xpath query or a set of sp's to produce the XML?ALF |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-17 : 08:31:28
|
Are you using SQL Server 2000? It has built-in XML capabilities and can render an XML stream in pretty much any format you need. Books Online has details under "FOR XML".quote:
I'm storeing the data in normal database format but when I print an invoice I need a copy of the invoice for archive perposes hence I never need to do a query on the archived data excted the print date and issue number hence you have a table like
I think you missed Onamuji's point somewha. DON'T store the XML of the invoice in the database. Since you already have the data for it in the tables, just log the printing records, you'll be able to reconstruct an invoice at any time.Edited by - robvolk on 10/17/2002 08:34:12 |
|
|
|
Feline
Starting Member
5 Posts |
Posted - 2002-10-17 : 13:21:10
|
| I can't as the data can chance on me I could put the invoice in to sperate tables but I'd just end up copying the hole database.I'll give you an example.1) you have a hoilday with 4 people on sevral forms of accomdation and sevral forms of transport.2) print and archive (you must be able to retrive this invoice by date or issue nothing else)3) Anther person decides to go on the holiday so you print another invoice and archive.The data for the first invioce has changed hence you can not derive the invoice again. I could copy all the data into archive tables but as I say I need about 90% of the database to do this hence you almost double the size of you're database. In order to produce the invoice you use xml output with a template XSL template difrent one per installation. Hence all you have to do to see an invoice, even after I've archived the holiday info from the database to summary form, is run the xml with the template. I need the invioce for legal reason but not all the data. Hence I just store the XML that was produce for that instance of the invioce as it's smaller than the invoice it's self.Yes I'm useing SQL 2000 sorry whould have said that at the start I've used FOR XML and EXPLICIT before but I'm unable to get the XML in the froamt I want. I can't seam to nest 2 eliments under one eliment.e.g.<Customer> <Payments/> <people> <accommodation/> <transport/> </People></customer>the sql would some thing like this--for payment elimentselect p.* from customer c INNER JOIN payment p on (p.customerId = c.customerId)WHERE customerid = 1-- for beds elimentselect b.* from customer c INNER JOIN people p INNER JOIN beds b ON (p.personId = b.personId) on (p.customerId = c.customerId)WHERE customerid = 1you get the gist I think. once I have all the eliments return useinf for xml I can put them together and return a lonf string as the xml for the invoice. This is where I'm thinking i've mist a trick can I do the query in one with explicit. I've tryed but when I nest to join for one parent it produces an error.The other optoin would be to use xpath with templates as you can use sevral sql querys to produce the document.Sorry about grammer and spelling it's not my fortay.thanks for the help peopleALF |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-10-17 : 14:03:58
|
| you shouldn't put the archived VERSION in an XML format ... you already have the TABLE structures ... you might as well just add a column to the INVOICE table called VERSION and update that as needed ... and then for the CURRENT version you would just select the one where the VERSION = MAX(VERSION) FOR THIS INVOICE ... if you didn't notice I'm trying to STRONGLY push you away from storing the invoice version as XML ... it may seem simple but the other approach will save you headaches... and ya it will make your table bigger but make sure you change the indexing ... I would suggest an index like (InvoiceNumber, Version) ... that help? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-17 : 14:36:22
|
| ...or, in the case of updated or modified invoices (more on this in a sec), use the date as a version or other indication of the original invoice and its contents. I have to admit that I've never seen systems that required an ENTIRE invoice snapshot taken for archival purposes, especially when the data that makes up the invoice is available. Secondly, in your example, it makes no sense to me to have to modify the original invoice to add another person. By all rights, and from what I've seen of such systems, that extra person gets their OWN invoice. At most, the only tie to the original invoice is the addition of a parent invoice number, which I have seen and which makes far more sense than keeping multiple copies of the same invoice, when only 10% of the data varies from one version to another...consider the waste of storing 10 entire copies of a heavily modified invoice when literally only 1 character on each copy is different from the other. Legal requirements or not, I KNOW there are systems that don't do it this way and work perfectly well.Lastly, the fact that you're having difficulty doing this is, to me at least, a strong hint that it is NOT the right way to go. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-10-17 : 17:32:55
|
| I did our catalog system here like that (kept a previous id of the original item), keep a history of every little change ... |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-10-17 : 18:06:39
|
| Another solution is to take your final "printed" output, and save that out to a file somplace (PDF? TIFF?), and put the path to that file in the database. That may even be smaller than the XML, and thus take up less HDD space per invoice. If you are storing all this data in the database, it's going to make your full backups take longer. If you put the data into files outside of SQL Server, you can back them up independantly of SQL Server. This would probably save you time and tape backup space.Just my $0.02.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
should I use xpath or sevral sp's to produce XML