Pros and Cons of XML storage in SQL Server

Question

Pros and Cons of XML storage in SQL Server

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
June 16, 2010 at 11:15 am

#222205

We are considering using the XML datatype for a new application, and I have been asked to provide - at high level - the pros and cons of XML storage in SQL Server, as well as scenarios in which XML is a good option.
I have some idea what to talk about, but was wondering if I could get additional input here.
Any advice, links etc. would be appreciated.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

We're using XML files only for getting data from a DB2 into SQL Server through a (required) 3rd party middleware.

The reason we're using XML is to be flexible in terms of add/remove columns to transfer since this can be done without wasting money on the middle ware folks to change the mapping. So, XML is used only to compensate a semi-optimal business structure 😉

Other than that, we don't use it at all sine we couldn't find a business case (in our departement) that couldn't be covered using T-SQL and a relational database model.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

lmu92 (6/16/2010)
We're using XML files only for getting data from a DB2 into SQL Server through a (required) 3rd party middleware.
The reason we're using XML is to be flexible in terms of add/remove columns to transfer since this can be done without wasting money on the middle ware folks to change the mapping. So, XML is used only to compensate a semi-optimal business structure 😉
Other than that, we don't use it at all sine we couldn't find a business case (in our departement) that couldn't be covered using T-SQL and a relational database model.

Thank you.

Indeed a good case for using XML for data storage is when data is semi-structured, ie. the fields are not known at the outset;

that's often the case with third-party tools.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]