SQL Server FOR XML – основной запрос

Мне был предоставлен XML-документ, который я хочу сгенерировать с помощью SQL-скрипта, я не сделал ничего подобного и не смог найти никаких примеров, которые могли бы привести меня к возможности генерировать окончательный XML, который мне нужен ( и я не уверен, какой из возможных методов доступен, если лучше подходит для того, что мне нужно – EXPLICIT или PATH, или если это даже возможно). Я надеюсь, что кто-то, кто имеет опыт генерации XML из SQL, сможет указать мне в правильном направлении (или сказать мне, что я пытаюсь сделать, невозможно и что мне нужно сделать это с подзапросами).

Сценарий заключается в том, что я возвращаю детали продукта из одной таблицы (я бы предпочел не выполнять подзапросы для каждого из значений, которые мне нужны).

Xml Я надеюсь, что сможем генерировать выглядит (я не могу контролировать этот формат):

<records> <record> <fields> <field name="id"> <values> <value>666111</value> </values> </field> <field name="name"> <values> <value> <![CDATA[My Product Title]]> </value> </values> </field> </fields> </record> <record> ... </record> </records> 

Первый метод, на который я смотрел, – использование FOR XML PATH

 SELECT TOP 2 'id' AS "@name", p.product_id as [value], p.title FROM products p ORDER BY p.product_id DESC FOR XML PATH ('field'), ROOT ('fields'), ELEMENTS; 

и это дает мне XML:

 <fields> <field name="id"> <value>20624</value> <title>test154</title> </field> <field name="id"> <value>20623</value> <title>test153</title> </field> </fields> 

Это дает мне «то, что мне нужно», но я не могу указать макет, который мне нужен для следующих элементов.

Я также рассмотрел FOR XML EXPLICIT

 SELECT TOP 2 1 AS Tag, NULL AS Parent, p.product_id AS [record!1!product_id!ELEMENT], NULL AS [values!2!value!ELEMENT] FROM products p UNION ALL SELECT TOP 2 2, 1, p.product_id, p.title FROM products p ORDER BY [record!1!product_id!ELEMENT] DESC FOR XML EXPLICIT; 

Что дало мне следующий XML:

 <record> <product_id>20624</product_id> <values> <value>test154</value> </values> </record> <record> <product_id>20623</product_id> <values> <value>test153</value> </values> </record> 

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

Это запрос, который вы, возможно, ищете

«В середине» – это трюк, который позволяет вам создавать несколько элементов с тем же именем один под другим …

 DECLARE @tbl TABLE(id INT,name VARCHAR(100)); INSERT INTO @tbl VALUES (666111,'My Product Title 111') ,(666222,'My Product Title 222'); SELECT ( SELECT 'id' AS [field/@name] ,id AS [field/values/value] ,'' ,'name' AS [field/@name] ,name AS [field/values/value] FOR XML PATH('fields'),TYPE ) FROM @tbl AS tbl FOR XML PATH('record'),ROOT('records') 

Результат

 <records> <record> <fields> <field name="id"> <values> <value>666111</value> </values> </field> <field name="name"> <values> <value>My Product Title 111</value> </values> </field> </fields> </record> <record> <fields> <field name="id"> <values> <value>666222</value> </values> </field> <field name="name"> <values> <value>My Product Title 222</value> </values> </field> </fields> </record> </records> 

ОБНОВЛЕНИЕ: Насколько я знаю, нет чистого способа добавить CDATA разделы

По некоторым причинам люди в Microsoft считают, что разделы CDATA не нужны. Ну, их нет, но иногда они требуются …

Единственный чистый способ добавить разделы CDATA – использовать FOR XML EXPLICIT . Другим обходным решением было положить что-то вроде '|' + name + '#' '|' + name + '#' (используйте два символа, которые никогда не будут встречаться в ваших фактических данных.

Затем вы можете NVARCHAR(MAX) результат к NVARCHAR(MAX) , заменить эти символы на строковой базе.

Это вернет ваш XML как строку

 SELECT REPLACE(REPLACE(CAST( ( SELECT ( SELECT 'id' AS [field/@name] ,id AS [field/values/value] ,'' ,'name' AS [field/@name] ,'|' + name + '#' AS [field/values/value] FOR XML PATH('fields'),TYPE ) FROM @tbl AS tbl FOR XML PATH('record'),ROOT('records') ) AS NVARCHAR(MAX)),'|','<![CDATA['),'#',']]>') 

На данный момент вы отбрасываете это обратно в XML CDATA ушел 🙁

что-то вроде того

  declare @t table (id varchar(10)) insert into @t values ('1') insert into @t values ('2') select ( select t.id 'fields/field/@id' , t.id 'fields/field/name' from @tt for xml path(''), type ) 'records/record' for xml path('') 

Последний SQL, который я использовал:

 SELECT TOP 2 ( SELECT (SELECT 'id' AS [field/@id], product_id [field/values/value] FOR XML PATH(''), TYPE), (SELECT 'title' AS [field/@id], title [field/values/value] FOR XML PATH(''), TYPE) FOR XML PATH('fields'), TYPE ) FROM products FOR XML PATH('record'), ROOT('records') 

Поскольку это позволяет мне манипулировать выходом немного легче.

Спасибо вам за @xdd и особенно @Shnugo за ваши ответы! Конечное решение основано на предложении @ Shnugo, просто избегая трюка добавления лишних строк.

  • Медленное измельчение XML в SQL
  • Вызов веб-службы из хранимой процедуры
  • Работа с типом данных XML (SQL SERVER 2005)
  • Извлечение значения из XML в SQL Server
  • Чтение из XML с помощью TSQL
  • Запрос Sql для динамических узлов в XML
  • Запрос на получение XML-вывода для иерархических данных с использованием FOR XML PATH в SQL Server
  • Преобразование XML из одного формата в другой
  • Выбор десятичных чисел из XML, которые используют запятую в качестве разделителя
  • Оптимизация импорта XML в SQL Server
  • Извлечение значения дочернего узла из XML с использованием T-SQL
  • Interesting Posts

    SQL: создание временных переменных

    Передача значения Form1 TextBox в форму Form2

    SQL Server Best Practice – Как управлять индексами на большой, разнообразной таблице

    Подсчет между двумя датами

    T-SQL: как создать «частную» функцию внутри хранимой процедуры

    вызов одного сохраненного процесса из другого путем выполнения команды

    Как установить строку по умолчанию для запроса, который не возвращает строк?

    План обслуживания SQL Server 2008 R2 – что-нибудь еще?

    Добавление секунд с использованием DATEADD возвращает значение, округленное до минут – почему?

    Веб-сервис SQL Server 2008, устарел?

    Как описать строки, заканчивающиеся на один или два числа в одном шаблоне LIKE?

    Преимущества «использования» блока для инициализации SqlCommand

    AngularJS: как фильтровать на основе ближайшего расстояния от почтового кода?

    SQL GROUP BY по второму запросу

    SQL Server 2012 Обновить скорость таблицы, основанную на ставке в таблице 2

    Давайте будем гением компьютера.