Store XML/XSD in SQL Server 2008

  • Thread starter Thread starter schouw
  • Start date Start date
S

schouw

What is the easiest way to save down an XML /XSD DataSet in SQL Server
2008 from C#?

I want to make as little effort as possible to update, insert and
select XML stored in the database.

Ideas on the top of my head:

1. Use DataSets and hold a copy in-memory of what I want to store
down.

2. Can I create a stored procedure that takes in an XML string and
then inerts it into the database?

3. Use SOAP and put a server in-between that manages the persistent
data.

Lars
 
What is the easiest way to save down an XML /XSD DataSet in SQL Server
2008 from C#?

I want to make as little effort as possible to update, insert and
select XML stored in the database.

How do you want the data stored?

TEXT column (traditional CLOB)
multiple columns (of various types)
XML column (with schema validation, XPath query etc.)
?
Ideas on the top of my head:

1. Use DataSets and hold a copy in-memory of what I want to store
down.

I think that would fit with the multiple columns requirements.

In general I am not so keen on DataSet's.
2. Can I create a stored procedure that takes in an XML string and
then inerts it into the database?

Ofcourse.

But that does not change much.

You still need to decide on how you want the data stored.

And you need some SQL statements to do it.

The only difference will be that those statements
now are in a SP in the database instead of being in
your app.
3. Use SOAP and put a server in-between that manages the persistent
data.

You can do that.

But it does not change anything.

logic code---(call)---code saving in db---(?)---db
logic code---(web service call)---code saving in db---(?)---db

The interesting part with '?' is still the same.

Arne
 
Hi Arne,

I was thinking of storing my data in multiple columns.
I created the database structure my using Altova XMLSpy from XSD files
I created myself. So that product takes care of the XSD to Rational DB
format in the SQL Server 2008.
Next I want to load this data up in C# with as little effort as
possible, and at the same time enable filtering / select of rows.
But in my case it is more like a data subset than one row of data. So
my idea was Dataset since it it seems to be able to do this for me.

I want to enable XML Schema validation and use XPath (possible only on
the client side not interacting with the database). This will also
come in handy if other people want to access my database not
from .NET.

This is the route I follow now, which might not be the best route way.

Lars
 
I was thinking of storing my data in multiple columns.
I created the database structure my using Altova XMLSpy from XSD files
I created myself. So that product takes care of the XSD to Rational DB
format in the SQL Server 2008.
Next I want to load this data up in C# with as little effort as
possible, and at the same time enable filtering / select of rows.
But in my case it is more like a data subset than one row of data. So
my idea was Dataset since it it seems to be able to do this for me.

I want to enable XML Schema validation and use XPath (possible only on
the client side not interacting with the database). This will also
come in handy if other people want to access my database not
from .NET.

This is the route I follow now, which might not be the best route way.

Given the context then:

db table--data adapter--data set--XML

seems as a pretty obvious solution.

It could be done differently, but I would expect the alternatives
to require more code.

Arne
 
Back
Top