FOR XML PATH (''): экранирование "специальных" символов. Sql пример for xml path

sql - SQL Server FOR XML PATH динамически меняет теги

Я не уверен в состоянии определить, должен ли тег быть "buy_new" или "sell_new", но это может сработать для вас:

SELECT ( SELECT Investment1 as Investment, EventDate1 as Date, Quantity1 as Quantity FROM #temptable e1 where e1.temp_id = e.temp_id AND (SOME CONDITION FOR 'Buy_New') FOR XML PATH('Buy_New'),TYPE ) , ( SELECT Investment1 as Investment, EventDate1 as Date, Quantity1 as Quantity FROM #temptable e1 where e1.temp_id = e.temp_id AND (SOME CONDITION FOR 'Sell_New') FOR XML PATH('Sell_New'),TYPE ) FROM #temptable e FOR XML PATH(''), ROOT('Loader') GO

РЕДАКТИРОВАТЬ:

По вашему ответу я понимаю, что вам нужно что-то более динамичное. Это может быть не элегантное решение, которое вы ищете, но оно может выполнить свою работу без необходимости жесткого кодирования каждого элемента:

Во-первых, создайте таблицу для хранения имен требуемых динамических элементов:

create table elements (id int, name nvarchar(20)) insert into elements (id, name) values (1, 'sell_new') insert into elements (id, name) values (2, 'buy_new') insert into elements (id, name) values (3, 'other_new')

Затем идет обходной путь:

SELECT cast('<' + e.name +'>' + cast( ( SELECT Investment AS 'Investment', EventDate as 'Date', Quantity AS 'Quantity' FROM temptable t1 WHERE t1.investment = t.investment FOR XML PATH(''),TYPE ) as varchar(max)) + '</' + e.name +'>' as xml) from temptable t JOIN elements e ON e.id = t.RecordType order by investment for xml path(''), root('Loader')

Результат:

qaru.site

sql - ДЛЯ XML PATH на сервере SQL и [text()]

Другие текущие ответы не объясняют многое о том, откуда это происходит, или просто предлагают ссылки на плохо отформатированные сайты и на самом деле не отвечают на вопрос.

Во многих ответах по сети для группировки строк есть ответы на вставку для копирования без большого объяснения того, что происходит. Я хотел бы лучше ответить на этот вопрос, потому что мне было интересно то же самое, а также дать понять, что на самом деле происходит в целом.

Короче говоря, это синтаксис, помогающий преобразовать вывод XML при использовании FOR XML PATH который использует имена столбцов (или псевдонимы) для структурирования вывода. Если вы назовете text() столбца text() данные будут представлены в виде текста в корневом теге.

<row> My record data <row>

В примерах, которые вы видите в Интернете, как группировать строки и конкатрировать , может быть не очевидно (за исключением того факта, что ваш запрос имеет немного for xml части for xml), что вы на самом деле создаете XML файл с определенной структурой (или, скорее,, отсутствие структуры) с помощью FOR XML PATH (''). ('') Удаляет корневые теги xml и просто выплескивает данные.

Как обычно, AS действует для обозначения или переименования псевдонима столбца. В этом примере вы сглаживаете этот столбец как [text()]. [] Являются просто разделителями столбцов SQL Server, часто ненужными, за исключением сегодняшнего дня, поскольку наше имя столбца имеет () s. Это оставляет нас с text() для нашего имени столбца.

Когда вы используете FOR XML PATH вы выводите XML файл и можете управлять структурой с именами столбцов. Подробный список параметров можно найти здесь: https://msdn.microsoft.com/en-us/library/ms189885.aspx

Пример включает начало имени столбца с помощью знака @, например:

SELECT color as '@color', name FROM #favorite_colors FOR XML PATH

Это переместило бы данные столбца в атрибут текущей строки xml, в отличие от элемента внутри него. Вы в конечном итоге

Итак, вернемся к [text()]. Это на самом деле указывает тест XPath Node. В контексте MS Sql Server вы можете узнать об этом обозначении здесь. В основном это помогает определить тип элемента, к которому мы добавляем эти данные, например нормальный узел (по умолчанию), комментарий xml или в этом примере - некоторый текст в теге.

SELECT color as [@color] ,'Some info about ' + name AS [text()] ,name + ' likes ' + color AS [comment()] ,name ,name + ' has some ' + color + ' things' AS [info/text()] FROM #favorite_colors FOR XML PATH

Обратите внимание, что мы используем несколько имен в именах столбцов:

@color: атрибут тега
text(): некоторый текст для этого корневого тега
comment(): комментарий xml
info/text(): некоторый текст в определенном теге xml, <info>

Результат выглядит следующим образом:

<row color="red"> Some info about tim  <name>tim</name> <info>tim has some red things</info> </row> <row color="blue"> Some info about that guy  <name>that guy</name> <info>that guy has some blue things</info> </row>

Итак, с решениями, которые мы видим для группировки строк вместе с использованием FOR XML PATH, есть два ключевых компонента.

AS [text()]: записывает данные как текст, вместо того, чтобы обернуть его в тег
FOR XML PATH (''): переименовывает корневой тег в '', или, скорее, полностью удаляет его

Это дает нам результат "XML" (воздушный кавычек), который по существу является просто строкой.

SELECT name + ', ' AS [text()] -- no 'name' tags FROM #favorite_colors FOR XML PATH ('') -- no root tag

возвращается

tim, that guy,

Оттуда это просто вопрос присоединения этих данных к более крупному набору данных, из которого он пришел.

qaru.site

добейтесь большего от SQL Server

Автор:

Простой пример: пакетная проверка пользовательского ввода

Работа с XML-данными

Знакомимся с OPENXML

Освобождение памяти

SQL-синтаксис для возврата XML-данных

Как выглядит содержимое раздела EXPLICIT?

Миссия выполнена

XML-программирование в SQL Server 2005

Переработанный пример

Новая версия процедуры

Возврат результатов

SQL Server 2000 - первая версия флагманской СУБД Microsoft, в которой поддерживается создание и манипулирование XML-данными с помощью T-SQL.

В этой статье я вкратце рассмотрю, как манипулировать XML-данными, доступными в SQL Server 2000. В основном речь пойдет о внутрисистемном взаимодействии между хранимыми процедурами SQL Server 2000, написанными на T-SQL, и Web-сервисами или компонентами пользовательского интерфейса.

Я расскажу, как обращаться к данным, которые содержатся в XML-строках, передаваемых хранимым процедурам, и как выводить XML-данные. На рис. 1 показана высокоуровневая архитектура этих операций. Я познакомлю вас со следующими синтаксическими конструкциями T-SQL, связанными с XML:

sp_xml_preparedocument;
OPENXML;
sp_xml_removedocument;
FOR XML EXPLICIT.

Рис. 1. Применение на практике некоторых новшеств в синтаксисе T-SQL

Простой пример: пакетная проверка пользовательского ввода

В SQL Server 2000 нет встроенного типа для хранения XML-данных. Для хранения XML-строк в переменных или полях таблиц применяют тип (n)(var)char или (n)text. Почти во всех случаях, встречающихся при разработке, такие переменные оказываются входными параметрами хранимых процедур; поэтому так будет и в моем примере. (Следует заметить, что передача кода T-SQL между уровнями всегда рискованна с точки зрения безопасности и, как правило, оказывается не лучшим вариантом с точки зрения производительности; это достаточно веские причины, чтобы никогда так не поступать.)

Рассмотрим хранимую процедуру sptxValidateLookupData, разработанную моей группой несколько лет назад. Она предназначена для проверки на допустимость данных, вводимых пользователем в рабочий процесс определенной системы. Чтобы свести к минимуму количество обменов с базой данных, разработчики системы решили накапливать весь пользовательский ввод и передавать информацию, которую требуется проверять на допустимость, в базу данных в одном XML-документе. Хранимая процедура выполняет проверку на допустимость и возвращает вызывающему процессу результаты, также помещая их в один XML-документ. Процедура sptxValidateLookupData обслуживает несколько разных рабочих процессов, поэтому в одном и том же пакете могут запрашиваться проверки "существует" или "не существует" для любого значения (datum) или области (domain). В следующем фрагменте кода показаны типичные входные XML-данные этой хранимой процедуры:

В корневом узле <ValidateData> содержатся подузлы <Validate>, описывающие, какую проверку нужно выполнить. Type задает область, для которой выполняется проверка, Name - проверяемый атрибут, а Test - тип проверки (существует ли значение Value в поле, указанном атрибутом Name, в области Type). Заметьте: этот фрагмент XML-данных описывает четыре операции проверки на допустимость для трех областей, но версия хранимой процедуры, которая используется в настоящее время, поддерживает 17 областей и любое число проверок. Таким образом, у нас есть компонент проверки на допустимость, обеспечивающий многократное использование кода, отличную расширяемость и высокую производительность, удобный в сопровождении, а главное - простой до неприличия!

Результаты также возвращаются в XML-формате. Если все проверки для данного потока возвратили TRUE (т. е. если истинны утверждения, задаваемые в узлах Test), возвращается пустой тэг <Results/>. Однако, если какие-либо проверки потерпели неудачу, возвращается список ошибок:

Работа с XML-данными

В SQL Server 2000 XML-данные передаются процессу T-SQL как простые строки. Чтобы с ними можно было работать как с реляционными или иерархическими данными, необходимо дать SQL Server "понять", что это XML-данные, - "подготовить" их. Для этого вызывается системная хранимая процедура sp_xml_preparedocument. Давайте посмотрим начало хранимой процедуры sptxValidateLookupData (листинг 1). Системная хранимая процедура sp_xml_preparedocument считывает XML-текст, передаваемый как входной параметр (параметр @XMLString в вызове в листинге 1), затем передает текст анализатору MSXML и формирует проанализированный документ, готовый к обработке функцией OPENXML, возвращающей набор записей.

Этот документ является иерархическим представлением различных узлов XML-документа (элементов, атрибутов, текста, комментариев и т.д.). Оно хранится в кэше сервера, максимальный размер которого равен одной восьмой от общего объема памяти сервера. Поэтому необходимо аккуратно работать с этим внутренним представлением в интенсивно используемых системах или в средах с ограниченным объемом памяти.

Листинг 1. Хранимая процедура sptxValidateLookupData

create procedure sptxValidateLookupData @XMLString ntext as set nocount on declare @idoc int, @Name nvarchar(30), @Value nvarchar(300), @Test nvarchar(30), @Result int, @Type nvarchar(30), @TestResult int -- Готовим входные XML-данные к выборке с помощью OPENXML exec sp_xml_preparedocument @idoc OUTPUT, @XMLString

Процедура sp_xml_preparedocument возвращает описатель (значение @idoc в вызове в листинге 1), через который можно обращаться к созданному внутреннему представлению XML-документа. Это значение используется в качестве параметра функции OPENXML, возвращающей набор записей для оператора SELECT. OPENXML - "мост" к подготовленному XML-документу, и ее можно указывать в операторе SELECT аналогично таблице или представлению (листинг 2).

Я создал и заполнил табличную переменную, чтобы не выполнять многократные вызовы OPENXML, которые были бы менее эффективны, чем многократное обращение к табличной переменной. Лучше копировать содержимое XML-документов в табличные переменные, чтобы избавиться от многократного неэффективного доступа к XML-данным (хотя, если вы собираетесь обратиться к XML-данным только один раз, просто выполните запрос с OPENXML без предварительного создания табличной переменной). Второй оператор на рис. 3 копирует входные XML-данные в табличную переменную @tempValidateLookupData.

Листинг 2. Создание табличной переменной

-- Создаем табличную переменную для хранения -- данных о проверках declare @tempValidateLookupData table ( [Type] nvarchar(30), [Name] nvarchar(30), [Value] nvarchar(300), [Test] nvarchar(30), [TestResult] int ) -- Заполняем табличную переменную данными о проверках, -- которые требуется выполнить insert @tempValidateLookupData select [Type], [Name], [Value], [Test], NULL from OPENXML (@idoc, '/ValidateData/Validate') with ([Type] nvarchar(30), [Name] nvarchar(30), [Value] nvarchar(300), [Test] nvarchar(30))

Знакомимся с OPENXML

Теперь повнимательнее рассмотрим синтаксис OPENXML:

OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]]) [WITH (SchemaDeclaration | TableName)]

Параметр idoc - описатель документа, созданный для внутреннего представления XML-документа. Другими словами, это значение, ранее возвращенное процедурой sp_xml_preparedocument. Заметьте: в одной хранимой процедуре можно манипулировать несколькими XML-строками. В таком случае необходимо вызвать sp_xml_preparedocument для каждой XML-строки и объявить по отдельной переменной для хранения каждого возвращенного описателя.

Параметр rowpattern идентифицирует узлы XML-документа, связанного с описателем idoc, которые должны обрабатываться как записи. Он указывает XML-анализатору, где в XML-документе находятся интересующие вас данные.

Параметр flags задает, какое сопоставление используется при запросе в первую очередь - ориентированное на атрибуты (attribute-centric) или на элементы (element-centric). Если этот параметр опущен, SQL Server 2000 по умолчанию применяет сопоставление, ориентированное на атрибуты, что вполне подходит для нашего случая. Подробнее об этом параметре см. описание функции OPENXML в SQL Server Books Online.

Раздел WITH функции OPENXML указывает SQL Server 2000, какие типы данных SQL сопоставляются содержимому XML-документа. Вы можете либо явно задать поля, либо сослаться на таблицу базы данных с подходящей структурой. Каждое поле XML-документа, используемое в операторе SELECT, должно присутствовать в разделе WITH (описываться явно или в таблице). В рассматриваемом примере:

