CODEWITHSUNDEEP
c#sql-serverxml
KingCronus
KingCronus

Reputation: 4519

When/how to use the SQL Server XML datatype?

I am in the design stage of a new application at the moment, and I have a bit of a design question which I thought I might be able to crowd source some advice with.

I am connecting to a variety of devices using proprietary connections, each device responds with a collection of data which I parse into a nice class dependent on the machine type. Some machines respond with different data to others, but there is some common ground, for this reason I have a simple inheritance model.

- (abstract) Machine
    - Machine Type A
    - Machine Type B
    - Machine Type C

The "Machine" type has properties such as name, IP address, that sort of thing, which are common to all machines.

At this point I am coming to design my database, and I do not desire having a different table for each of my classes. Additionally, I want to remain flexible to any new machines which are to be added to the system over time.

So...my idea was to have a table with columns for the common features of any "Machine", and then an XML field containing the rest of the information about a particular machine. This way I figured I could remain flexible without having large levels of redundant "null" values in my database.

This would also keep the column count down, since a machine may have 30 or 40 different features to be recorded.

Since I have never used the XML datatype before, I was wondering if I am going about this problem in a sensible manner? What are the implications of using the XML field.

It is also of note that I would not be likely to be searching by any of the fields in the XML, only on the common fields.

Upvotes: 2

Views: 1097

Answers (2)

Lloyd
Lloyd

Reputation: 8396

personally, i suggest you avoid storing XML in the database.. i used this approach once using Oracle 10g on a powerful server.. it's fine to begin with but once you start querying your data, filtering on values from the XML performance begins to plummet.. yes, you can get deeper and deeper into the configuration of XMLDB but it might begin to drive you mad..

try reading some of the other questions on storing arbitrary data first, e.g. Storing Scientific Data in a Relational Database.

It boils down to having a good think about how you plan to use, extract and query the data.

Upvotes: 3

marc_s
marc_s

Reputation: 754458

Depends on how often and how frequently you need to use the data from those custom pieces of data.

If you basically need to store the entire XML as a blob, and - if at all - rarely query it, then the XML datatype is a really good option. You can query into it to extract detail info - it's just not the most performant thing to do.

If that specific data is the heart and soul of your app and you need to use and query that all the time, then I'd probably just create the necessary relational tables and store it that way.

The XML data type is great

  • if you need that data in XML format most of the time (e.g. you get it from some source, need to store it and possibly forward it to some other place)

  • if you need to store data that's not very often queried and used

The XML data type in SQL Server works very well - but again: querying against it is possible, but rather slow (compared to querying relational tables).

Upvotes: 2

Related Questions