Is Possible convert a table to JSON in previous versions of SQLSERVER 2016?

Question

My question is to know if it is possible from T-SQL to create a JSON file in previous versions of 2016, since from this version it already brings the function implicitly, but in my case I am using SQL Server 2014.

Is there a way to parse it from XML to JSON or create a function?

Here's an example of an XML File with objects inside him processing by SP.

ALTER PROCEDURE [dbo].[sp_Socio_IntegracionPOST] @Codigo nvarchar(20), @Query int
AS
BEGIN

    DECLARE @socio xml,@direccion xml,@contacto xml
  SET NOCOUNT ON;

  --Este query consulta el socio de negocio
  IF (@Query = 1)
  BEGIN

  SET @direccion = (SELECT 
      SUCURSAL AS AddressName,
      DIRECCION AS Street,
      mun.NombreMunicipio AS 'Block',
      FORMAT(CONVERT(int, codciudad), '00000') AS ZipCode,
      mun.NombreMunicipio AS City,
      'CO' AS County,
      'CO' AS Country,
      dep.IdSAP AS 'State',
      NULL AS AddressType,
      RTRIM(dir.CODIGO) AS BPCode,
      CONVERT(date, GETDATE()) AS CreateDate,
      CONVERT(char(8), GETDATE(), 108) AS CreateTime,
      FORMAT(CONVERT(int, codciudad), '00000') AS U_HBT_MunMed,
      CASE
        WHEN DEFECTO_FACTURA = 1 THEN 'Y'
        ELSE 'N'
      END AS U_HBT_DirMM
    FROM tblmvto_direccion dir
    INNER JOIN tblMunicipios mun
      ON dir.CODCIUDAD = mun.CodMunc
    INNER JOIN tblDepartamentos dep
      ON dir.DEPARTAMENTO = dep.NombreDepartamento
    WHERE dir.CODIGO = @Codigo
    FOR XML PATH ('Row'))

    SET @contacto = (SELECT 
      RTRIM(contac.CODIGO) AS CardCode,
      CONCAT(RTRIM(contac.PrimerNombre), RTRIM(' ' + contac.SegundoNombre), RTRIM(' ' + contac.PrimerApellido), RTRIM(' ' + contac.SegundoApellido)) AS 'Name',
      contac.Telefono AS Phone1,
      contac.TelefonoMovil as MobilePhone,
      contac.CorreoContacto AS E_Mail,
      'E' AS Gender,
      'Y' AS Active,
      contac.PrimerNombre AS FirstName,
      contac.SegundoNombre AS MiddleName,
      CONCAT(RTRIM(contac.PrimerApellido), RTRIM(' ' + contac.SegundoApellido)) AS LastName,
      CONVERT(date, GETDATE()) AS CreateDate,
      CONVERT(char(8), GETDATE(), 108) AS CreateTime
    FROM tblmvto_contactos contac
    WHERE contac.CODIGO = @Codigo
    FOR XML PATH('Row'))

    SET @contacto = CONVERT(xml, '<ContactEmployees>'+CAST(@contacto AS nvarchar(MAX))+'</ContactEmployees>')
    SET @direccion = CONVERT(xml, '<BPAddresses>'+CAST(@direccion AS nvarchar(MAX))+'</BPAddresses>')


      SET @socio = (SELECT top 1
      RTRIM(sn.codigo) AS CardCode,
      RTRIM(sn.nit) as FederalTaxID,
      CONCAT(RTRIM(sn.nombre), RTRIM(' ' + sn.nombre1), RTRIM(' ' + sn.apellido), RTRIM(' ' + sn.apellido1)) AS CardName,
      [dbo].[FN_Validar_CardType_SocioNegocio](sn.escliente, sn.esprovee, sn.esempleado) AS CardType,
      CASE
        WHEN sn.GranContribuyente = 1 THEN 'S'
        ELSE 'N'
      END AS U_GraCont,
      RTRIM(sn.celular) AS Phone1,
      CONCAT(rtrim(sn.nombre), RTRIM(' ' + sn.nombre1)) AS U_HBT_Nombres,
      RTRIM(sn.apellido) AS U_HBT_Apellido1,
      RTRIM(sn.apellido1) AS U_HBT_Apellido2,
      sn.Nacionalidad AS U_HBT_Nacional,
      CASE
        WHEN sn.codtipoper = 'O-SIN' THEN 1
        ELSE CONVERT(int, sn.codtipoper)
      END AS U_HBT_TipEnt,
      FORMAT(CONVERT(int, sn.codciudad), '00000') AS U_HBT_MunMed,
      CASE
        WHEN ti.IdSAP = 'O-SIN' THEN '13'
        ELSE ti.IdSAP
      END AS U_HBT_TipDoc,
      CASE
        WHEN tc.IdSAP = 'O-SIN' OR
          tc.IdSAP IS NULL THEN 'RS'
        ELSE tc.IdSAP
      END AS U_HBT_RegTrib,
      CASE
        WHEN sn.Autorretenedor = 0 THEN 'N'
        ELSE 'Y'
      END AS U_HBT_AutRet,
      CASE
        WHEN rf.IdSAP = 'O-SIN' THEN 'R-99-PN'
        ELSE rf.IdSAP
      END AS U_HBT_RegFis,
      mp.IdSAP AS U_HBT_MedPag
    FROM tblsocio_negocios sn
    INNER JOIN tbltipo_identificacion ti
      ON sn.codtipo = ti.codtipo
    INNER JOIN tbltipo_contribuyente tc
      ON sn.codcontribuye = tc.codcontribuye
    INNER JOIN tblregimenfiscal rf
      ON sn.RegimenFiscal = rf.Id
    INNER JOIN tblMedioPago mp
      ON sn.MedioPago = mp.Id
    WHERE sn.codigo = @Codigo
    FOR XML PATH ('BusinessPartner'))
    SELECT CAST( CAST(@socio AS nvarchar(MAX)) + '(Objeto)' +  CAST(@contacto AS nvarchar(MAX)) + '(Objeto)' + CAST(@direccion AS nvarchar(MAX)) as nvarchar(max))
  END
END

Answer 1

You can but it will take a little work. You can use C Sharp to create a CLR function that can be passed an nvarchar type and return an nvarchar type. There is no JSON type so it would have to be stored, typically, as a nvarchar(max).

On the C Sharp side it is not hard to do - look at serialization methods. Many users are hesitant to use the CLR but its a short learning curve and can be very useful. Please see the comments below for limitations of CLR.