-- Заполняем табличную переменную данными о проверках, -- которые требуется выполнить insert @tempValidateLookupData select [Type], [Name], [Value], [Test], NULL from OPENXML (@idoc, '/ValidateData/Validate') with ([Type] nvarchar(30), [Name] nvarchar(30), [Value] nvarchar(300), [Test] nvarchar(30))

имена полей в операторе SELECT и в разделе WITH должны соответствовать именам атрибутов во входном XML-документе. Также заметьте, что, поскольку XML-данные рассматриваются как набор записей, программа проверки на допустимость будет правильно работать при любом количестве значений (хоть при одном, хоть при десяти): вызывающий процесс просто передает столько узлов XML-документа, сколько нужно проверить на допустимость.

Освобождение памяти

Внутреннее представление XML-документа остается в памяти, пока соединение процесса с сервером не будет закрыто или сброшено или пока память не освободят явно. Следует вызывать системную процедуру sp_xml_removedocument как можно раньше, поскольку лучше поскорее освободить эту память, чтобы на сервере было доступно больше ресурсов (для освобождения памяти требуется указать описатель):

-- Освобождаем память сервера, которая используется -- образом входных XML-данных, созданным OPENXML exec sp_xml_removedocument @idoc

Это особенно важно в средах, где используется пул соединений. В таких средах соединения могут существовать в течение нескольких дней, и многократное создание образов XML-документов в памяти без последующего освобождения может вызвать снижение производительности, поскольку количество свободной памяти сократится. Пул соединений сбрасывает соединение, если оно повторно используется кем-то другим, при этом освобождаются и описатели XML-данных. Но лучше явно освобождать описатели, а не полагаться, на то, что при сбросе соединений это сделают за вас.

После разбора XML-документа процедура sptxValidateLookupData выполняет большой оператор IF, чтобы выбрать требуемую проверку и занести в переменную @TestResult значение, соответствующее результатам проверки. Затем результаты проверки используются при обновлении табличной переменной (листинг 3).

Листинг 3. Выбор требуемой проверки

-- Заносим результаты проверок в табличную переменную, -- перебирая записи, у которых поле TestResult содержит NULL while exists (select TestResult from @tempValidateLookupData where TestResult is null) begin -- Извлекаем данные из записи, проверяемой на допустимость Select top 1 @Type=[Type], @Name=[Name], @Value=Value, @Test=Test from @tempValidateLookupData where TestResult is null -- И выполняем соответствующую проверку... -- Проверка для области Countries (взята в качестве примера) if @Type = 'Countries' begin if exists (select CountryCode from dbo.Country where CountryCode = convert(nvarchar(4), @Value)) select @TestResult = CASE when @Test = 'Exists' then 1 else 0 end else select @TestResult = CASE when @Test = 'Not Exists' then 1 else 0 end end -- (16 других проверок на допустимость опущены для краткости) -- Обновляем соответствующую запись: заносим в нее результат -- проверки update @tempValidateLookupData set TestResult = @TestResult where Name = @Name and Value = @Value and Test = @Test end

SQL-синтаксис для возврата XML-данных

