Is it possible to insert JSON data to SQL Server 2005





     Is it possible to insert JSON data to SQL Server 2005 directly using stored procedure?


    Somthing like for XML we are using sp_xml_preparedocument and openxml. For JSON ???

    Monday, December 03, 2007 2:23 PM

All replies

  • Please forgive my ignorance: What is "JSON data"?

    Monday, December 03, 2007 2:33 PM
  • Hi kent,


    It is JavaScript Object Notation a lightweight notation for data tansfer similar to XML.


    I need to know about SQL Server 2005 support to this kind of data representation.




    Monday, December 03, 2007 2:40 PM
  • Nope not yet.

    You have to write your own parser using .NET CLR.. Try some JSON to XML convertor objects & pass those XML string into database..

    Monday, December 03, 2007 2:44 PM
  • Hi,

       Now i am parsing JSON to XML using Newtonsoft.Json dll and then passing XML to the database for insert. I want to remove this middle layer. Is it possible in SQL Server 2005 itself ?


    Now i am using this approach ...


    DECLARE @hdoc int

    DECLARE @doc varchar(1000)



    SET @doc1 ='

    <?xml version="1.0" encoding="utf-8"?>


    <CustomerID> 10 </CustomerID>

    <EmployeeID> 12 </EmployeeID>



    EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc


    INSERT INTO torder( CustomerID, EmployeeID)

    SELECT CustomerID, EmployeeID FROM Openxml( @hdoc, '/Order', 3) WITH ( CustomerID int,

    EmployeeID int)


    exec sp_xml_removedocument @hdoc




    Like sp_xml_preparedocument i am looking for JSON data........



    Tuesday, December 04, 2007 9:16 AM
  • Nope.... You have to stick with the Middle Layer. Otherwise you can create your own .NET CLR function (like openxml) to parse your JSON data......


    Tuesday, December 04, 2007 2:09 PM
  • Hi,


    Can you guide me how to create own .NET CLR function for Sql Server2005?  Any reference....
    Thursday, December 06, 2007 5:18 AM