Post reply

XML Storage

  • July 21, 2008 at 8:38 pm

    #381812

    Comments posted to this topic are about the item XML Storage

  • July 22, 2008 at 5:26 am

    #846287

    Excellent question.

    Jamie

  • July 22, 2008 at 6:17 am

    #846338

    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...

    -- Gianluca Sartori

  • July 22, 2008 at 6:48 am

    #846364

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

    :satisfied:

  • July 22, 2008 at 8:10 am

    #846425

    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.

  • July 22, 2008 at 12:00 pm

    #846589

    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

  • July 23, 2008 at 12:05 am

    #846821

    Excellent question 🙂

  • July 23, 2008 at 12:09 am

    #846823

    Good..

    --

  • July 23, 2008 at 12:35 am

    #846839

    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

  • July 23, 2008 at 6:40 am

    #846975

    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

  • July 23, 2008 at 8:51 am

    #847100

    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

  • July 23, 2008 at 2:48 pm

    #847386

    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 11 (of 11 total)

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