Итак, проверки на допустимость выполнены, теперь нужно возвратить результаты вызывающему процессу. SQL Server 2000 поддерживает несколько механизмов вывода XML-данных с помощью директивы FOR XML оператора SELECT. Прежде чем продолжить рассказ о sptxValidateLookupData, я кратко рассмотрю еще кое-какие новшества в синтаксисе языка SQL.

В SQL Server 2000 три типа разделов FOR XML. FOR XML RAW и FOR XML AUTO позволяют сформировать простейший XML-вывод с минимумом усилий и соответственно с отсутствием контроля над форматом вывода. Большинство уважающих себя программистов для SQL Server 2000 используют FOR XML EXPLICIT. При применении режима EXPLICIT программист полностью контролирует вид XML-документа, возвращаемого запросом, и должен обеспечить синтаксическую корректность и допустимость XML-документа.

Как выглядит содержимое раздела EXPLICIT?

Существует ряд жестких синтаксических требований к формированию запросов, использующих режим EXPLICIT. Каждый запрос с режимом EXPLICIT должен содержать два поля метаданных. У первого поля, указываемого в операторе SELECT, должно быть имя Tag и тип int. Это номер тэга текущего элемента, т. е. фактически номер типа поддерева. У второго поля должно быть имя Parent и тоже тип int. Оно содержит номер тэга элемента, который является родителем текущего элемента. Эти поля описывают иерархию XML-дерева. Если поле Parent записи имеет значение 0 или NULL, его данные располагаются на вершине XML-иерархии. Кроме этого единственного исключения, все значения поля Parent должны соответствовать ранее объявленным значениям тэгов. Заметьте: набор результатов должен содержать ровно одну запись, у которой поле Parent содержит 0 или NULL, причем она должна быть первой в наборе результатов (если имеется более одной записи, у которой номер тэга родителя равен 0 или NULL, генерируется XML-фрагмент).

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

[ElementName!TagNumber!AttributeName!Directive]

где ElementName - имя элемента (если в качестве ElementName указано "Countries", результатом будет <Countries>), а TagNumber - номер тэга элемента. TagNumber вместе с полями метаданных Tag и Parent описывает иерархию XML-дерева. Каждый TagNumber соответствует одному ElementName, а AttributeName является именем XML-атрибута (если оно задано).

Рассмотрение использования Directive и работы в нескольких ситуациях, в которых AttributeName может быть NULL, выходит за рамки данной статьи. Раздел "Using Explicit Mode" в SQL Server 2000 Books Online - превосходный источник дополнительной информации.

Кроме того, для формирования корректных XML-данных набор результатов должен быть упорядочен так, чтобы каждый родитель шел непосредственно перед своими потомками.

Вернемся к sptxValidateLookupData. Как вы помните, в описании процедуры говорилось о двух форматах возвращаемого набора результатов. Если все проверки данного пакета возвратили TRUE, я возвращаю пустой тэг <Results/>. Но если какая-то проверка потерпела неудачу, я возвращаю XML-документ со списком ошибок.

В листинге 4 приведен код процедуры sptxValidateLookupData, который генерирует наборы результатов с помощью FOR XML EXPLICIT. Синтаксис режима EXPLICIT довольно многословен, поэтому рассмотрим его по частям. Первая ветвь оператора IF обрабатывает простую ситуацию, когда все мои проверки возвратили TRUE:

Листинг 4. Применение FOR XML EXPLICIT

-- Если все проверки на допустимость пройдены, -- возвращаем пустой набор XML-данных... if not exists (select [TestResult] from @tempValidateLookupData where TestResult = 0) select 1 as TAG, 0 as parent, NULL as [Results!1!] for xml explicit -- ...иначе возвращаем XML-данные о проверках, -- потерпевших неудачу else select 1 as TAG, 0 as parent, NULL as [Errors!1!], NULL as [Error!2!], NULL as [Error!2!ErrorMessage], NULL as [Error!2!FieldName] union all select 2 as TAG, 1 as parent, NULL, NULL, ltrim(rtrim(value)) + ' does not exist in ' + type, [name] from @tempValidateLookupData where [TestResult] = 0 and test = 'Exists' union all select 2 as TAG, 1 as parent, NULL, NULL, ltrim(rtrim(value)) + ' already exists in ' + type, [name] from @tempValidateLookupData where [TestResult] = 0 and test = 'Not Exists' for xml explicit

Поскольку AttributeName не задано, этот оператор создаст единственный XML-элемент Results без атрибутов и потомков: <Results/>

Ветвь ELSE гораздо интереснее: в ней я формирую XML-данные о проверках, потерпевших неудачу, объединяя операторы SELECT с помощью UNION. Как показано в листинге 5, каждый запрос формирует узлы выходных XML-данных определенного типа (который описывается в комментариях после каждого запроса).

Листинг 5. Формирование узлов

-- ...иначе возвращаем XML-данные о проверках, -- потерпевших неудачу else select 1 as TAG, 0 as parent, NULL as [Errors!1!], NULL as [Error!2!], NULL as [Error!2!ErrorMessage], NULL as [Error!2!FieldName] -- (приведенный выше запрос формирует элемент <Errors>) union all select 2 as TAG, 1 as parent, NULL, NULL, ltrim(rtrim(value)) + ' does not exist in ' + type, [name] from @tempValidateLookupData where [TestResult] = 0 and test = 'Exists' -- (приведенный выше запрос формирует узлы вида -- <Error ErrorMessage="BO1 does not exist in Countries" -- FieldName="CountryCode"/>) union all select 2 as TAG, 1 as parent, NULL, NULL, ltrim(rtrim(value)) + ' exists in ' + type, [name] from @tempValidateLookupData where [TestResult] = 0 and test = 'Not Exists' -- (приведенный выше запрос формирует узлы вида -- <Error ErrorMessage="JA exists in Countries" -- FieldName="CountryCode"/>) for xml explicit

