deepdark.net - James Green's Blog

Wednesday, April 26, 2006

« Pimpin' Cryptogram, more interesting tha... | Main | Things I have learned today... »

So I mentioned earlier that I am excited about using XPath against XML data stored in SQL Server (v2000 at the moment) but I keep coming up against the same problems, like text/ntext data types are invalid for local variables in a batch and I can't fit the documents I want inside varchar(8000).

The result with the most Google Juice on this points to the solution we all want. There needs to be a version of sp_xml_preparedocument that accepts a pointer to a text/ntext column. There are various solutions around, none of which are kind on server resources (like creating an undetermined number of varchar(8000) variables in scope) and none of which are pretty.

The chances of getting this went from slim to none last Nov. Oh well. I've yet to sink my teeth into SQL Server 2005 and I know they have done a lot with XML in that release, but Microsoft: Do you think every system and team cuts over to the new version of your products the day after launch? I understand you're excited but the same happened to VB6 when VS.NET 1.0 came out.

The project I have in mind involves taking the XML out of Excel documents and working with it using XPath and XSLT. So for now this dosen't live in the data tier. Oh well. The sun will rise on the morning.

Oh and a nod to the brainy and beautiful Anina for "google juice" links.

Database | Geeking Out!

Wednesday, April 26, 2006 9:41:25 AM (AUS Eastern Standard Time, UTC+10:00)

Wednesday, April 26, 2006 6:15:50 PM (AUS Eastern Standard Time, UTC+10:00)

I've toyed with the idea of putting XML into a database, but I fear that the overhead would turn it into a nightmare. I does seem rather contra-intuitive to store XML in a database.

In one sense storing XML in a database breaks all the rules of normalisation. I mean, surely the data contained in the XML should be parsed and stored in a suitable heirarchical structure.

In another sense storing XML in a database is a very powerful abstraction. You can design this whole database backend thingy without explicitly caring about the actual data inside.

I really wish they'd just get a move on and make a decision on XML Binary storage
http://www.w3.org/XML/Binary/
it sure beats HDF or my half-baked idea of using shared memory.
http://hdf.ncsa.uiuc.edu/HDF5/

Robbie | retsilAT NOSPAMnotiinet dot net dot au

Thursday, April 27, 2006 5:56:22 PM (AUS Eastern Standard Time, UTC+10:00)

Thanks for posting Robbie. I wasn't aware of XML & Binary at the w3c. Interesting reading.

With the 'sparse properties' problem of just say there are 1000 possible properties for an entity and any entity can have any combination of them, well we know that works relationally. Implementing it as XML for the sake of it might not offer any benefit.

Where I do think it is acceptable is in the case of interop with other systems that are sending you XML. In these cases it can be very valid to store the XML rather than

What I had in mind for this project was getting word and excel docs saved as XML which would be practically impossible to shread when coming in and reassemble going out. The aim is still having them useful as word and excel documents, but to be able to query them as data sources at the same time.

In Microsoft SQL Server 2005, you can query into the XML in XML columns so you can include attributes and elements in the WHERE clause of a query which I can see as being very powerful.

Ed / James / idiot

Comments are closed.

Contact me on Messenger

On this page....

Twitter Updates