1. Главная страница » Компьютеры »

For xml path sql пример

Автор: | 16.12.2019

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

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

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

Также в языке T-SQL существует несколько конструкций для работы с XML данными, которые позволяют нам как сформировать XML документ, так и извлечь данные из XML документа. Например, с помощью конструкции OPENXML мы можем из XML данных получить привычные нам табличные данные, а с помощью конструкции FOR XML мы можем сформировать XML документ из табличных данных, для того чтобы сохранить его в базе данных или передать клиентскому приложению. В данной статье мы поговорим о том, как можно сформировать XML данные с помощью FOR XML, в следующих материалах я расскажу про конструкцию OPENXML.

Конструкция FOR XML в T-SQL

FOR XML – конструкция языка T-SQL, позволяющая получить результаты SQL запроса в формате XML. Данная конструкция определяется после определения всего SQL запроса, в случае необходимости в определение списка выборки вносятся изменения с целью формирования определённой структуры XML документа. Конструкцию FOR XML можно использовать также и во вложенных запросах. У данной конструкции есть несколько режимов работы.

Режимы работы конструкции FOR XML

  • RAW – режим, при котором в XML документе создается одиночный элемент для каждой строки результирующего набора данных инструкции SELECT;
  • AUTO – в данном режиме структура XML документа создается автоматически, в зависимости от инструкции SELECT (объединений, вложенных запросов и так далее);
  • EXPLICIT – самый расширенный режим работы конструкции FOR XML, при котором Вы сами формируете структуру итогового XML документа, за счет чего этот режим самый трудоемкий. Данный режим в основном используется для создания XML документов с очень сложной структурой, которую не получается реализовать с помощью других режимов;
  • PATH – это своего рода упрощенный режим EXPLICIT, который хорошо справляется со множеством задач по формированию XML документов, включая формирование атрибутов для элементов. Если Вам нужно самим сформировать структуру XML данных, то рекомендовано использовать именно этот режим.

У конструкции FOR XML есть еще и параметры, с помощью которых мы также можем влиять на структуру итогового XML документа. Давайте рассмотрим несколько полезных параметров конструкции FOR XML.

Параметры конструкции FOR XML

  • TYPE – возвращает сформированные XML данные с типом XML, если параметр TYPE не указан, данные возвращаются с типом NVARCHAR(MAX). Параметр необходим в тех случаях, когда над итоговыми XML данными будут проводиться операции, характерные для XML данных, например, выполнение инструкций на языке XQuery;
  • ELEMENTS – если указать данный параметр, столбцы возвращаются в виде вложенных элементов;
  • ROOT – параметр добавляет к результирующему XML-документу один элемент верхнего уровня (корневой элемент), по умолчанию «root», однако название можно указать произвольное.

Примеры создания XML данных с помощью конструкции FOR XML на T-SQL

Сначала давайте создадим тестовые данные, которые мы будет использовать в примерах. Все примеры будут выполнены в Microsoft SQL Server 2016 Express.

Читайте также:  Autohotkey команды для клавиатуры

For xml path sql пример

Данные созданы, теперь переходим к самим примерам.

1. Пример использования режима RAW в конструкции FOR XML

В этом примере мы используем режим RAW (режим указывается после ключевых слов FOR XML), а также параметр TYPE, для этого мы просто после основного запроса SELECT напишем данную конструкцию с указанием нужного режима и параметра.

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

For xml path sql пример

For xml path sql пример

Запрос нам вернул XML данные, где каждая строка таблицы TestTable является элементом row, а все столбцы отображены в виде атрибутов этого элемента.

В следующем запросе мы укажем дополнительные параметры конструкции FOR XML.

For xml path sql пример

For xml path sql пример

В данном случае мы изменили название каждого элемента на Product, для этого указали соответствующий параметр, добавили параметр ELEMENTS, для того чтобы столбцы были отображены в виде вложенных элементов, а также добавили корневой элемент Products с помощью параметра ROOT.

2. Пример использования режима AUTO в конструкции FOR XML

