January 8, 2009 at 2:41 am
Which data type of SQL server 2005 is equivalent to BLOB data type ? Is it nvarchar() ?
I want to store XML in database. Can we use XML datatype for this purpose ?
January 8, 2009 at 5:37 am
Binary large object data would typically end up in an image, text or ntext field. varchar(max) and nvarchar(max) are also capable of holding large amounts of data. For XML data, you should use the XML data type as it will also enforce XML schema.
Remember that storing XML data in an XML field limits it's usefulness in your database. If you intend to query and update portions of the XML data, it is usually best to split it up into actual relational tables rather than leaving it as one big document.
January 8, 2009 at 10:08 am
Hi
The best option is to use XML data type to store this kind of information. So this way you can even validate the XML by having XSD. From performance point of view you can have indexes also on this.
Check out the below link
http://www.developer.com/db/article.php/3531196
Thanks -- Vj
January 8, 2009 at 11:53 pm
I just want to store XML in Database. Dont want to enforce schema on it. Dont want to update portions of it.
If XML gets modified, just want to update the old XML with new XML.
Another requirement is - retrieve XML through a stored procedure and return it to java application.
With these requirements, is it a good idea to use xml data type ? I think it will not make any difference if I use nvarchar(max) or xml with the above mentioned requirements ? Correct me if I am wrong.
January 9, 2009 at 7:40 am
First off, see the following articles and read up on the XML support:
http://msdn.microsoft.com/en-us/library/ms345118(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms345115(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms345117.aspx
Per the articles referenced above, you can store the XML in any of the following data types. The choice depends upon what you intend to do with the data as stored.
XML
1. Best for manipulating the XML.
2. Your XML will be validated to ensure that it is a valid XML structure (i.e., proper ending tags etc.)
3. Can be bound to a schema (XSD)
4. Can be indexed
5. Con: Will not preserve the original XML format e.g., imbedded whitespace, etc. See the above articles for specifics. So if you have to store an exact copy (e.g., including extra whitespace) of the XML (e.g., for legal reasons) then you may wish to use a different data type.
VARBINARY(MAX)
Best for just storing XML "blobs". It is a binary stream passed back and forth to the application. Being a binary stream it handles things like UTF-8 encoding (which is a varying multi-byte encoding). This is easilly converted to the XML data type later on.
Cons: Will have to CAST to XML should you wish to work with it as XML.
NVARCHAR(MAX) / VARCHAR(MAX)
Although you can store XML in these data types they should be avoided due to character conversion side-effects due to encoding issues between the XML (which can have all sorts of encoding) and the database. E.g., UTF-8 encoding.
And, BTW, .Net XML is UTF-16 encoding.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply