October 7, 2015 at 4:41 pm
I've never worked with the XML data type in SQL Server, although I know its been there for a few iterations of SQL SErver. Now I've got a situation in which it might be a good idea to store some configuration data as XML, since that's the way it comes. (We had thought about storing the data in a VARCHAR(MAX) field.)
So really I've got two questions related to the XML data type. The first question is does the XML data type have a size limitation? For example do you do something like:
ConfigFile XML(1000) NULL
Or is it just something like this:
ConfigFile XML NULL
The second question is persisting the data to a file. As the name I choose for the variable suggests, we want to save the data from a configuration file into a SQL Server database. How do we go about doing that? We'll be developing a C# application, it will read and write the data both from the SQL table and the user's local HD. How do we go about saving it?
Kindest Regards, Rod Connect with me on LinkedIn.
October 7, 2015 at 4:51 pm
Looks like 2GB is your limitation on XML data.
https://msdn.microsoft.com/en-us/library/hh403385(v=sql.110).aspx
As far as the question on how to store it, you can define a column in a table with a data type of XML. You could then store your configuration files in that column as XML. Pulling it out to save as a file is as easy as querying the column.
October 8, 2015 at 7:00 am
While SQL Server can store XML, it's not great at it. You might want to consider looking at one of the ID/Value databases that's meant to cope with this type of data. Maybe Hadoop or DocumentDB.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 8, 2015 at 8:33 am
Grant Fritchey (10/8/2015)
While SQL Server can store XML, it's not great at it. You might want to consider looking at one of the ID/Value databases that's meant to cope with this type of data. Maybe Hadoop or DocumentDB.
I very seriously doubt that the agency I work for would consider DocumentDB. Heck the overwhelming majority of applications dependent upon databases use MS Access for the backend. However I've heard that a recent version of SQL Server support Hadoop, so I'll look at that. If memory serves Hadoop was introduced with SQL 2012, which I think we've got some instances of. Assuming that's correct, is Hadoop turned on automatically? If not then I'll have an uphill battle to try and get it turned on to even experiment with it.
Kindest Regards, Rod Connect with me on LinkedIn.
October 8, 2015 at 8:43 am
Rod at work (10/8/2015)
Grant Fritchey (10/8/2015)
While SQL Server can store XML, it's not great at it. You might want to consider looking at one of the ID/Value databases that's meant to cope with this type of data. Maybe Hadoop or DocumentDB.I very seriously doubt that the agency I work for would consider DocumentDB. Heck the overwhelming majority of applications dependent upon databases use MS Access for the backend. However I've heard that a recent version of SQL Server support Hadoop, so I'll look at that. If memory serves Hadoop was introduced with SQL 2012, which I think we've got some instances of. Assuming that's correct, is Hadoop turned on automatically? If not then I'll have an uphill battle to try and get it turned on to even experiment with it.
Microsoft supports Hadoop, yes. But not SQL Server. It's through HDInsight as part of Azure. If they won't like DocumentDB, they're just as likely to dislike HDInsight.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 8, 2015 at 8:49 am
Grant Fritchey (10/8/2015)
Rod at work (10/8/2015)
Grant Fritchey (10/8/2015)
While SQL Server can store XML, it's not great at it. You might want to consider looking at one of the ID/Value databases that's meant to cope with this type of data. Maybe Hadoop or DocumentDB.I very seriously doubt that the agency I work for would consider DocumentDB. Heck the overwhelming majority of applications dependent upon databases use MS Access for the backend. However I've heard that a recent version of SQL Server support Hadoop, so I'll look at that. If memory serves Hadoop was introduced with SQL 2012, which I think we've got some instances of. Assuming that's correct, is Hadoop turned on automatically? If not then I'll have an uphill battle to try and get it turned on to even experiment with it.
Microsoft supports Hadoop, yes. But not SQL Server. It's through HDInsight as part of Azure. If they won't like DocumentDB, they're just as likely to dislike HDInsight.
Nuts! OK, thank you Grant.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply