SQLXML без кодировки XML?

Я использую общую систему отчетности, которая берет данные из представления базы данных (SQL Server 2005). В этом представлении мне пришлось объединить данные из отношений «один ко многим» в одну строку и использовать решение, описанное priyanka.sarkar в этой теме: Объединение нескольких результатов в подзапросе в одно значение, разделенное запятыми . Решение использует SQLXML для объединения данных (подзапрос):

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

Это работает отлично (это даже не так тяжело по производительности), за исключением того, что мои данные теперь кодируются в XML (& => & и т. Д.) С помощью SQLXML - в конце концов, мне не нужны данные XML, я просто использовал это как трюк - и потому что общей системы, я не могу обойти это, чтобы очистить его, чтобы закодированные данные попадали прямо в отчет. Я не могу использовать хранимые процедуры с общей системой, поэтому слияние CURSOR или COALESCE-ing здесь не вариант ...

Итак, я ищу способ в T-SQL, который позволяет мне снова декодировать XML, или даже лучше: избегает его кодирования SQLXML. Очевидно, я мог бы написать хранимую функцию, которая делает это, но я бы предпочел встроенный, более безопасный способ ...

Спасибо за вашу помощь...


sql sql-server tsql sqlxml
person Koen    schedule 08.07.2010    source источник

Ответы (2)


arrow_upward
5
arrow_downward

Если вы укажете type в качестве опции для for xml, вы можете использовать запрос XPath для преобразования типа XML обратно в varchar. С примером табличной переменной:

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

Одно из возможных решений:

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)

Это напечатает:

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

Вот альтернатива без преобразования XML. У него есть рекурсивный запрос, поэтому производительность может отличаться. Это из блога 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)
person Andomar    schedule 08.07.2010
comment
Спасибо, я упростил (SELECT ... FOR XML PATH (''), TYPE) .value ('.', 'Nvarchar (max)') - person Koen; 08.07.2010
comment
+1 Меня сбили с толку параметры cross apply и root, поэтому я добавил комментарий @ Koen в качестве ответа. - person dotjoe; 14.01.2012

arrow_upward

arrow_downward

person    schedule
comment
эта директива type сэкономила мне так много времени. - person dance2die; 27.09.2016