CODEWITHSUNDEEP
c#sqlweb-servicessql-server-2005
James Hill
James Hill

Reputation: 61832

SQL Server: variable length XML storage

I'm in the process of writing an application that interacts with a third party application.

The third party application will be passing my application several raw XML requests. I would like to save each of these requests in a communications log in my DB.

What's the most efficient way to store this variable-length data? VARCHAR(MAX)? NVARCHAR(MAX)?

If one is a better choice than the other (or there is another option I'm missing), please explain why it's the best choice.

Upvotes: 2

Views: 320

Answers (4)

devio
devio

Reputation: 37215

Adding to Yuck's answer:

  • VARCHAR(MAX)

XML means Unicode, and if you choose non-Unicode storage then data loss is almost certain

  • NVARCHAR(MAX)

appropriate if you only want to log the XML data

  • XML

if you want to query XML content later

  • typed XML with XML SCHEMA COLLECTION

only if you have a fixed xml schema (XSD) which will never ever change. (ALTER XML SCHEMA COLLECTION does not support update or delete of XML entities as I understand)

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

XML seams to be the obvious data type of choice when dealing with XML but not always.

Have a look at this article by Robert Sheldon. Working with the XML Data Type in SQL

In some cases, you shouldn’t use the XML data type, but instead use large object storage—VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX). For example, if you simply store your XML documents in the database and retrieve and update those documents as a whole—that is, if you never need to query or modify the individual XML components—you should consider using one of the large object data types. The same goes for XML files that you want to preserve in their original form, such as legal documents. If you need to retain an exact textual copy, use large object storage.

Upvotes: 4

Madison Cat
Madison Cat

Reputation: 75

I have used the XML Datatype for this type of thing MSDN link - XML DataType 2005

Native and allows you to do some normal angle bracket things to the actual data.

Big plus is that I am not converting or messing around with the actual data, and introducing subtle bugs with the actual XML.

Big plus if you want to do anything with the XML like render it.

Downside is that you have to be aware the column is XML data and you need to code for it in upstream apps.

Upvotes: 2

Yuck
Yuck

Reputation: 50855

Since you're using SQL Server 2K5 the best data type to store XML data is xml.

This provides parsing and schema validation features. It also allows you to index the XML data later if need be.

Upvotes: 7

Related Questions