Сейчас давайте попробуем режим AUTO, при этом мы еще модифицируем запрос, например, добавим в него объединение для наглядности.

For xml path sql пример

For xml path sql пример

В данном режиме нам вернулись XML данные, где записи таблицы TestTable представлены в виде элементов, ее столбцы — в виде атрибутов, а соответствующие записи (на основе объединения) таблицы TestTable2 — в виде вложенных элементов с атрибутами. Параметром ROOT мы также задали корневой элемент.

3. Пример использования режима EXPLICIT в конструкции FOR XML

В режиме EXPLICIT мы уже можем сами указывать, что именно будет элементом, а что атрибутом.

For xml path sql пример

For xml path sql пример

В данном случае мы указали, что у нас будет всего один уровень в иерархии (1 AS Tag), если нужно несколько, то пишутся дополнительные запросы и объединяются с помощью UNUON ALL, в Parent при этом указывается номер родительского элемента, для верхнего уровня в Parent, как в нашем случае, ничего не указывается.

С помощью специального синтаксиса мы определили, что значение ProductId будет атрибутом Id у элемента Product, а ProductName и Price будут вложенными элементами элемента Product, для этого мы в имена этих столбцов добавили директиву ELEMENT. Параметры TYPE и ROOT мы здесь также использовали.

4. Пример использования режима PATH в конструкции FOR XML

Точно такой же результат, как и в примере выше, можно получить и с помощью режима PATH, только делается это немного проще.

For xml path sql пример

For xml path sql пример

В этом запросе мы с помощью параметра задали название элементов для каждой строки данных, т.е. Product, указали корневой элемент с помощью параметра ROOT. С помощью символа @, указали, что значение столбца ProductId должно быть атрибутом Id у элемента строки (если имя столбца начинается с символа @ и не содержит косую черту /, то SQL сервер создает атрибут элемента). В итоге структура XML данных у нас получилась точно такая же, как и при использовании режима EXPLICIT в примере 3.

Личный блог. Заметки о программировании и не только

Страницы

пятница, 11 декабря 2009 г.

Вывод результата выборки в строчку: FOR XML PATH

Бывает необходимость вывести результат выборки в строчку.
Рассмотрим на примере. Есть задача и список исполнителей, необходимо построчно вывести задачи и ответственных. В случае группировки по задаче, ответственному мы получим число записей = число задач * число ответственных, нам же требуется вывести задачи построчно. Это отлично реализуется с помощью инструкции MS SQL — FOR XML PATH, изначально используемой для формирования xml строки из выборки.

Пример кода: task — Задачи, executer — Исполнитель:
Рассмотрим более интересный пример, более близкий к изначальному применению FOR XML PATH — сформируем CSV файл из таблицы одним запросом без всяких циклов: Если field не строка, то его нужно привести: CAST(field AS VARCHAR(50)).

Добавление для ORACLE

3 комментария:

For xml path sql пример

Стоит отметить, что в Oracle wm_concat — недокументированная функция. В production использовать её — моветон.
Да и еще. При переходе с версии 10 на 11 её поведение немного поменялось.

For xml path sql пример

Спасибо за пример с XML PATH(») -пригодилось!)

ОБЛАСТЬ ПРИМЕНЕНИЯ: For xml path sql примерSQL Server For xml path sql примерБаза данных SQL Azure For xml path sql примерAzure Synapse Analytics (хранилище данных SQL) For xml path sql примерParallel Data Warehouse APPLIES TO: For xml path sql примерSQL Server For xml path sql примерAzure SQL Database For xml path sql примерAzure Synapse Analytics (SQL DW) For xml path sql примерParallel Data Warehouse

В следующих примерах показано использование режима PATH при формировании XML из запроса SELECT. The following examples illustrate the use of PATH mode in generating XML from a SELECT query. Многие из этих запросов являются запросами к XML-документам с инструкциями по производству велосипедов, хранящимся в столбце Instructions таблицы ProductModel. Many of these queries are specified against the bicycle manufacturing instructions XML documents that are stored in the Instructions column of the ProductModel table.

