Разбить XML-файлы с помощью SQL Server в отдельные строки

Вот что я делаю. Я вставляю XML-файл в столбец XML вместе с другими столбцами, такими как отметки даты и т. Д. Для ведения журнала (это не относится к этому запросу)

Файл выглядит примерно так:

<topLevelItem> <secondLevelItem> <secondLevelItemDetail> </secondLevelItemDetail> <secondLevelItemAnotherDetail> </secondLevelItemAnotherDetail> </secondLevelItem> <secondLevelItem> <secondLevelItemDetail> </secondLevelItemDetail> <secondLevelItemAnotherDetail> </secondLevelItemAnotherDetail> </secondLevelItem> <secondLevelItem> <secondLevelItemDetail> </secondLevelItemDetail> <secondLevelItemAnotherDetail> </secondLevelItemAnotherDetail> </secondLevelItem> <topLevelItem> 

Моя цель – иметь возможность запросить поле XML и получить набор результатов с каждым <secondLevelItem> в собственной строке, в форме XML. Например:

Строка 1:

  <secondLevelItem> <secondLevelItemDetail> </secondLevelItemDetail> <secondLevelItemAnotherDetail> </secondLevelItemAnotherDetail> </secondLevelItem> 

Строка 2:

  <secondLevelItem> <secondLevelItemDetail> </secondLevelItemDetail> <secondLevelItemAnotherDetail> </secondLevelItemAnotherDetail> </secondLevelItem> 

Строка 3:

  <secondLevelItem> <secondLevelItemDetail> </secondLevelItemDetail> <secondLevelItemAnotherDetail> </secondLevelItemAnotherDetail> </secondLevelItem> 

Это необходимо сделать с использованием Microsoft SQL Server и без использования CLI. Мне нужно иметь возможность объявить, какой узел XML я хочу разбить, чтобы начать, поскольку некоторые файлы имеют дополнительные XML-элементы, которые мне не нужны. Так что-то вроде WHERE node = secondLevelItem .

С SQL Server 2005 и новее в SQL Server есть отличная поддержка XQuery.

Этот подход здесь будет использовать .nodes() и .query() :

 SELECT Col.query('.') FROM dbo.YourTable CROSS APPLY YourXmlColumn.nodes('/topLevelItem/secondLevelItem') AS Tbl(Col) WHERE (some condition) 

Функция .nodes() создает «временную, встроенную» псевдо-таблицу Tbl с одним столбцом Col который содержит одну запись XML для каждого узла в столбце XML, который соответствует указанному XPath (в данном случае: каждый <secondLevelItem> внутри <topLevelItem> будет соответствовать).

Поскольку вам нужен весь XML для каждого из этих элементов XML, просто используйте .query('.') Чтобы вернуть этот полный XML-элемент.

Используйте узлы (), чтобы уничтожить ваш XML и использовать local-name () и sql: variable (), чтобы найти нужный вам узел, получить XML с запросом () .

 declare @NodeName varchar(100) = 'secondLevelItem' select TNquery('.') from YourTable cross apply XMLCol.nodes('//*[local-name()=sql:variable("@NodeName")]') as T(N) 

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

 DECLARE @XML xml SET @XML = '<topLevelItem> <secondLevelItem> <secondLevelItemDetail>One</secondLevelItemDetail> <secondLevelItemAnotherDetail>One One</secondLevelItemAnotherDetail> </secondLevelItem> <secondLevelItem> <secondLevelItemDetail>Two</secondLevelItemDetail> <secondLevelItemAnotherDetail>Two Two</secondLevelItemAnotherDetail> </secondLevelItem> <secondLevelItem> <secondLevelItemDetail>Three</secondLevelItemDetail> <secondLevelItemAnotherDetail>Three Three</secondLevelItemAnotherDetail> </secondLevelItem> </topLevelItem>' SELECT Tbl.Col.query('.') FROM @XML.nodes('/topLevelItem/secondLevelItem') AS Tbl(Col) 
  • Отношения SQL Server между двумя таблицами по xml type coumn и output xml
  • SQL Server: для управления сортировкой XML по атрибуту
  • Объединение значений xml при выборе XQuery в T-SQL
  • Почему при выполнении XQuery существует порядок сортировки в плане выполнения
  • Вставка нескольких узлов в поле xml в одном запросе
  • Как я могу найти слово с секретным кодом с Sql Server XQuery?
  • Как группировать конкатенацию нескольких столбцов?
  • Удалить уровни неопределенности пустых тегов XML в SQL Server
  • Как отображать XML-данные в виде табличного формата
  • XQuery существует проверка в select sql query
  • Обновление таблицы SQL Server с помощью XML
  • Давайте будем гением компьютера.