XML Data Type

Question

XML Data Type

paul.corujo

SSCommitted

Points: 1891
More actions
November 13, 2008 at 10:20 am

#94094

Hey All,
I will be storing some XML in my database strictly for storage and retrieval purposes. I know that with SQL 2005 there is powerful XML support and that this XML can be indexed. If I do not plan on changing the XML itself would it be better to store the data as nvarchar(max)? Again I am just looking for the quickest way to search the XML once it is stored. Thanks in advance for all your help and guidance.
Paul

Viewing 9 posts - 1 through 8 (of 8 total)

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

winash SSChampion Points: 11446 More actions · Answer 1

If you are going to be searching the XML then I'd suggest store it as XML - i.e. create an XML datatype column and store the XML in that column.

It's easier to use XQuery to search an XML than to store it as plain text and use string searching...

paul.corujo SSCommitted Points: 1891 More actions · Answer 2

Thanks so much for your response. I have tried both the XQUERY as well as putting it into a column of nvarchar(max) type. Then I converted it back to XML for the search. The results were about the same. Somebody had suggested denormalizing the xml into a table. Any thoughts on that?

aftabsatti SSC Enthusiast Points: 193 More actions · Answer 3

Can you paste the xml that you are storing, Its always better to store the data in tables instead of using xml datatype column and search can be optimized by creating indexes.

paul.corujo SSCommitted Points: 1891 More actions · Answer 4

paul.corujo

SSCommitted

Points: 1891

November 14, 2008 at 1:19 pm

#898855

surfaceable>

Thanks again for your help.

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 5

You can't paste XML in the forums.

Try attaching it as a txt file.

paul.corujo SSCommitted Points: 1891 More actions · Answer 6

paul.corujo

SSCommitted

Points: 1891

November 14, 2008 at 1:28 pm

#898864

Sorry about that.

aftabsatti SSC Enthusiast Points: 193 More actions · Answer 7

Create a table with fields :

Feature_Id (Primary key),

Fk_MainTablekey ,

Type,

Id,

Status

winash SSChampion Points: 11446 More actions · Answer 8

I wouldn't say it is "always" better to store data in tables instead of using XML - it depends on the data and the application.

In your case you could follow the table structure suggested by aftabsatti - or you could either store the data in an XML column or an nvarchar column and cast to XML (I know - I'm only listing the options available and not making any recommendations :blink: )....

What is the structure of the table you were planning to store the XML data in - i.e. were there other columns or was there only the XML data and perhaps an ID column? What kind of searches will you be doing on the data? Do you need to join to other tables based on values from the XML?

Storing data in the relational format is normally preferred but there will be cases where the flexibility afforded by storing in XML format works better...