Читайте также:  Destiny 2 цена в стиме

Указание простого запроса в режиме PATH Specifying a simple PATH mode query

Этот запрос указывает режим FOR XML PATH. This query specifies a FOR XML PATH mode.

Следующий результат представляет собой элементный XML, в котором значение каждого столбца в итоговом наборе строк образует элемент. The following result is element-centric XML where each column value in the resulting rowset is wrapped in an element. Поскольку предложение SELECT не указывает псевдонимы для имен столбцов, формируются имена дочерних элементов, совпадающие с именами соответствующих столбцов в предложении SELECT . Because the SELECT clause does not specify any aliases for the column names, the child element names generated are the same as the corresponding column names in the SELECT clause. Для каждой строки в наборе строк добавляется тег row >. For each row in the rowset a row > tag is added.

Следующий результат совпадает с результатом запроса в режиме RAW с указанным параметром ELEMENTS . The following result is the same as the RAW mode query with the ELEMENTS option specified. Запрос возвращает элементный XML с установленным по умолчанию элементом row > для каждой строки в результирующем наборе. It returns element-centric XML with a default row > element for each row in the result set.

Дополнительно можно указать имя элемента строки, которое переопределит значение по умолчанию row >. You can optionally specify the row element name to overwrite the default row >. Например, следующий запрос возвращает элемент ProductModel > для каждой строки в наборе строк. For example, the following query returns the ProductModel > element for each row in the rowset.

Итоговый XML-документ будет иметь указанное имя элемента строки. The resulting XML will have a specified row element name.

Если задана строка нулевой длины, закрывающий тег не формируется. If you specify a zero-length string, the wrapping element is not produced.

Результат: This is the result:

Указание имен столбцов, подобных синтаксису языка XPath Specifying XPath-like column names

В следующем запросе указанное имя столбца ProductModelID начинается с символа @ и не содержит косой черты (/). In the following query the ProductModelID column name specified starts with ‘@’ and does not contain a slash mark (‘/’). Поэтому в итоговом XML-документе создается атрибут элемента row >, имеющий соответствующее значение столбца. Therefore, an attribute of the row > element that has the corresponding column value is created in the resulting XML.

Результат: This is the result:

Указав в запросе root параметр FOR XML , можно добавить один элемент верхнего уровня. You can add a single top-level element by specifying the root option in FOR XML .

Для формирования иерархии можно добавить синтаксис, подобный PATH. To generate a hierarchy, you can include PATH-like syntax. Если, например, изменить имя столбца Name на «SomeChild/ModelName», можно получить XML-документ с иерархией, показанной в следующем результате: For example, change the column name for the Name column to "SomeChild/ModelName" and you will obtain XML with hierarchy, as shown in this result:

Следующий запрос извлекает не только код модели продукта и его имя, но и расположения производственных инструкций для модели продукции. Besides the product model ID and name, the following query retrieves the manufacturing instruction locations for the product model. Поскольку столбец Instructions имеет тип xml , то для получения расположения указывается метод query() типа данных xml . Because the Instructions column is of xml type, the query() method of xml data type is specified to retrieve the location.

Частичный результат. This is the partial result. Поскольку в данном запросе имя столбца указано как ManuInstr, XML-данные, возвращенные методом query() , помещаются в тег ManuInstr >, как показано ниже: Because the query specifies ManuInstr as the column name, the XML returned by the query() method is wrapped in a ManuInstr > tag as shown in the following:

В предыдущий запрос FOR XML можно было включить пространства имен для элементов Root > и ProductModelData >. In the previous FOR XML query, you may want to include namespaces for the Root > and ProductModelData > elements. Для этого следовало сначала определить префикс пространства имен, создать привязку с помощью предложения WITH XMLNAMESPACES и затем использовать префиксы в запросе FOR XML. You can do this by first defining the prefix to namespace binding by using WITH XMLNAMESPACES and using prefixes in the FOR XML query. Дополнительные сведения с. в разделе Добавление пространств имен в запросы с помощью WITH XMLNAMESPACES. For more information, see Add Namespaces to Queries with WITH XMLNAMESPACES.

