4 ms·
I had a project where I used an xml column[1] in MSSQL2008, since we had variable schema data and didn't want to store it vertically (one row per value), and de
by binarymax 14y ago
I had a project where I used an xml column[1] in MSSQL2008, since we had variable schema data and didn't want to store it vertically (one row per value), and definitely didn't want to alter schema on the fly. While I wouldn't necessarily recommend doing what I did, I don't see anything wrong with it and it solved that specific problem rather eloquently. We got the data out just fine with XQuery.
Believe it or not it was extremely fast...but only after playing with the xml format for awhile and making sure the indexes all fit nicely. You can, almost surprisingly, index specific XML fields inside of xml data-type columns[2].
I can almost hear you all cringing after reading that :)
[1] http://msdn.microsoft.com/en-us/library/ms190936(v=sql.90).aspx http://msdn.microsoft.com/en-us/library/ms190936(v=sql.90).a...
[2] http://msdn.microsoft.com/en-us/library/ms191497(v=sql.90).aspx http://msdn.microsoft.com/en-us/library/ms191497(v=sql.90).a...
- eli 14y agoI don't think you should feel bad about that. Though if you were using Postgres instead of MSSQL, you would probably consider JSON (or HSTORE) instead of XML since Postgres 9.2 has a built-in JSON column type.
- fusiongyro 14y agoPostgres has had an XML type for a lot longer than that. There's also better built-in support for XML than JSON because it's been in there longer.
- meaty 14y agoWe had an entire system based on SQL Server's "FOR XML" output clause and XML data islands in IE. It was an unmitigated fucking disaster zone that took 5 years to get rid of. That put us off the above pretty sharpish.
- DenisM 14y agoI was on the dev team that put together the XML data type support in SQL Server. We had bigger dreams back then, and I still do. :)