returning name value json pairs

[ccmcbride]

is there a way to get name value pairs in json format from the sdac table/query component?

currently, if I return tDataset from a function, the result look similar to this:

Code: Select all

{"result":[{"table":[["RecUID",18,0,0,0,8,0,0,false,false,0,false,false],["UID",1,1,0,20,21,0,0,false,false,0,false,false],["OrderUID",1,2,0,20,21,0,0,false,false,0,false,false],["CustUID",1,3,0,20,21,0,0,false,false,0,false,false]],
"RecUID":[1750,1752,2264,2739,2773,2778,2782,2836,2877,2883,2885],
"UID":["ot020111131510712654","ot020111131762236427","ot041711104288799857","ot071911162081920522","ot072711161455123156","ot080311133860552998","ot080311134357214373","ot081111105464965044","ot081511131928125876","ot081611120071683148","ot081611120266301526"],"OrderUID":["CL201101191013960738","CL201101261713425781","CL201104151238906840","IO071411120091634644","IO072611141842077312","CL201107221203113009","CL201107221157940519","IO080511090441052867","IO080511090441052867","IO071511115342577294","CL201107221140822726"],"CustUID":["CM022610154310062679","CM022610154354798679","CM022610154350524724","CM022610154326676199","CM122310154142718036","CM022610154477292553","CM071911180417935293","CM022610154295558961","CM022610154295558961","CM022610154359115446","CM022610154359115446"],

my coworker says she needs it to be in name value pairs.
is there a way I can get what she needs without having to iterate the entire record set and creating it manually?

[AndreyZ]

Hello,

SQL Server does not have native support for JSON. You can vote for this feature at http://connect.microsoft.com/SQLServer/ ... m-openjson . Also, you will find there one workaround that allows working with JSON.
Another possible solution is to use the FOR XML clause. Here is a code example:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  MSQuery1.SQL.Text := 'SELECT * FROM TABLENAME FOR XML PATH, TYPE';
  MSQuery1.Open;
  TMSXMLField(MSQuery1.Fields[0]).SaveToFile('D:\my.xml');
end;

For more information about the FOR XML and TYPE clauses, please read the following articles:
http://msdn.microsoft.com/en-us/library/ms178107.aspx
http://msdn.microsoft.com/en-us/library/ms190025.aspx