I am working on a project that is receiving a complex JSON payload from a mobile app. Each payload contains multiple instances of an object that has its own properties, and then also contains arrays of child objects. i want to pass this whole object to SQL server, but if that is not possible, I'd like to pass in the arrays of child objects. I'm anticipating that SQL Server can process the data faster than ColdFusion can, and that reducing the number of calls to the database will improve performance.
I started here: http://www.codeproject.com/Answers/558230/How-to-pass-collection-values-to-sql-server-2008-S#answer1 and learned that I could push the whole thing in as XML, but I wonder about the processing time of converting the JSON to xml. I also learned that there is something called table valued parameters ( https://msdn.microsoft.com/en-us/library/bb510489.aspx , https://msdn.microsoft.com/en-us/library/dd776382.aspx ) and from that bulk INSERTs.
My team is afraid of the xml solution but I think it hold a lot of promise. If SQL Server eventually can handle JSON that would be perfect.
For now (within a loop) I am inserting the master record and getting back the new ID, then adding the new Id to the child array elements. I then accumulate all the data from child arrays for a given type and insert them in one bulk insert.
I'd love to try the xml solution but I'm not getting support from my team on that