|
KarstenK wrote: This is a time bomb. On any day or any reason this "database" will crash..
Yup. I made that prediction within a month of being here. People look at me like I'm one of those homeless people holding up a "the world will end tomorrow" sign.
|
|
|
|
|
What you should do is set up a one-time agent that exports the data into a new table that contains a reasonable schema so you can illustrate the folly of their ways. It shouldn't take you more than an hour or so to do.
".45 ACP - because shooting twice is just silly" - JSOP, 2010 ----- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010 ----- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Never mind - I just saw that the data keeps changing. In that case, saving the data as a comma-delimited blob would be better, because then you could include the header row, and thus you can be reasonably confident that you can correctly parse it out of the blob.
If you need it, I have a CSV parser that parses the data into a DataTable without intervention from the programmer. Just aim a stream at it and let it eat (and it even determines appropriate data types, unlike Microslop's importer).
Go here for an explanation of the class and to get the download - it's part of a larger application, but it should be a simple matter to extract what you need.
SQLXAgent - Jobs for SQL Express - Part 3 of 6[^]
".45 ACP - because shooting twice is just silly" - JSOP, 2010 ----- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010 ----- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
modified 5-Dec-17 14:24pm.
|
|
|
|
|
John Simmons / outlaw programmer wrote: saving the data as a comma-delimited blob
At 323K per row, chances are HIGH that the XML data contains tons of commas that are structurally irrelevant. The worst of all nightmares.
It Is The Absolute Verifiable Truth & Proven Fact
That Your Belly-Button Signature Ties
To Viviparous Mama.
|
|
|
|
|
But the blob would still be much smaller because of the absence of XML element tags (assuming that each field contains no more than a few words)
".45 ACP - because shooting twice is just silly" - JSOP, 2010 ----- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010 ----- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
I like XML in the database.
It allows me to put stuff I don't care about somewhere I don't need to see it. But the downstream processes can get it and stick it in browser-based UIs easily.
|
|
|
|
|
If you want your data as xml from the database, you can do that in the query that pulls the data.
".45 ACP - because shooting twice is just silly" - JSOP, 2010 ----- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010 ----- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Quicker to do it once ahead of time -- it doesn't change during the day.
|
|
|
|
|
Marc Clifton wrote: But the core software is built on a third party product that puts everything in XML because that way its, um, extensible!!! Yay for buzzwords getting in the way of real work.
Jeremy Falcon
|
|
|
|
|
Amateurs.
You should create a servicelayer that converts between xml and json, and then store it in a Mongodb.
That's what nosql is for.
|
|
|
|
|
I'm not hearing anything weird or out of the ordinary
|
|
|
|
|
I've seen tables much, much bigger than that storing XML. And sometimes, you just need to store XML without knowing anything about the structure of it, like if it comes from another system and you need it verbatim.
Most interesting approach I've seen is not to use a native XML type, but to compress it to binary, then convert that to base64 and store that in nvarchar columns. Which drowns everything in zeros and probably makes things bigger than storing native, with the added benefit that its practically impossible to query.
Regards,
Rob Philpott.
|
|
|
|
|
Marc Clifton wrote: But the core software is built on a third party product that puts everything in XML because that way its, um, extensible!!!
Isn't that the definition of persisted object stores (ie NoSQL)?
Marc Clifton wrote: To me, that just seems in[s]ane.
Just not well thought out. But something that follows naturally for over enthusiasm for new technology (not blaming XML but rather that it was 'new' and thus used.)
Marc Clifton wrote: And this isn't the only table that contains these XML blobs,
Could be worse...could be invalid or even ill-formed xml.
|
|
|
|
|
jschell wrote: ill-formed xml
No such thing.
|
|
|
|
|
PIEBALDconsult wrote: No such thing.
Not sure what you mean but I dealt with ill formed xml many times.
Both in preventing it and in trying to parse data from something that had been created incorrectly.
|
|
|
|
|
If it isn't well-formed, it isn't XML.
|
|
|
|
|
Well I worked in the real world where things are not perfect but for which I still must create solutions.
And something that looks like 'xml' and which everyone else from the current developers and the external service provider refers to as 'xml' is in fact xml to me as a relevant term to apply to it to avoid confusion. Regardless of whether it is valid or not. Being a professional I will be sure to note in the code that it is not valid and as such the code I wrote to deal with it was needed.
|
|
|
|
|
"valid" is another matter altogether.
|
|
|
|
|
PIEBALDconsult wrote: "valid" is another matter altogether.
Being pedantic doesn't change what I said. I have written a lot of code which both validates whether xml is ill-formed and valid such as via XSD/DTD validation and business validation. And I have written and maintained XSDs for years as well.
But none of that changes the meaning of my previous post. Nor that my goal is to deliver business solutions not idealism.
|
|
|
|
|
jschell wrote: Just not well thought out. But something that follows naturally for over enthusiasm for new technology (not blaming XML but rather that it was 'new' and thus used.)
I remember a project in Germany where 'everything had to be in XML'. It was not overzealous engineers, but marketing that drove this madness. I'm sure today they are in 'blockchain' up to their elbows.
|
|
|
|
|
I have seen technology driven by the market demands.
But to be fair I think that is a more legitimate reason versus some single developer claiming that technology X will be used because it is 'better' or because it is a 'solution' to a business need.
|
|
|
|
|
The gun shop where I work uses a specialized product for sales and gun tracking, and lately it's been crashing and losing indices several times daily. I just discovered that this state-of-the-art system is based on a Visual FoxPro database!
How many other products out there are still using long obsolete technologies, I wonder? I've still got a copy of Paradox, if anyone needs it...
Will Rogers never met me.
|
|
|
|
|
Perhaps they just did not understood the essence of a 3rd party software 'putting everything into XML' is to connect it to a queue and process messages with a middleware on the other side, storing everything to standard relational database?
|
|
|
|
|
[Edited to avoid enraging the Britishers]
There's much to be had, at the the timber shop. (5, 3)
Answer was "Great Deal"
I'll post today's shortly.
One morning I shot an elephant in my pajamas. How he got in my pajamas, I don't know.
modified 6-Dec-17 4:48am.
|
|
|
|
|
Gotten? Any chance of speaking modern English?
|
|
|
|