CODEWITHSUNDEEP
asp.net.netsqlxml
Aniket Inge
Aniket Inge

Reputation: 25725

Replacing XML with DATABASE in .NET

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)?

Upvotes: 0

Views: 149

Answers (2)

Romhein
Romhein

Reputation: 2216

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#)

Upvotes: 0

Mendhak
Mendhak

Reputation: 8795

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.

Upvotes: 2

Related Questions