CODEWITHSUNDEEP
sql-serverxmlsql-server-2008sql-server-2005xml-serialization
Nilish
Nilish

Reputation: 1076

Advantages of XML DataType in SQL Server 2005

I was trying to understand the basic advantage of using XML DataType in SQL Server 2005. I underwent the article here, saying that in case you want to delete multiple records. Serialize the XMl, send it in Database and using below query you can delete it..

enter image description here

I was curious to look into any other advantage of using this DataType...

EDIT

Reasons for Storing XML Data in SQL Server 2005

Here are some reasons for using native XML features in SQL Server 2005 as opposed to managing your XML data in the file system:

  1. You want to use administrative functionality of the database server for managing your XML data (for example, backup, recovery and replication).

    My Understanding - Can you share some knowledge over it to make it clear?

  2. You want to share, query, and modify your XML data in an efficient and transacted way. Fine-grained data access is important to your application. For example, you may want to insert a new section without replacing your whole document.

    My Understanding - XML is in specific column row, In order to add new section in this row's cell, Update is required, so whole document will be updated. Right?

  3. You want the server to guarantee well-formed data, and optionally validate your data according to XML schemas.

    My Understanding - Can you share some knowledge over it to make it clear?

  4. You want indexing of XML data for efficient query processing and good scalability, and the use a first-rate query optimizer.

    My Understanding - Same can be done by adding individual columns. Then why XML column?

Upvotes: 1

Views: 3610

Answers (2)

Chris S
Chris S

Reputation: 65436

I would say of the 4 advantages you've listed, these two are critical:

You want to share, query, and modify your XML data in an efficient and transacted way

SQL Server stores the XML in an optimised way that it wouldn't for plain strings, and lets you query the XML in an efficient way, rather than requiring you to bring the entire XML document back to the client. Think how inefficient it is if you want to query 10,000 XML columns, each containing 1k of data. For a small XPath query you would need to return 10k of data across the wire, for each client, each time.

You want indexing of XML data for efficient query processing and good scalability, and the use a first-rate query optimizer

This ties into what I said above, it's far more efficiently stored than a plain text column which would also run into page fragmentation issues.

Upvotes: 1

Jimbo
Jimbo

Reputation: 2537

Pros: Allows storage of xml data that can be automatically controlled by an xml schema - thereby guaranteeing a certain level of data quality Many web/desktop apps store data in xml form, these can then be easily stored and queried in the database - so it is a great place to store xml data that an app may need to use (e.g. for configuration settings)

Cons: Be careful about using xml fields, they may start off as innocent storage but can become a performance nightmare if you want to search, analyse and report on many records.

Also, if xml fields will be added to, changed or deleted this can be slow and leads to complex t-sql.

In replication, the whole xml gets updated even if only one node changes - therefore you could have many more conflicts that cannot easily be resolved.

Upvotes: 1

Related Questions