XML Storage

  • Mike C

    SSC-Insane

    Points: 23224

    Comments posted to this topic are about the item XML Storage

  • Jamie-2229

    SSCrazy Eights

    Points: 8151

    Excellent question.

    Jamie

  • spaghettidba

    SSC Guru

    Points: 105661

    Really excellent question!

    Knowing nothing about XDM I tried to figure out what the storage sizes would be running this command:

    SELECT datalength(@x)

    SELECT datalength(@n)

    The result was:

    -----------

    169

    -----------

    486

    This was a way to solve it...

  • OCTom

    SSChampion

    Points: 11755

    It's good to learn something new. Thank you.

    :satisfied:

  • ramkirangj

    Grasshopper

    Points: 18

    Its very good to learn new things.

    Keep on asking such questions so that people like us who new to sql can learn it perfectly.

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    As always excellent question with very good explanation.

    I am just wondering why you get a different DATALENGTH than I do (I get 486)

    Thanks!

    Best Regards,

    Chris Büttner

  • Anipaul

    SSC-Insane

    Points: 24681

    Excellent question 🙂

  • IN_Sandeep

    SSCertifiable

    Points: 6259

    Good..

    --

  • hong1tao

    SSC Enthusiast

    Points: 129

    change sql as below

    DECLARE @x xml,

    @n varchar(1000);

    SET @n = N' ';

    SET @x = CAST(@n AS xml);

    SELECT 'varchar length = ', DATALENGTH(@n)

    UNION

    SELECT 'xml length = ', DATALENGTH(@x);

    result is

    varchar length = 158

    xml length = 169

  • Jon Monahan

    SSCarpal Tunnel

    Points: 4901

    I found similar results to the previous poster regarding the use of VarChar vs. NVarChar (for those of us without having to support multiple platforms...).

    In my results - varchar ended up being half the length of what nvarchar produced - but still not as small as the XML size.

    varchar length = 243

    xml length = 169

  • Mike C

    SSC-Insane

    Points: 23224

    Hi all,

    Thanks for the feedback. The difference between varchar, nvarchar, and xml is an interesting aspect of XML storage. The gist of it is that the xml data type stores string data internally as Unicode (nvarchar), so when you assign varchar data to an xml data type the names and values of nodes are converted implicitly to Unicode for internal storage in the XDM. Unfortunately it's generally difficult to predict the size difference between XML string data and XDM instances; there are several factors affecting it:

    * As mentioned, string values and node names are stored as Unicode (even if source string is non-Unicode)

    * A lot of duplication is removed, since metadata duplication can be removed (start and end tags are replaced with a tree-like structure)

    * An XDM instance can be strongly typed via XML Schema, so numeric values, dates, etc., can be stored internally in a non-textual format

    Basically the size difference generally has to be determined on a per-XML instance basis.

    Thanks,

    Mike C

  • hong1tao

    SSC Enthusiast

    Points: 129

    BTW, I remove space and newline from @n

    hong1tao (7/23/2008)


    change sql as below

    DECLARE @x xml,

    @n varchar(1000);

    SET @n = N' ';

    SET @x = CAST(@n AS xml);

    SELECT 'varchar length = ', DATALENGTH(@n)

    UNION

    SELECT 'xml length = ', DATALENGTH(@x);

    result is

    varchar length = 158

    xml length = 169

Viewing 12 posts - 1 through 12 (of 12 total)

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