I've written an ASP.NET application that uses SQL SERVER Database. During modification of the Database, an experienced guru in .NET+DBMS said, one of the problems of relating/mapping multiple tables could be elegantly solved with .NET XML strings. I've never heard of this technique, but not to sound ignorant of such "hacks", I cannot ask him about it. Is there a way to INSERT/UPDATE/DELETE XML strings like a regular database table(without using Linq to XML)?
6
-
I think they just meant storing XML inside the database (in a text column), rather than trying to normalize all the data into other tables. It's an approach that sometimes works, but frankly I usually end up just wishing I had normalized the data.Mike Christensen– Mike Christensen2012-11-29 19:04:28 +00:00Commented Nov 29, 2012 at 19:04
-
I don't know what he meant, but I was stunned when he said Use XML strings instead of so many tables in the meeting. And he has said that more than once when I've presented a schema @MikeChristensenAniket Inge– Aniket Inge2012-11-29 19:05:42 +00:00Commented Nov 29, 2012 at 19:05
-
2To be completely honest, this is a discussion that you should be having with him. Swallow your pride, ask him what he means and have him walk you through a quick example. Developers are not omniscient, we cannot know everything.Mendhak– Mendhak2012-11-29 19:08:52 +00:00Commented Nov 29, 2012 at 19:08
-
Welp it's definitely an approach. If you always need to load all the data at once, it's faster than doing a bunch of JOINs. The drawbacks are you can't really query on that data (Though some RDBS's have special XML column types and support XPath queries within the data) and if you need to upgrade/change things, it can be a royal pain.Mike Christensen– Mike Christensen2012-11-29 19:10:11 +00:00Commented Nov 29, 2012 at 19:10
-
@Shah I agree, but the next meeting will be a week after now as he is travelling abroad + he is one of those client-side gurus. So, I am stuck with no option but to ask here. I have asked everyone on the team if they understood what he meant. I have no choice but to ask on stack overflow.Aniket Inge– Aniket Inge2012-11-29 19:10:29 +00:00Commented Nov 29, 2012 at 19:10
|
Show 1 more comment
2 Answers
If it's not Linq to XML, then he may mean XML Columns. SQL Server 2005 onwards lets you define XML Columns in your tables. So you can have actual XML in fields in the table and you can then perform XQuery operations on them.
I do not know what your context is and what the problem statement is so I cannot tell you if this is a good or bad idea. It has its uses and can be useful in certain cases, but not in an extensive, scalable way. It really depends on what you will be using it for.
1 Comment
Mike Christensen
I've seen plenty of first answers that are not
+1 worthy.I am not sure what he meant, but it's possible to serialize your object to XML and pass the XML as a parameter to a Stored Procedure.
DECLARE @PersonJobsXML XML
SELECT @PersonJobsXML = '<PersonJobs>
<PersonId>24234</PersonId>
<Job>
<JobTitle>Engineer I</JobTitle>
<CompanyName>ACME</CompanyName>
</Job>
<Job>
<JobTitle>Engineer II</JobTitle>
<CompanyName>World Inc.</CompanyName>
</Job>
<Job>
<JobTitle>Engineer II</JobTitle>
<CompanyName>Tek Corp</CompanyName>
</Job>
</PersonJobs>'
SELECT PersonJobs.Job.value('../PersonId[1]', 'INT') AS PersonId
, PersonJobs.Job.value('JobTitle[1]', 'VARCHAR(200)') AS JobTitle
, PersonJobs.Job.value('CompanyName[1]', 'VARCHAR(200)') AS CompanyName
FROM @PersonJobsXML.nodes('//PersonJobs/Job') AS PersonJobs ( Job )
This allows you to pass a list of object to the database with just one call; the downside is that the size could be quite limited
But If you are using SQL server 2008 you should look at Table-Valued Parameters: see Table Value Parameters in SQL Server 2008 and .NET (C#)
