Typed XML Storage

Question

Typed XML Storage

huskerwendy

Hall of Fame

Points: 3633
More actions
February 22, 2007 at 10:36 am

#72625

We're using an XML data type column to store unstructured data in our database and I have read conflicting information regarding the storage of the data when using typed XML. Some of the articles I've read indicate that the data is shredded at runtime when the XML data type field is queried and some indicate that it is shredded and inserted into system tables when the XML document is inserted into the database? I appreciate any help you can give me.
Thanks!

Wendy Schuman

Viewing 6 posts - 1 through 5 (of 5 total)

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

Mike C SSC-Insane Points: 23224 More actions · Answer 1

Hi Wendy,

The XML data is only stored in a pre-shredded format if you add XML Indexes to the column. When you add XML Indexes, SQL Server shreds the XML and uses the shredded representation as an index. XML Indexes can significantly increase performance of XML data type queries, but it also requires that SQL Server maintain the XML Indexes and a considerable amount of storage space.

See BOL "CREATE INDEX" statement for information on creating XML Indexes.

Thanks,

Michael

**** GO HUSKERS!!! ****

huskerwendy Hall of Fame Points: 3633 More actions · Answer 2

Thanks Mike! That really helps clear things up for me. I'll do some research on indexing that column.

P.S. Go Big Red! Are you from Nebraska?

Wendy Schuman

Mike C SSC-Insane Points: 23224 More actions · Answer 3

Lived in Omaha way back in the day, always been a big Husker fan

Whoever told you that XML is shredded when stored in an XML-typed column by default might be confusing the concepts of XDM and shredding.

When the XML is validated against a schema, the XDM is generated and the InfoSet data is stored. Basically the XDM instance is persisted in the database. This means it doesn't have to be re-validated every time you query it, and it's already an XDM instance, which only needs to be re-generated (or changed) when the XML content changes.

Shredding is actually converting the XML data to relational form, which you either have to do explicitly using methods like the .nodes() method of the XML data type, or by declaring XML Indexes.

huskerwendy Hall of Fame Points: 3633 More actions · Answer 4

"Whoever told me" is actually me reading different articles online about SQL Server XML data types, so it's actually me confusing the concepts.

We really do want the XML data shredded so I added a primary index to the column and when I queried it, an index seek was used. Before it was using a table valued function (xml reader with xpath filter). That's exactly what I was looking for. Now I'm going to do some more testing using a secondary index and see if I can get more performance gains.

Thanks for you help!

Wendy Schuman

Mike C SSC-Insane Points: 23224 More actions · Answer 5

LOL. Glad to hear it By "whoever told you", I was referring to the folks who wrote those articles There are different types of secondary XML indexes available, so take a look at the docs and determine which one fits your requirements best.