title: Convert SQL Server table data from XML to JSON
body: <p>I have a complex sql with multiple joins involved in it like the below.</p>
  
  <pre><code>select * from 
  Table1 inner join Table2 on t1.id=t2.id
  ...
  ...
  ...
  for xml path
  </code></pre>
  
  <p>I just converted the result set to xml then I am trying to convert the xml to Json format using the following function code.</p>
  
  <pre><code>Create FUNCTION [uf_xmltojson] (@XMLResult XML)
  RETURNS nvarchar(max)
  WITH EXECUTE AS CALLER
  AS
  Begin
  Declare  @JSONVersion NVarchar(max), @Rowcount int
  Select @JSONVersion = '', @rowcount=count(*) from @XMLResult.nodes('/root/*') x(a)
  Select @JSONVersion=@JSONVersion+
  Stuff(
    (Select TheLine from 
      (Select ',
      {'+
        Stuff((Select ',"'+coalesce(b.c.value('local-name(.)', 'NVARCHAR(255)'),'')+'":'+
         --Replace(  -- escape double quotes
          Replace( --escape tab properly within a value
           --Replace( --escape return properly
             --Replace( --linefeed must be escaped
               Replace( --backslash too
                 Replace(coalesce(b.c.value('text()[1]','NVARCHAR(MAX)'),''),--forwardslash
                 '\', '\\'),   
                '/', '\/'),   
           -- CHAR(10),'\n'),   
           --CHAR(13),'\r'),   
         CHAR(09),'\t'), 
        --'"','\"')     
       +''   
       from x.a.nodes('*') b(c) 
       for xml path(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'')+'}'
     from @XMLResult.nodes('/root/*') x(a)
     ) JSON(theLine)
    for xml path(''),TYPE).value('.','NVARCHAR(MAX)' )
  ,1,1,'')
  --if @Rowcount&gt;1 Return '['+@JSONVersion+'
  Return '['+@JSONVersion+'
  ]'
  return @JSONVersion
  end
  </code></pre>
  
  <p>I achieved the expected result but it takes almost 10 to 13 seconds to get the json format result. The time taken to get the xml result is 1 or 2 seconds. I know converting XML to Json takes considerable amount of time.</p>
  
  <p>Can anyone please suggest me any other best way to convert sql server table data to Json.</p>
  
  <p>I am trying to produce results within 1 or 2 secs.</p>