node - SQLXML sin codificación XML?

xml.value sql server

Question

Estoy usando un sistema genérico para informes que toma datos de una vista de base de datos (SQL Server 2005). En esta vista, tuve que combinar datos de relaciones uno a muchos en una fila y usé la solución descrita por priyanka.sarkar en este hilo: Combine los resultados múltiples en una subconsulta en un solo valor separado por comas . La solución utiliza SQLXML para fusionar los datos (subconsulta):

SELECT STUFF(
( SELECT '', '' + Name
FROM MyTable _in
WHERE _in.ID = _out.ID
FOR XML PATH('''')), -- Output multiple rows as one xml type value,
-- without xml tags
1, 2, '''') -- STUFF: Replace the comma at the beginning with empty string
FROM MyTable _out
GROUP BY ID -- Removes duplicates

Eso funciona perfectamente (ni siquiera es tan pesado en el rendimiento), excepto que ahora mis datos tienen codificación XML (& => & etc.) por SQLXML. Después de todo, no quería datos XML, simplemente usé esto como un truco. debido al sistema genérico, no puedo codificar esto para limpiarlo, por lo que los datos codificados van directamente al informe. No puedo usar procedimientos almacenados con el sistema genérico, por lo que la combinación de CURSOR o COALESCE no es una opción aquí ...

Entonces, lo que busco es una manera en T-SQL que me permita decodificar el XML nuevamente, o incluso mejor: evita que SQLXML lo codifique. Obviamente, podría escribir una función almacenada que haga esto, pero preferiría una manera más segura e integrada ...

Gracias por tu ayuda...

Answer 1

Si especifica el type como una opción for xml , puede usar una consulta XPath para convertir el tipo XML de nuevo a varchar . Con una variable de tabla de ejemplo:

declare @MyTable table (id int, name varchar(50))
insert @MyTable (id, name) select 1, ''Joel & Jeff''
union all select 1, ''<<BIN LADEN>>''
union all select 2, ''&&BUSH&&''

Una posible solución es:

select b.txt.query(''root'').value(''.'', ''varchar(max)'')
from (
select distinct id
from @MyTable
) a
cross apply
(
select CASE ROW_NUMBER() OVER(ORDER BY id) WHEN 1 THEN ''''
ELSE '', '' END + name
from @MyTable
where id = a.id
order by
id
for xml path(''''), root(''root''), type
) b(txt)

Esto imprimirá:

Joel & Jeff, <<BIN LADEN>>
&&BUSH&&

Aquí hay una alternativa sin conversiones XML. Tiene una consulta recursiva, por lo que el rendimiento del kilometraje puede variar. Es del blog de Quassnoi :

;WITH with_stats(id, name, rn, cnt) AS
(
SELECT id, name,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY name),
COUNT(*) OVER (PARTITION BY id)
FROM @MyTable
),
with_concat (id, name, gc, rn, cnt) AS
(
SELECT id, name,
CAST(name AS VARCHAR(MAX)), rn, cnt
FROM with_stats
WHERE rn = 1
UNION ALL
SELECT with_stats.id, with_stats.name,
CAST(with_concat.gc + '', '' + with_stats.name AS VARCHAR(MAX)),
with_stats.rn, with_stats.cnt
FROM with_concat
JOIN with_stats
ON with_stats.id = with_concat.id
AND with_stats.rn = with_concat.rn + 1
)
SELECT id, gc
FROM with_concat
WHERE rn = cnt
OPTION (MAXRECURSION 0)

Answer 2

(
select ...
from t
for xml path(''''), type
).value(''.'', ''nvarchar(max)'')