none
Is it possible to insert JSON data to SQL Server 2005

    Question

  •  

    Hi,

     

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

    Thanks,
    Monday, December 03, 2007 2:23 PM

All replies

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

    Monday, December 03, 2007 2:33 PM
    Moderator
  • 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.

     

    Thanks,

     

    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"?>

    <Order>

    <CustomerID> 10 </CustomerID>

    <EmployeeID> 12 </EmployeeID>

    </Order>'

     

    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