Разбить 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. Например:
- Поиск связанных элементов XML, фильтрующих атрибут текущего элемента в XPath
- XML Column - не может распылять / применять данные () в выражении, которое содержит тип «xxx» в предполагаемом типе
- Объединение SQL с XQuery и Linq To SQL
- SQL Server 2012 создает список VARBINARY в одном XML-элементе
- измельчение 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 .
- XPath fn: данные в SQL-сервере приводят к тому, что преобразование типа в выражении может влиять на «CardinalityEstimate» в выборе плана запроса
- Как передать переменную в XPath XQuery в выражении SQL
- Запрос дочерних узлов XML с предложением WHERE через SQL
- Замена префиксов пространства имен xml в SQL Server с помощью XQuery
- Как получить значение атрибута с помощью XQuery в MS SQL Server 2008
- Я запрашиваю схему, которая идентична во 2 файлах xml, за исключением самого высокого уровня
- tsql для xml, сложный xml
- как выбрать записи, похожие на какую-либо строку для любого столбца таблицы?
С 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)