Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Unanswered: Deserialize JSON to SQL Server Table

    Found a couple of SQL functions which would do the work online, and few CLR ones, but would like to hear your views on any best practices if any for doing the job
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK..so if this is a sample JSO

    Code:
    {
         "firstName": "John",
         "lastName" : "Smith",
         "age"      : 25,
         "address"  :
         {
             "streetAddress": "21 2nd Street",
             "city"         : "New York",
             "state"        : "NY",
             "postalCode"   : "10021"
         },
         "phoneNumber":
         [
             {
               "type"  : "home",
               "number": "212 555-1234"
             },
             {
               "type"  : "fax",
               "number": "646 555-4567"
             }
         ]
     }
    From wiki

    JSON - Wikipedia, the free encyclopedia


    Don't they identify a major "Container" Object that they all belong? Also, I'm assuming it's so free form, that you may or may not have an RI Model to accoumodate it...

    So just like decontructing XML, if a call fails to decontruct, You just have to return an error I assume

    Yes?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Yup ... couple of references I was looking at ...

    A Function to Split JSON Data - SQLServerCentral

    and
    Consuming JSON Strings in SQL Server

    Now ... since this is in the staging db ... with the data being delivered through Service Broker ... performance is of paramount importance. I would be receiving about 500 messages per second ... with 5 "rows" serialized into JSON per message.

    My idea is to simply accept the messages... put them into a temp table and then have a separate process pick them up to deserialize. Now I am unsure if I need to go the CLR way or keep to TSQL ... my mind's telling me to go for CLR but being a core SQL Server developer that does not feel well with me.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •