Today I have committed to doing something I said I will never do
-
Today I have committed to doing something I said I will never do: I will be using the XML data type in SQL Server as a primary storage mechanism for a project instead of a normalized database. I am crying a little on the inside but it is the best solution :( (well the best solution that involves using SQL Server)
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. I also do Android Programming as I find it a refreshing break from the MS. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost
I can feel your pain. The queries targeting XML type of data are terribly looking, defining the schema is not intuitive and the whole thing is a big mess. I passed through this about year ago, when I was making an application change log based on XML data and Microsoft Diff Diagram. It was a huge timewaster and it’s not Microsoft or SQL Server fault it’s because the XML is evil.
The narrow specialist in the broad sense of the word is a complete idiot in the narrow sense of the word. Advertise here – minimum three posts per day are guaranteed.
-
Today I have committed to doing something I said I will never do: I will be using the XML data type in SQL Server as a primary storage mechanism for a project instead of a normalized database. I am crying a little on the inside but it is the best solution :( (well the best solution that involves using SQL Server)
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. I also do Android Programming as I find it a refreshing break from the MS. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost
There's an XML data type? I'd just stick it in a varchar.
-
There's an XML data type? I'd just stick it in a varchar.
The xml type is similar to a VARCHAR(MAX) except it also comes with operators that support XPath as well as indexing elements in the xml file through an XML index.
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. I also do Android Programming as I find it a refreshing break from the MS. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost
-
XML. What an absoloute farce it is. It wa supposed ot be the next best thing and all its ended up being used for is storing info like an ini file, only harder to read. What a farce...
Morality is indistinguishable from social proscription
-
The xml type is similar to a VARCHAR(MAX) except it also comes with operators that support XPath as well as indexing elements in the xml file through an XML index.
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. I also do Android Programming as I find it a refreshing break from the MS. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost
I suppose it might be good for postal addresses from multiple countries and such. Even in the U.S. there is a wide variety in addressing.
-
Today I have committed to doing something I said I will never do: I will be using the XML data type in SQL Server as a primary storage mechanism for a project instead of a normalized database. I am crying a little on the inside but it is the best solution :( (well the best solution that involves using SQL Server)
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. I also do Android Programming as I find it a refreshing break from the MS. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost
"Pragmatism must take precedence over elegance, for Nature cannot be impressed."
-
XML. What an absoloute farce it is. It wa supposed ot be the next best thing and all its ended up being used for is storing info like an ini file, only harder to read. What a farce...
Morality is indistinguishable from social proscription
fat_boy wrote:
and all its ended up being used for is storing info like an ini file
Clue less. Marc
-
Today I have committed to doing something I said I will never do: I will be using the XML data type in SQL Server as a primary storage mechanism for a project instead of a normalized database. I am crying a little on the inside but it is the best solution :( (well the best solution that involves using SQL Server)
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. I also do Android Programming as I find it a refreshing break from the MS. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost
Ennis Ray Lynch, Jr. wrote:
I will be using the XML data type in SQL Server as a primary storage mechanism
Welcome to the xark side. It's cool, but it's also an incredible PITA. Beware of an interesting bug I discovered when joining tables--fully qualify your tables when refering to XML fields in some of those bizarre joins, if you don't you will get random results back from your queries. Marc
-
Today I have committed to doing something I said I will never do: I will be using the XML data type in SQL Server as a primary storage mechanism for a project instead of a normalized database. I am crying a little on the inside but it is the best solution :( (well the best solution that involves using SQL Server)
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. I also do Android Programming as I find it a refreshing break from the MS. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost
Indeed so sad, My dealings with the xml datatype in tables have been painful. Hopefully you bill by the hour. Ken By the way the xml datatype as a parameter in a stored procedure is amazing, truely wicked fast for multi data entry.
-
Today I have committed to doing something I said I will never do: I will be using the XML data type in SQL Server as a primary storage mechanism for a project instead of a normalized database. I am crying a little on the inside but it is the best solution :( (well the best solution that involves using SQL Server)
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. I also do Android Programming as I find it a refreshing break from the MS. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost
Ennis Ray Lynch, Jr. wrote:
(well the best solution that involves using SQL Server)
I'm guessing one of the many nosql options would work brilliantly here.
I can imagine the sinking feeling one would have after ordering my book, only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
-
"Pragmatism must take precedence over elegance, for Nature cannot be impressed."
"I am the Ratt Buoy! I'm not impressed!" -- The Ratt Buoy
-
Indeed so sad, My dealings with the xml datatype in tables have been painful. Hopefully you bill by the hour. Ken By the way the xml datatype as a parameter in a stored procedure is amazing, truely wicked fast for multi data entry.
-
The tip looks very interesting. Do you have performance data? Is it faster than sending xml and ripping it upon arrival. I've found that perfomance for things like an order and its order details is great when they are submitted as an xml doc. Does your method add items one at a time or do the equivalent of creating an in memory table an then performing an insert select statement. Ken
-
Today I have committed to doing something I said I will never do: I will be using the XML data type in SQL Server as a primary storage mechanism for a project instead of a normalized database. I am crying a little on the inside but it is the best solution :( (well the best solution that involves using SQL Server)
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. I also do Android Programming as I find it a refreshing break from the MS. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost
I don't understand why XML keeps getting a bad rap. It is well suited for handling data that has a variable 'shape'. It is easy to debug, as opposed to some opaque binary serializaton mechanism. It's my mechanism of choice for persisting information that isn't going into a data base. Granted, it's not the most efficient storage mechanism there is, but that's not a primary consideration in the cases I use it. I can even see using it in a data base application. I wouldn't use it for something I need to search against, or that required heavy lifting with the data base itself. It would be useful though for information that doesn't fit the table/row/column paradigm, like a value that was unique for each entry in the data base.
Software Zen:
delete this;
Fold With Us![^] -
The tip looks very interesting. Do you have performance data? Is it faster than sending xml and ripping it upon arrival. I've found that perfomance for things like an order and its order details is great when they are submitted as an xml doc. Does your method add items one at a time or do the equivalent of creating an in memory table an then performing an insert select statement. Ken
I have no data on that, but I don't see how this could be slower.
KenJohnson wrote:
an insert select statement
So far I've only used the queries I included -- I haven't had an opportunity to use this technique in a real-world scenario. I haven't used it to perform an INSERT, but I expect I could do
INSERT INTO table ... SELECT * FROM @datatable. However, I probably wouldn't because I prefer to insert rows one at a time so I can deal will Exceptions as they happen without affecting the other rows. Bear in mind, that (as far as I can tell) the DataTable IS_A table. -
XML. What an absoloute farce it is. It wa supposed ot be the next best thing and all its ended up being used for is storing info like an ini file, only harder to read. What a farce...
Morality is indistinguishable from social proscription
-
have you heard of xsl transforms?
---------------------------------------------------------- Lorem ipsum dolor sit amet.
swjam wrote:
have you heard of xsl transforms?
No, and I am pretty sure I dont want to! ;P I am a kernel engineer. I use C and write drivers. I keep about as far away form trendy stuff as possible. I use a language that hasnt changed for decades in a kernel that isnt far behind.
Morality is indistinguishable from social proscription