Home Forums Programming XML if you're using typed xml in sql server 2008, does the xml you store need to have every node that's in the XSD? RE: if you're using typed xml in sql server 2008, does the xml you store need to have every node that's in the XSD?

  • November 27, 2012 at 11:28 am

    #1562795

    You don't need all the nodes. XML, by default, doesn't contain nodes with NULL values, so the engine just assumes those are NULL if they're missing.

    Untyped XML can actually take more storage than the exact same data as varchar(max).

    I recently imported a fairly large XML file into a table, so I tested storage size with it by keeping it raw text (varchar(max)) in one table and XML in another table, then checking the Disk Usage By Table report for the database. The raw text took 680k for the table (allocated), 664 (data), while the XML took 1,128 allocated and 1,088 data.

    I don't have time right now to set up an XSD for that table, so can't test typed XML right now. Sounds like you have what you need to test that. Try text, untyped XML (no XSD, but stored in an XML datatype column), and typed XML (XML datatype with a declared XSD). See what sizes you get with each of those.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon