Navegando por distintos foros me he encontrado esta maravilla, un script de SQL server que a partir de una consulta nos devuelve el resultado en formato JSON.

-- la consulta 
select *
into #json_base
from tabla;

-- a partir de aqui no tocar
declare @columns table (
    id int identity primary key,
    name sysname,
    datatype sysname,
    is_number bit,
    is_date bit);

insert into @columns(name, datatype, is_number, is_date)
select columns.name, types.name,
       case when number_types.name is not NULL then 1 else 0
       end as is_number,
       case when date_types.name is not NULL then 1 else 0
       end as is_date
from tempdb.sys.columns
join tempdb.sys.types
    on (columns.system_type_id = types.system_type_id)
left join (values ('int'), ('real'), ('numeric'), 
                  ('decimal'), ('bigint'), ('tinyint')) as number_types(name)
    on (types.name = number_types.name)
left join (values ('date'), ('datetime'), ('datetime2'),
                  ('smalldatetime'), ('time'), ('datetimeoffset')) as date_types(name)
    on (types.name = date_types.name)
where object_id = OBJECT_ID('tempdb..#json_base');

declare @field_list varchar(max) = STUFF((
    select '+'',''+' + QUOTENAME(QUOTENAME(name, '"') + ':', '''')
           + '+' + case when is_number = 1
                        then 'COALESCE(LTRIM('
                                + QUOTENAME(name) + '),''null'')'
                        when is_date = 1
                        then 'COALESCE(QUOTENAME(LTRIM(convert(varchar(max), '
                                + QUOTENAME(name) + ', 126)),''"''),''null'')'
                        else 'COALESCE(QUOTENAME('
                                + QUOTENAME(name) + ',''"''),''null'')'
                   end
    from @columns
    for xml path('')),
    1, 5, '');

create table #json_result (
    id int identity primary key,
    line varchar(max));

declare @sql varchar(max) = REPLACE(
    'insert into #json_result '
  + 'select '',{''+{f}+''}'' '
  + 'from #json_base', '{f}', @field_list);

exec(@sql);

update #json_result
set line = STUFF(line, 1, 1, '')
where id = 1;

select '['
UNION ALL
select line
from #json_result
UNION ALL
select ']';

drop table #json_base;
drop table #json_result;

Por cierto, lo he encontrado aquí

Venga va, ya que estamos, seguimos con SQL server y las transacciones cuando hay un try catch de por medio.

Cuando hacemos un procedimiento almacenado y tenemos que insertar varios registros que dependen entre si, lo normal es usar transacciones para poder hacer un roll back en caso de que se produzca algún error. Antiguamente los programaba asi:

BEGIN TRANSACTION

/* código del procedimiento*/
IF (@@ERROR <> 0) GOTO ERROR

/* código del procedimiento*/

COMMIT TRAN
RETURN 0

Y al final del procedimiento nuestra anticuada rutina

ERROR:
ROLLBACK TRANSACTION
RETURN 1

Esto sigue funcionando pero es del pleistoceno y es mejor hacerlo con un try catch, que así nos evita tener que estar preguntando cada dos por tres si se ha producido un error:

 
BEGIN TRANSACTION;
BEGIN TRY

/* código del procedimiento*/


END TRY
BEGIN CATCH
    
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

Bueno, parece que el chuletero lo tengo un poco abandonado pero es porque ando un pelín saturado de curro. Hoy toca pelearse un poco con SQL y la forma de parsear los XML.

En ocasiones podemos necesitar pasar una lista de valores a un procedimiento para insertarlos en base de datos. Si el número de esos valores puede ser dinámico la mejor forma de pasarlos es mediante un XML. Supongamos que tenemos por ejemplo el siguiente XML:

declare @xml xml;
set @xml = '
	<productos>
		<codigo>1</codigo>
		<codigo>2</codigo>
		<codigo>3</codigo>
	</productos>
';

Sin queremos hacer una consulta sobre este XML sería tan sencillo como:

SELECT  ParamValues.ID.value('.','int') as id 
FROM @xml.nodes('/productos/codigo') as ParamValues(ID) 

Pero qué pasa si queremos complicarlo un poco y no es una lista de valores sino un XML más complejo, como por ejemplo:

declare @xml xml
set @xml = '
	<home>		
		<posicion>
			<imagen>aa</imagen>
			<hover>bbb</hover>
			<url>cccc</url>
		</posicion>
		<posicion>
			<imagen>ddd</imagen>
			<hover>eee</hover>
			<url>ffff</url>
		</posicion>
	</home>
';

Pues la forma de hacerlo sería así:

SELECT
   pos.value('imagen[1]', 'Varchar(250)') as 'imagen',
   pos.value('hover[1]', 'Varchar(250)') as 'hover',
   pos.value('url[1]', 'varchar(250)') as 'url'
FROM
   @xml.nodes('/home/posicion') as posicion(pos)