views:

63

answers:

5

What are the benefits of storing XML in SQL Server over storing JSON in a varchar field?

Any tutorial available for how to use the XML data type effectively?

Do I need to provide the dtd / xml schema somehow? I've heard it is optional, right?

Thank you.

UPDATE: here's the answer to the last part of the Q.

XML schema information is used in storage and query optimizations. Typed XML instances contain typed values in the internal, binary representation as well as in XML indexes. This provides efficient processing of typed XML data.

quoted from: http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx

+1  A: 

you can query for individual xml property values if you use the xml data type. I don't believe the same functionality is available for json:
http://msdn.microsoft.com/en-us/library/ms191474.aspx

I don't believe that the xsd is required as I've used the query feature without having that defined before

Joel Martinez
is the query efficient?
Henry
Honestly, I don't know ... the feature I implemented it may as well have used json as it was just used as a blob storage. Though I did find it useful to troubleshoot some issues a few times that I could query within that field. So I've never had a production feature use this
Joel Martinez
@Henry: You can add xml indexes to help if necessary: http://msdn.microsoft.com/en-us/library/ms345121%28SQL.90%29.aspx
OMG Ponies
@Henry, the XML data type will be more efficient and optimized than parsing the same data in a plain varchar column!!!
KM
+4  A: 

XML in SQL Server 2005 and up allows you to directly manipulate the XML stored inside the database table using XQuery - you can't really do that with JSON in a VARCHAR field.

Check out XML Support in Microsoft SQL Server 2005 and Fundamentals of the XML Datatype in SQL Server 2005 for more info and more background.

Also, the XML stored in a XML column in SQL Server is "tokenized" and pre-parsed - it's not just stored as plain text. And you can even put indices on your XML - on its nodes, its values - whatever you need.

marc_s
+4  A: 

Storing it as XML allows you to leverage the SQL XML support: XPATH, XQUERY, XML Indexes and such. These allow for efficient search and manipulation of the content. I recommend you read XML Best Practices for Microsoft SQL Server 2005

JSON content in VARCHAR would be opaque to searches and manipulation.

Remus Rusanu
+2  A: 
  1. XML can be indexed for more performant querying.
  2. Data can be extracted from XML data usng XPath.
  3. A schema can be provided to constrain the XML to a secifiation but this is optional.
  4. Client libraries understan the XML data type and can send/receive it more carefully/easily.

None of the above is available for JSON stored in varchar.

Daniel Renshaw
+1  A: 

Storing XML in SQL 2005 is great. You can put the whole XML file in a single field, then run SELECT commands to pull out certain attributes and elements. Putting a XSD is not neccesary. I don't believe it has any JSON support even in 2008 although I could be wrong. Here's a good starter article on it.

dferraro