Persisting data as XML in a SQL Server database?

Question

I am working on a WPF application for a vertical market. In a discussion today about how to build the data structure to support the selection of a dozen checkboxes and radio buttons, I thought about the possibility of serializing an object to XML and storing that in SQL Server, then deserializing it when the data needs to be displayed.

My question is about storing XML in SQL Server 2008. My colleagues think this approach is a bad idea and put forward the possibility of using a generic list table that stores data in name-value pairs.

What are the pros and cons of storing XML data in SQL Server 2008? Am I out of my mind on this, or am I on to something?

Answer 1

The problem I see is that XML is a storage medium, and so is SQL Server. So you're storing data in one medium, and then storing that in another storage medium.

Can you not just skip the middle man and create a table? either:

• key-value pairs like your colleagues suggest or
• A table with a dozen columns, 1 for each selection on your form.

The latter, would permit easy data-binding from sql server to your form.

Answer 2

Space Usage

XML storage takes less space compared to the NVARCHAR(N) storage. One of the reasons for this is that, the XML data type converts element/attribute names to internal identifiers and store the ID instead of the element name. This saves lot of storage space.

For example, look at the below example:

DECLARE @x NVARCHAR(MAX)
SELECT @x = '
  <Employees>
    <EmployeeFirstNameLastNameandMiddleName>Jacob v Sebastian</EmmployeeFirstNameLastNameandMiddleName>
    <EmployeeFirstNameLastNameandMiddleName>Someone x somewhere</EmployeeFirstNameLastNameandMiddleName>
  </Employees>'

The test:

SELECT DATALENGTH(@x) AS Length

Result:

Length
------
498

...versus:

DECLARE @x1 AS XML
SELECT @x1 = CAST(@x AS XML)
SELECT DATALENGTH(@x1) AS Length

Result:

Length
------
218

Note that the NVARCHAR version took 498 bytes and XML version took only 218 bytes.

Performance

Generally speaking, XML might give you better performance. However, it depends on the usage of the data. If you need to query the XML value and extract the information from nodes/attributes, XML will give you the best performance. If you use NVARCHAR, you will need to convert the value to XML at runtime and this will add overhead to the query processor.

Secondly, less size means less IO needed while reading and writing. This can directly translate to performance gains.

If you store the data just for the sake of storing, and no query operations are done inside the XML document, It might be a better choice to store the data as VARBINARY. This might give better performance as SQL Server does not need to perform the XML related validations and formatting while storing and reading data.

NVARCHAR vs VARBINARY

If you don't want to store the data as XML, your next best option is VARBINARY(MAX). Storing the values in NVARCHAR can cause you encoding related problems.

Taken from the post here.

---

I have to admit, for the sake of dynamically laying out forms, it will be far less of a pain to administrate via xml, which you can store in the db, rather than using a key/value pair in the db.