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)'')