Заметьте: запросы, объединенные UNION, упорядочены так, чтобы каждый потомок шел сразу за своим родителем, хотя узлы могут быть потомками нескольких подзапросов (у запросов "exists" и "not exists" поля tag и parent имеют одинаковые значения). Когда запросы, объединенные с помощью UNION, обрабатываются в режиме FOR XML EXPLICIT, сериализатор FOR XML вычисляет имена полей [ElementName!TagNumber!AttributeName!Directive] и значения полей метаданных Tag и Parent, а затем выводит XML-иерархию, заданную программистом:

Миссия выполнена

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

XML-программирование в SQL Server 2005

При разработке SQL Server 2005 Microsoft значительно усовершенствовала поддержку XML. Последние два года я создавал новую систему на основе внутренних версий этого продукта (рай для SQL-разработчика!) и рад сообщить, что программистов, использующих XML, ждет масса хороших новостей.

Весь синтаксис поддержки XML, введенный в SQL Server 2000, не изменился, но в SQL Server 2005 в него внесена уйма дополнений. Я рассмотрю некоторые из них и покажу, как изменится хранимая процедура sptxValidateLookupData, рассмотренная в статье. Я также расскажу о следующих новшествах SQL Server 2005: о типах данных XML, FOR XML PATH, TYPE, синтаксисе nodes().

Все методики работы с XML, применяемые в SQL Server 2000 (передача XML-строк хранимым процедурам в параметрах типа ntext, манипулирование ими с помощью sp_xml_preparedocument, sp_xml_removedocument, FOR XML EXPLICIT и OPENXML), доступны и в SQL Server 2005, но в новой версии появились средства, позволяющие использовать другие подходы. Как только вы поработаете с новым синтаксисом, вам уже не захочется возвращаться к старому.

В SQL Server 2005 внесены революционные изменения в сам механизм хранения данных, в частности введены три новых типа данных: nvarchar(max), varbinary(max) и xml. В T-SQL возникали сложности с обработкой значений типа ntext, поэтому, если вы собираетесь по-прежнему использовать те же конструкции работы с XML, что и в SQL Server 2000, имеет смысл отказаться от ntext и перейти на nvarchar(max) и varbinary(max). Однако наиболее интересен тип данных XML, также добавленный в ядро сервера. Он предоставляет программистам ряд совершенно новых возможностей.

Переработанный пример

Как вы помните, хранимая процедура sptxValidateLookupData, рассмотренная в статье, поддерживает единый процесс пакетной проверки пользовательского ввода в Web-страницы, позволяющий выполнить несколько разных проверок на допустимость для значений, получаемых из гетерогенных источников. Директивы, описывающие проверки, передаются хранимой процедуре в одном XML-документе. Хранимая процедура выполняет проверки и возвращает результаты вызывающему процессу, тоже в одном XML-документе.

Новая версия процедуры

Как и в процедуре для SQL Server 2000, я помещаю содержимое входного XML-документа в табличную переменную. Но я больше не вызываю sp_xml_preparedocument и не применяю OPENXML. Как показано в листинге 6, я изменил тип данных параметра на xml (с ntext) и воспользовался новым синтаксисом nodes().

Листинг 6. Использование типа данных XML

create procedure sptxValidateLookupData @XMLString xml as set nocount on declare @idoc int, @Name nvarchar(30), @Value nvarchar(300), @Test nvarchar(30), @Result int, @Type nvarchar(30), @TestResult int -- Создаем табличную переменную для хранения -- данных о проверках declare @tempValidateLookupData table ( [Type] nvarchar(30), [Name] nvarchar(30), [Value] nvarchar(300), [Test] nvarchar(30), [TestResult] int ) -- Заполняем табличную переменную данными о проверках, -- которые требуется выполнить insert @tempValidateLookupData select ref.value ('@Type', '[nvarchar](30)'), ref.value ('@Name', '[nvarchar](30)'), ref.value ('@Value', '[nvarchar](300)'), ref.value ('@Test', '[nvarchar](30)'), NULL from @XMLString.nodes('/ValidateData/Validate') as node(ref)

Давайте повнимательнее рассмотрим метод nodes. Он позволяет получить ссылку для каждой записи, которая соответствует элементу Validate, находящемуся внутри элемента ValidateData переменной @XMLString. Эта переменная описывается инструкцией AS NODE(ref) как набор записей, представляющий узлы (node rowset). В каждом определении поля в операторе SELECT вызывается метод value, извлекающий значение заданного атрибута. Он выполняется для каждой записи, поэтому для каждого элемента Validate с параметрами проверки генерируется по одной записи.

В каждом вызове метода value указывается имя атрибута элемента, извлекаемого из XML-узла. Перед именем атрибута ставится символ @, и оно заключается в одинарные кавычки, затем (также в кавычках) указывается тип данных SQL, сопоставляемый атрибуту.

Логика выполнения проверок на допустимость в новой версии sptxValidateLookupData осталась прежней.

Возврат результатов

Как вы помните, в исходной спецификации процедуры описывались два формата возвращаемого набора записей. Если все проверки данного пакета возвратили TRUE, набор должен содержать пустой тэг <Results/>. Однако, если какие-то проверки потерпели неудачу, нужно вернуть список XML-данных об ошибках.

В листинге 7 приведен новый код процедуры sptxValidateLookupData, генерирующий наборы результатов с помощью FOR XML PATH, TYPE. Обратите внимание, насколько этот синтаксис компактнее старого синтаксиса FOR XML EXPLICIT.

Листинг 7. Применение FOR XML PATH

-- Если все проверки на допустимость пройдены, -- возвращаем пустой набор XML-данных... if not exists (select [TestResult] from @tempValidateLookupData where TestResult = 0) select null for xml path ('Results'), type -- ...иначе возвращаем XML-данные о проверках, -- потерпевших неудачу else select null, ( select ltrim(rtrim(value)) + ' does not exist in ' + type AS '@ErrorMessage', [name] AS '@FieldName' from @tempValidateLookupData where [TestResult] = 0 and test = 'Exists' for xml path ('Error'), type ), ( select ltrim(rtrim(value)) + ' already exists in ' + type AS '@ErrorMessage', [name] AS '@FieldName' from @tempValidateLookupData where [TestResult] = 0 and test = 'Not Exists' for xml path ('Error'), type ) for xml path ('Errors'), type

Как и прежде, первая ветвь оператора IF обрабатывает простой случай, когда все проверки возвратили TRUE:

Ветвь ELSE, как и раньше, формирует XML-данные для проверок, потерпевших неудачу, но на сей раз с помощью нескольких вложенных операторов SELECT FOR XML PATH, TYPE. Явно заданные имена полей указывают SQL Server, что их нужно использовать в качестве имен XML-атрибутов (перед ними должен идти символ @, их надо заключить в одинарные кавычки). Директивы FOR XML PATH ('Error') указывают серверу, что требуется обернуть XML-данные, создаваемые внутренними операторами SELECT, элементом Error, а директива FOR XML PATH ('Errors') - что внешний SELECT формирует корневой элемент 'Errors'. Таким образом, этот SQL-код генерирует тот же набор результатов, что и прежде, но с помощью гораздо более лаконичного запроса, чем запрос с FOR XML EXPLICIT.

Может, на первый взгляд это и не очевидно, но отказ от запросов SELECT, объединенных операторами UNION, выполняемых при использовании FOR XML EXPLICIT, делает код гетерогенных запросов гораздо компактнее и удобнее в сопровождении. Например, недавно моя группа переписала одну пользовательскую функцию SQL Server 2000 длиной в 5000 строк (большую их часть составлял 43-уровневый запрос с FOR XML EXPLICIT). Новый синтаксис позволил уложиться в 497 строк.

Если вы хотите по-прежнему придерживаться модели с UNION (что сомнительно), то можете написать второй запрос SELECT так, как показано в листинге 8. Этот подход не сработал бы, если бы ваши внутренние XML-узлы содержали разные количества атрибутов. Одно из основных преимуществ нового вложенного синтаксиса в том, что в отличие от операторов SELECT с UNION количество полей в подзапросах не обязательно должно быть одинаковым. Тем не менее, пример в листинге 8 демонстрирует еще одну новую конструкцию - FOR XML ROOT. Она позволяет объявить корневой элемент возвращаемых XML-данных, не создавая явный запрос SELECT. Дополнительные сведения по этому вопросу см. в статье "XML Options in Microsoft SQL Server 2005".

Листинг 8. Применение FOX XML ROOT

select ltrim(rtrim(value)) + ' does not exist in ' + type AS '@ErrorMessage', [name] AS '@FieldName' from @tempValidateLookupData where [TestResult] = 0 and test = 'Exists' union all select ltrim(rtrim(value)) + ' already exists in ' + type AS '@ErrorMessage', [name] AS '@FieldName' from @tempValidateLookupData where [TestResult] = 0 and test = 'Not Exists' for xml path ('Error'), root ('Errors'), type

С помощью этих новых средств SQL Server 2005 вы можете запрашивать и генерировать XML-данные еще эффективнее, чем раньше. Я рассмотрел лишь небольшую часть возможностей нового синтаксиса. SQL Server 2005 позволяет делать с XML-данными почти все, что угодно. Если вас интересуют ресурсы с самой свежей информацией о SQL Server 2005, посетите сайт "Introducing SQL Server 2005".

msdn.microsoft.com

Работа с XML в Microsoft SQL Server 2008. Выборка данных в формате XML.

Всем привет! В этой статье мы поговорим о типе представления данных XML и рассмотрим, какие средства предоставляет Microsoft SQL Server 2008 для работы с XML. Для начала, давайте вспомним, что такое XML и как он выглядит. Язык XML – один из самых распространенных форматов представления иерархических данных практических во всех платформах и технологиях. В основе веб-страниц лежит язык HTML, который является подвидом XML. В основе сервисов SOAP лежит обмен данными в формате XML. Данное представление, также пользуется популярностью на многих платформах в качестве хранилища данных.

Пример данных в формате XML можно увидеть ниже: 1: <Devices> 2: <Device Name="Lumia" Vendor="Nokia" ShopId="2" /> 3: <Device Name="Sensation" Vendor="HTC" ShopId="1" /> 4: <Device Name="Mozart" Vendor="HTC" ShopId="2" /> 5: <Device Name="iPhone" Vendor="Apple" ShopId="1" /> 6: </Devices>

Однако, как связан формат XML и SQL Server, в котором данные, как известно, хранятся в реляционном виде. Как было сказано выше, XML используется для представления иерархической структуры данных. Соответственно в случае необходимости извлечения из реляционной базы иерархической структуры, использование одного запроса, который будет формировать XML документ, может избавить разработчика от необходимости выполнения нескольких запросов.

Рассмотрим следующий пример. Допустим, в нашей базе есть две таблицы Магазины и Устройства, которые связанны внешним ключом, как показано ниже:

Нам необходимо извлечь все магазины и все устройства, которые продаются в каждом магазине. Стандартным образом мы могли бы выполнить эту задачу двумя способами: соединить две таблицы при помощи команды JOIN; или сначала выбрать все магазины, а затем, для каждого магазина запросить список устройств. При помощи XML мы можем сформировать необходимую выборку одним простым запросом:

1: SELECT s.Id AS '@Id', s.Name AS '@Name', 2: ( 3: SELECT d.Id AS '@Id', d.Name AS '@Name', d.Vendor AS '@Vendor' 4: FROM Devices d WHERE d.ShopId = s.Id 5: FOR XML PATH('Device'), TYPE 6: ) AS 'Devices' 7: FROM Shops s 8: FOR XML PATH('Shop'), ROOT('Shops')

В результате мы получим следующий документ, который можно будет с легкостью обработать на любом современном языке программирования:

2: <Shop Name="PC Shop"> 4: <Device Name="Sensation" Vendor="HTC" /> 5: <Device Name="iPhone" Vendor="Apple" /> 8: <Shop Name="Rozetka"> 10: <Device Name="Lumia" Vendor="Nokia" /> 11: <Device Name="Mozart" Vendor="HTC" />

Данный запрос позволяет контролировать каким образом будут созданы элменты в документе. В данном случае мы указали название секции верхнего уровня и установили, что все колонки таблиц будут записаны, как атрибуты элементов.

Давайте изменим запрос таким образом, чтобы колонка Name таблицы ‘Магазины’ была отдельным XML элементом, название каждого устройства выводилось внутри тэга, а также добавим в каждый элемент ‘Shop’ комментарий XML:

2: CAST('<Name>' + s.Name + '</Name>' as XML) AS 'node()', 5: d.Vendor + ' ' + d.Name AS 'text()' 6: FROM Devices d WHERE d.ShopId = s.Id 7: FOR XML PATH('Device'), TYPE 9: newid() as 'comment()' 11: FOR XML PATH('Shop'), ROOT('Shops') Полученный результат: 1: <Shops> 2: <Shop> 3: <Name>PC Shop</Name> 4: <Devices> 5: <Device>HTC Sensation</Device> 6: <Device>Apple iPhone</Device> 7: </Devices> 8:  9: </Shop> 10: <Shop> 11: <Name>Rozetka</Name> 12: <Devices> 13: <Device>Nokia Lumia</Device> 14: <Device>HTC Mozart</Device> 15: </Devices> 16:  17: </Shop> 18: </Shops>

В данном запросе мы использовали три внутренних команды: node() – выводит содержимое поля внутрь элемента XML, без каких-либо преобразований; text() – выводит значение внутрь тэга; comment() – печатает значение, заключая его в кавычки комментариев XML.

В запросах выше мы использовали команду FOR XML PATH – это самый мощный режим, позволяющий полностью контролировать все параметры и структуру результирующего документа. Данный режим подходит для большинства задач по извлечению данных в формате XML.

Однако, Microsoft SQL Server предоставляет еще несколько команд, которые используются в частных случаях для выполнения специфических задач:

FOR XML RAW. Эта команда – самый простой способ запросить XML данные. Данная команда представляет каждую строку результирующую строку выборки как один элемент документа, а все колонки – как его атрибуты. Например, следующий запрос:

1: SELECT * FROM Devices FOR XML RAW ('Device') Вернет следующий результат: 1: <Device Name="Lumia" Vendor="Nokia" ShopId="2" /> 2: <Device Name="Sensation" Vendor="HTC" ShopId="1" /> 3: <Device Name="Mozart" Vendor="HTC" ShopId="2" /> 4: <Device Name="iPhone" Vendor="Apple" ShopId="1" />

Иногда, в наших таблицах хранятся значения со значением NULL. По умолчанию такие значения просто вырезаются из результирующего документа. Изменить это поведение можно модернизировав запрос следующим образом:

1: SELECT * FROM Devices FOR XML RAW('Device'), ELEMENTS XSINIL

Теперь, если в столбце Vendor таблицы Devices будет находится значение NULL – в документе оно будет отображено следующим образом:

1: <Vendor xsi:nil="true" />

FOR XML AUTO. Данная команда отличается от XML RAW только тем, что поддерживает иерархии при выборке данных.

FOR XML EXPLICIT. Этот режим самый неудобный из всех, представленных в среде SQL Server. Он требует объемных запросов, которые подготавливают данные в специальном формате. Практически не используется на практике.

В этой статье мы рассмотрели, каким образом можно извлекать данные из реляционной базы в формате XML, какие средства и режимы предоставляет SQL Server для формирования и гибкой настройки документов XML. В следующей статье мы узнаем об обратном процессе, каким образом можно вставлять XML данные в таблицы базы данных.

Спасибо за внимание!

djekmusic.blogspot.com

xml - Удаление пустых узлов XML с использованием T-SQL FOR XML PATH

Я использую FOR XML PATH для построения XML из таблицы в SQL Server 2008R2. XML должен быть построен следующим образом:

Согласно файлам схемы, InnerContainerElement является необязательным, тогда как требуется InnerNodeOne. Файлы схемы не настроены мной, довольно сложны, ссылаются друг на друга и не имеют явных пространств имен XSD, поэтому я не могу их легко загрузить в базу данных.

XML должен быть создан из таблицы, которая заполняется с использованием следующего запроса:

SELECT 1 AS NumberNode , '0001' AS [FormattedNumberNode] , '0240' AS [InnerNodeOne] , '201201' AS [InnerNodeStartDate] INTO #temporaryXMLStore UNION SELECT 2 AS NumberNode , '0001' AS [FormattedNumberNode] , NULL AS [InnerNodeOne] , NULL AS [InnerNodeStartDate]

Я могу представить два способа построения этого XML с помощью FOR XML PATH.

1) Использование "InnerContainerElement" в качестве имени результата из подзапроса XML:

SELECT NumberNode , [FormattedNumberNode] , ( SELECT [InnerNodeOne] , [InnerNodeStartDate] FOR XML PATH(''), TYPE ) AS [InnerContainerElement] FROM #temporaryXMLStore FOR XML PATH('OuterElement'), ROOT('Root') TYPE

2) Использование "InnerContainerElement" в качестве выходного элемента из подзапроса XML, но без его именования:

SELECT NumberNode , [FormattedNumberNode] , ( SELECT [InnerNodeOne] , [InnerNodeStartDate] FOR XML PATH('InnerContainerElement'), TYPE ) FROM #temporaryXMLStore FOR XML PATH('OuterElement'), ROOT('Root'), TYPE

Однако ни один из них не дает желаемого результата: в обоих случаях результат выглядит так:

Всякий раз, когда InnerContainerElement пуст, он по-прежнему отображается как пустой элемент. Это недопустимо в соответствии с схемой: всякий раз, когда элемент InnerContainerElement находится в XML, требуется также InnerNodeOne.

Как я могу построить запрос FOR XML PATH таким образом, что InnerContainerElement используется, когда он пуст?

qaru.site

добейтесь большего от SQL Server

Автор:

Простой пример: пакетная проверка пользовательского ввода

Работа с XML-данными

Знакомимся с OPENXML

Освобождение памяти

SQL-синтаксис для возврата XML-данных

Как выглядит содержимое раздела EXPLICIT?

Миссия выполнена

XML-программирование в SQL Server 2005

Переработанный пример

Новая версия процедуры

Возврат результатов

sp_xml_preparedocument;
OPENXML;
sp_xml_removedocument;
FOR XML EXPLICIT.

Рис. 1. Применение на практике некоторых новшеств в синтаксисе T-SQL

Простой пример: пакетная проверка пользовательского ввода

Работа с XML-данными

Листинг 1. Хранимая процедура sptxValidateLookupData

Листинг 2. Создание табличной переменной

Знакомимся с OPENXML

Теперь повнимательнее рассмотрим синтаксис OPENXML:

OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]]) [WITH (SchemaDeclaration | TableName)]

Освобождение памяти

Листинг 3. Выбор требуемой проверки

SQL-синтаксис для возврата XML-данных

Как выглядит содержимое раздела EXPLICIT?

[ElementName!TagNumber!AttributeName!Directive]

Листинг 4. Применение FOR XML EXPLICIT

Листинг 5. Формирование узлов

Миссия выполнена

XML-программирование в SQL Server 2005

Переработанный пример

Новая версия процедуры

Листинг 6. Использование типа данных XML

Логика выполнения проверок на допустимость в новой версии sptxValidateLookupData осталась прежней.

Возврат результатов

Листинг 7. Применение FOR XML PATH

-- Если все проверки на допустимость пройдены, -- возвращаем пустой набор XML-данных... if not exists (select [TestResult] from @tempValidateLookupData where TestResult = 0) select null for xml path ('Results'), type -- ...иначе возвращаем XML-данные о проверках, -- потерпевших неудачу else select null, ( select ltrim(rtrim(value)) + ' does not exist in ' + type AS '@ErrorMessage', [name] AS '@FieldName' from @tempValidateLookupData where [TestResult] = 0 and test = 'Exists' for xml path ('Error'), type ), ( select ltrim(rtrim(value)) + ' already exists in ' + type AS '@ErrorMessage', [name] AS '@FieldName' from @tempValidateLookupData where [TestResult] = 0 and test = 'Not Exists' for xml path ('Error'), type ) for xml path ('Errors'), type

Как и прежде, первая ветвь оператора IF обрабатывает простой случай, когда все проверки возвратили TRUE:

Листинг 8. Применение FOX XML ROOT

technet.microsoft.com

sql-server - FOR XML PATH (''): экранирование "специальных" символов

Этот код в основном преобразует символы, основанные на позиции в одной строке, на символ в том же положении в другой строке и запускается для всех строк в таблице.

Когда я запускаю эту (упрощенную версию):

DECLARE @R char(40) DECLARE @U char(40) SET @R=' abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+'+char(181) SET @U=REVERSE(@R) DECLARE @TestTable TABLE (RowID int identity(1,1) primary key, Unreadable varchar(500)) INSERT INTO @TestTable VALUES ('+µt$zw!*µsu+yt!+s$xy') INSERT INTO @TestTable VALUES ('%*!!xµpxu!(') INSERT INTO @TestTable VALUES ('pxpµnxrµu+yµs%$t') ;WITH CodeValues AS ( SELECT Number,SUBSTRING(@R,Number,1) AS R,ASCII(SUBSTRING(@U,Number,1)) AS UA FROM Numbers WHERE Number<=LEN(@R) ) SELECT t.RowID ,(SELECT ''+c.R FROM Numbers n INNER JOIN CodeValues c ON ASCII(SUBSTRING(t.Unreadable,n.Number,1))=c.UA WHERE n.Number<=LEN(t.Unreadable) FOR XML PATH('') ) AS readable FROM @TestTable t

Я получаю следующее:

RowID readable ----------- --------------------------------------- 1 a simple translation 2 hello world 3 wow you ran this

Но нужно:

RowID readable ----------- --------------------------------------- 1 a simple translation 2 hello world 3 wow you ran this

Есть ли какой-либо способ, кроме REPLACE(), правильно ли показывать пробелы? Это также происходит при разрыве строк в моем фактическом коде.

Можно ли это лучше переписать? Я просто использовал FOR XML PATH('') для объединения отдельных значений строк вместе.

задан KM. 27 июня '09 в 0:30 источник поделиться

qaru.site