Home Forums SQL Server 2008 T-SQL (SS2K8) How do I use one column for node names and the others for elements in that node? RE: How do I use one column for node names and the others for elements in that node?

  • September 18, 2012 at 8:42 pm

    #1539358

    As a side bar, this is one of the reasons for my strong dislike for XML.

    The following XML takes 193 bytes including a single end-of-line character.

    <PartsList>

    <Part>123</Part>

    <Color>blue</Color>

    <Size>small</Size>

    <Part>124</Part>

    <Color>black</Color>

    <Size>medium</Size>

    <Part>125</Part>

    <Color>red</Color>

    <Size>large</Size>

    </PartsList>

    The following only takes 61 bytes and has all the same information in it.

    Part,Color,Size

    123,blue,small

    124,black,medium

    125,red,large

    That's more than 3 times the number of bytes for XML. And some folks wonder why I/O is one of the biggest bottle-necks on some servers. Imagine what it would cost to get a 300% improvement by changing hardware.;-)

    Considering that both the XML and the CSV are nothing more than flat data, is it really worth using XML for this? Even on hierarchical data, there's a terrible byte-count-bloat to pay when using XML not to mention what it takes to shred the stuff. It's much more effective to pass one CSV for each level of hierarchy... just like the tables where it all came from originally.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)