Читайте также:  Caterpillar cat s40 характеристики

Обратите внимание, что префикс MI также определяется в предложении WITH XMLNAMESPACES . Note that the MI prefix is also defined in the WITH XMLNAMESPACES . В результате метод query() заданного типа xml не определяет префикс в прологе запроса. As a result, the query() method of the xml type specified does not define the prefix in the query prolog. Результат: This is the result:

Формирование списка значений с помощью режима PATH Generating a value list using PATH mode

Данный запрос строит список значений кодов продуктов для каждой модели продукции. For each product model, this query constructs a value list of product IDs. Кроме того, для каждого кода продукта запрос создает вложенные элементы ProductName >, как показано в следующем фрагменте XML: For each product ID, the query also constructs ProductName > nested elements, as shown in this XML fragment:

Это запрос, создающий желаемый XML: This is the query that produces the XML you want:

Обратите внимание на следующие данные из предыдущего запроса: Note the following from the previous query:

Первая вложенная инструкция SELECT возвращает список ProductID, для чего использует значение data() в качестве имени столбца. The first nested SELECT returns a list of ProductIDs by using data() as the column name. Поскольку в режиме FOR XML PATH запрос указывает для имени элемента строки пустую строку, формирование элемента не происходит. Because the query specifies an empty string as the row element name in FOR XML PATH , no element is generated. Вместо этого список значений назначается атрибуту ProductID . Instead, the value list is assigned to the ProductID attribute.

Вторая вложенная инструкция SELECT извлекает названия продуктов для модели продукта. The second nested SELECT retrieves product names for products in the product model. Запрос формирует элементы ProductName >, которые возвращаются в виде элементов ProductNames >, поскольку в качестве имени столбца в запросе указано ProductNames . It generates ProductName > elements that are returned wrapped in the ProductNames > element, because the query specifies ProductNames as the column name.

Частичный результат: This is the partial result:

Вложенный запрос, формирующий имена продуктов, возвращает результат в виде строки, которая преобразуется в сущность и затем добавляется в XML-документ. The subquery constructing the product names returns the result as a string that is entitized and then added to the XML. Если добавить директиву типа FOR XML PATH (»), type , вложенный запрос возвращает результат как тип xml , а преобразования в сущность не происходит. If you add the type directive, FOR XML PATH (»), type , the subquery returns the result as xml type and no entitization occurs.

Добавление пространств имен в итоговый XML-документ Adding namespaces in the resulting XML

Как описано в разделе Добавление пространств имен с помощью предложения WITH XMLNAMESPACES, предложение WITH XMLNAMESPACES может использоваться для включения пространств имен в запросы в режиме PATH. As described in Adding Namespaces Using WITH XMLNAMESPACES, you can use WITH XMLNAMESPACES to include namespaces in the PATH mode queries. Например, имена, указанные в предложении SELECT, имеют префиксы пространства имен. For example, names specified in the SELECT clause include namespace prefixes. Следующий запрос в режиме PATH строит XML-документ с пространствами имен. The following PATH mode query constructs XML with namespaces.

Атрибут @xml:lang , добавляемый к элементу English >, определяется в стандартном пространстве имен xml. The @xml:lang attribute added to the English > element is defined in the predefined xml namespace.

Результат: This is the result:

Следующий запрос похож на приведенный в примере В, отличаясь тем, что в нем пространства имен добавляются в результирующий XML-документ с помощью предложения WITH XMLNAMESPACES . The following query is similar to example C, except that it uses WITH XMLNAMESPACES to include namespaces in the XML result. Дополнительные сведения с. в разделе Добавление пространств имен в запросы с помощью WITH XMLNAMESPACES. For more information, see Add Namespaces to Queries with WITH XMLNAMESPACES.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *