Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 For xml path без энкодинга спец символов  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 3878
Господа, был какой-то способ записать FOR XML PATH так, чтобы он не энкодил спецсимволы. SQL 2016 пока не 2017 (STRING_AGG еще нет). Или как-то по другому делалось

use master;
GO 

DECLARE @S SYSNAME = 'dbo'
DECLARE @T SYSNAME = 'spt_monitor'
	DECLARE @SCRIPT1 NVARCHAR(MAX) = 
	   CONCAT('SELECT ', STUFF(CAST((     
		   SELECT [text()] = CONCAT(N', [', c.Column_Name, ']', N'
')       
		   FROM INFORMATION_SCHEMA.COLUMNS c     
		   WHERE TABLE_SCHEMA = @S AND TABLE_NAME = @T      
		   ORDER BY c.ORDINAL_POSITION     
		   FOR XML PATH(''), TYPE) AS VARCHAR(MAX)), 1, 2, ''),
		   ' FROM ['+@S+'].['+@T+'] d WITH (NOLOCK)'
		);

		
print(@SCRIPT1)
11 апр 19, 12:44    [21859251]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
invm
Member

Откуда: Москва
Сообщений: 8489
DECLARE @S SYSNAME = 'dbo'
DECLARE @T SYSNAME = 'spt_monitor'
	DECLARE @SCRIPT1 NVARCHAR(MAX) = 
	   CONCAT('SELECT ', STUFF((     
		   SELECT [text()] = CONCAT(N', [', c.Column_Name, ']', N'
')       
		   FROM INFORMATION_SCHEMA.COLUMNS c     
		   WHERE TABLE_SCHEMA = @S AND TABLE_NAME = @T      
		   ORDER BY c.ORDINAL_POSITION     
		   FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, ''),
		   ' FROM ['+@S+'].['+@T+'] d WITH (NOLOCK)'
		);
11 апр 19, 12:52    [21859274]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
.Евгений
Member

Откуда:
Сообщений: 465
a_voronin,

я делаю список столбцов так:
declare @ins nvarchar(max) = N''
...
select @ins = @ins + '[' + p.COLUMN_NAME + '], ' 
from INFORMATION_SCHEMA.COLUMNS p
...
set @ins = left(@ins, len(@ins) - 1) --Вопрос отказа от обрезки финальной запятой посредством сортировки - дискутабельный.


Давно отказался от склейки XML.
11 апр 19, 13:32    [21859364]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6356
CLS функция для агрегирования колонки в строку.

+
/*
Автор: Колосов В.В. (создана по материалам интернет)
Дата создания: 26.07.2018
Назначение: агрегатная функция для сбора колонки в строку с разделителями
Пример использования:

create table #tt1(id int, nn varchar(10))
insert #tt1 values (1, 'qwe'),(1,'qwe'),(1,'dsa'),(2,'zxc'),(3,'tre'),(3,'tre1')
select id, dbo.ConcatColumn(nn,' + ') from (select distinct * from #tt1) tt1 group by id
 */
using System;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined, //use clr serialization to serialize the intermediate result  
    IsInvariantToNulls = true, //optimizer property  
    IsInvariantToDuplicates = false, //optimizer property  
    IsInvariantToOrder = false, //optimizer property  
    MaxByteSize = 8000) //maximum size in bytes of persisted value  
]
public class ConcatColumn : IBinarySerialize
{
    /// <summary>  
    /// The variable that holds the intermediate result of the concatenation  
    /// </summary>  
    private StringBuilder intermediateResult;
    private string _delimiter;

    /// <summary>  
    /// Initialize the internal data structures  
    /// </summary>  
    public void Init()
    {
        _delimiter = string.Empty;
        intermediateResult = new StringBuilder();
    }

    /// <summary>  
    /// Accumulate the next value, not if the value is null  
    /// </summary>  
    /// <param name="Value"></param>  
    public void Accumulate(SqlString Value, SqlString Delimiter)
    {
        if (Value.IsNull)
        {
            return;
        }

        intermediateResult.Append(Value.Value);

        if (!Delimiter.IsNull & Delimiter.Value.Length > 0)
        {
            _delimiter = Delimiter.Value; /// save for Merge
            if (intermediateResult.Length > 0) intermediateResult.Append(Delimiter.Value);
        }
    }

    /// <summary>  
    /// Merge the partially computed aggregate with this aggregate.  
    /// </summary>  
    /// <param name="other"></param>  
    public void Merge(ConcatColumn other)
    {
        if (intermediateResult.Length > 0 & other.intermediateResult.Length > 0)
            intermediateResult.Append(_delimiter);

        intermediateResult.Append(other.intermediateResult);
    }

    /// <summary>  
    /// Called at the end of aggregation, to return the results of the aggregation.  
    /// </summary>  
    /// <returns></returns>  
    public SqlString Terminate()
    {
        string output = string.Empty;

        //delete the trailing comma, if any  
        if (intermediateResult != null
            && intermediateResult.Length > 0)
        {
            output = intermediateResult.ToString(0, intermediateResult.Length - _delimiter.Length);
        }

        return new SqlString(output);
    }

    public void Read(BinaryReader r)
    {
        _delimiter = r.ReadString();
        intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(_delimiter);
        w.Write(this.intermediateResult.ToString());
    }
}
11 апр 19, 13:48    [21859402]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6356
CLR, разумеется.
11 апр 19, 13:48    [21859405]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
uaggster
Member

Откуда:
Сообщений: 554
.Евгений
a_voronin,

я делаю список столбцов так:
declare @ins nvarchar(max) = N''
...
select @ins = @ins + '[' + p.COLUMN_NAME + '], ' 
from INFORMATION_SCHEMA.COLUMNS p
...
set @ins = left(@ins, len(@ins) - 1) --Вопрос отказа от обрезки финальной запятой посредством сортировки - дискутабельный.


Давно отказался от склейки XML.

1. Это эксплуатация недокументированной фичи, которую не объявили deprecated только потому, что существует огромное количество кода, которое ее использует. Она нестандартна ниразу.
2. Используется лишняя переменная. Выражение нельзя непосредственно встроить в запрос.
3. '[' + p.COLUMN_NAME + '], ' - нужно использовать QUOTENAME()
4. set @ins = left(@ins, len(@ins) - 1) - Нужно складывать с ведущей запятой, а потом результат обрезать по первому символу с помощью STUFF([RESLT], 1, 1, '').

Не учите плохому, короче говоря.
11 апр 19, 13:56    [21859424]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
.Евгений
Member

Откуда:
Сообщений: 465
uaggster,
скажу короче:

1. sp_MSforeachtable тоже плоха, угу, конечно.
2. О ужас. Переменные надо экономить. Лишняя переменная - это кусок хлеба, вырванный из ртов голодных детей Африки.
3 и 4. Не нужно, а можно. Не спорю, можно. Разницы в эффективности нет, внешне выглядит слегка симпатичнее, на версиях ранее 2008 не работает.

В общем, успокойтесь и не нагнетайте. Все равно склейка посредством XML - либо большое, либо маленькое, но в любом случае зло.
11 апр 19, 14:46    [21859525]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
.Евгений
Member

Откуда:
Сообщений: 465
Владислав Колосов,

Напомните, пожалуйста: StringBuilder в .Net - потокобезопасный?
11 апр 19, 15:06    [21859575]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
invm
Member

Откуда: Москва
Сообщений: 8489
uaggster
1. Это эксплуатация недокументированной фичи, которую не объявили deprecated только потому, что существует огромное количество кода, которое ее использует. Она нестандартна ниразу.
2. Используется лишняя переменная. Выражение нельзя непосредственно встроить в запрос.
3. '[' + p.COLUMN_NAME + '], ' - нужно использовать QUOTENAME()
4. set @ins = left(@ins, len(@ins) - 1) - Нужно складывать с ведущей запятой, а потом результат обрезать по первому символу с помощью STUFF([RESLT], 1, 1, '').
Это все несущественно.
Существенно то, что в один прекрасный момент вместо списка можно получить только одно значение.
Но, как правило, сторонников подобного кода это не останавливает.
11 апр 19, 15:08    [21859577]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
.Евгений
Member

Откуда:
Сообщений: 465
.Евгений
Владислав Колосов,

Напомните, пожалуйста: StringBuilder в .Net - потокобезопасный?

А, это неважно.

SqlUserDefinedAggregateAttribute.IsInvariantToOrder Property
Remarks
Reserved for future use. This property is not currently used by the query processor: order is currently not guaranteed.

Что бы это значило?
11 апр 19, 15:22    [21859598]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
iap
Member

Откуда: Москва
Сообщений: 46656
.Евгений
3 и 4. Не нужно, а можно.
3. - совершенно необходимо (и совершенно необременительно).
Представьте, например, что имя содержит квадратные скобки.
11 апр 19, 15:23    [21859600]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
.Евгений
Member

Откуда:
Сообщений: 465
iap,

'[' + COLUMN_NAME + ']' и QUOTENAME(COLUMN_NAME)
дают одинаковый результат.
11 апр 19, 15:29    [21859609]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
uaggster
Member

Откуда:
Сообщений: 554
.Евгений
iap,

'[' + COLUMN_NAME + ']' и QUOTENAME(COLUMN_NAME)
дают одинаковый результат.

Наслаждайтесь:

Declare @s nvarchar(4000)

If OBJECT_ID('tempdb..#t') is not null 
	drop table #t

Create table #t ("[N]" int)

Select @s='Select ' + Quotename([name]) + 'from #t'
	from tempdb.sys.all_columns a
Where a.object_id = OBJECT_ID('tempdb..#t')

Exec (@s)

Select @s='Select [' +[name] + '] from #t'
	from tempdb.sys.all_columns a
Where a.object_id = OBJECT_ID('tempdb..#t')

Exec (@s)
11 апр 19, 16:31    [21859693]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
.Евгений
Member

Откуда:
Сообщений: 465
uaggster,

Согласен, что необходимость удвоения правой квадратной скобки я упустил. Спасибо, в будущем я это учту.
11 апр 19, 16:41    [21859702]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6356
.Евгений
Владислав Колосов,

Напомните, пожалуйста: StringBuilder в .Net - потокобезопасный?


Сборка уровня безопасности SAFE.
11 апр 19, 18:17    [21859810]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 4670
Владислав Колосов
.Евгений
Владислав Колосов,

Напомните, пожалуйста: StringBuilder в .Net - потокобезопасный?


Сборка уровня безопасности SAFE.

Уровень безопасности сборки в MSSQL, и thread safety в C# - это абсолютно разные и неперсекающиеся вещи.
StringBuilder вообще и ни разу не потокобезопасен: https://stackoverflow.com/questions/8831385/is-nets-stringbuilder-thread-safe
В исходниках версии FW 4.7.2 фигурирует использование неких методов типа ThreadSafeCopy, но ни синхронизации, ни проверки на принадлежность к текущему thread нет.
12 апр 19, 06:09    [21860068]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6356
По-моему, это не имеет значения, если только не объявлены statiс поля.
12 апр 19, 12:19    [21860377]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 4670
Владислав Колосов
По-моему, это не имеет значения

При работе в многопоточной среде с одним экземпляром еще как имеет. Различные мониторы, мьютексы, семафоры, и спинлоки придуманы не от нечего делать.
12 апр 19, 14:30    [21860581]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6356
Сон Веры Павловны,

да, для решения проблемы совместного использования ресурсов. А в агрегате CLR откуда совместное использование? То есть существует риск, что упомянутый в контексте функции стрингбилдер выбросит исключение или подхватит чужую строку? Хотелось бы знать.
12 апр 19, 14:46    [21860596]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 4670
Владислав Колосов
Сон Веры Павловны,

да, для решения проблемы совместного использования ресурсов. А в агрегате CLR откуда совместное использование? То есть существует риск, что упомянутый в контексте функции стрингбилдер выбросит исключение или подхватит чужую строку? Хотелось бы знать.

Изначально был задан вопрос:
.Евгений
Владислав Колосов,

Напомните, пожалуйста: StringBuilder в .Net - потокобезопасный?

Максимально общий ответ: нет. Поскольку в агрегате CLR нет совместного использования, то нет и кросспоточного взаимодействия, следовательно, изначальный вопрос был общего характера, т.к. в контексте использования в CLR-сборках MSSQL он (вопрос) смысла не имеет.
12 апр 19, 18:29    [21860797]     Ответить | Цитировать Сообщить модератору
 Re: For xml path без энкодинга спец символов  [new]
msLex
Member

Откуда:
Сообщений: 5942
Сон Веры Павловны
Максимально общий ответ: нет. Поскольку в агрегате CLR нет совместного использования, то нет и кросспоточного взаимодействия, следовательно, изначальный вопрос был общего характера, т.к. в контексте использования в CLR-сборках MSSQL он (вопрос) смысла не имеет.


Ну вообще CLR-сборки в MSSQL не ограничиваются агрегатами, и вполне могут быть содержать кросспоточное взаимодействие.
12 апр 19, 18:39    [21860807]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить