Запросы для данных XML в SQL

1. Что такое XML
2. XML в SQL Server
3. Пример документа XML
4. Поиск файла XML в SQL
5. Загрузка файла XML в переменную в T-SQL
6. Чтение XML-файла с помощью OPENXML ()
7. XML-поиск с использованием нативных методов
8. Поиск документов XML с определенным пространством имен
9. суммирование

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

Для начала я представлю общую концепцию стандарта XML и его типичные приложения на практике. Я покажу готовые рецепты, примеры - как писать SQL-запросы в XML-документы.

В этой статье я расскажу об основных приложениях функции OPENXML () и встроенных методах типов данных XML. Связанные выражения XPath и элементы языка XQuery в аспекте поиска XML-документов и их преобразования в реляционную форму.

Что такое XML

XML ( расширяемый язык разметки ) часто называют «языком», но, как и SQL, он не является языком программирования. XML является наиболее популярным стандартом (языком) описания данных. Его коммерческое появление в конце 90-х годов вызвало взрыв приложений.

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

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

Одним из основных преимуществ XML является возможность строгой типизации документов (схемы XSD). Мы можем проверить их правильность с точки зрения конкретного шаблона. Определите элементы или атрибуты как обязательные или необязательные. Чтобы наложить ограничения на значения - как в конструкциях CHECK CONSTRAINT (например, диапазоны). Этот стандарт очень гибкий и независимый от платформы.

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

XML в SQL Server

Основные функции, связанные с XML, уже появились в SQL Server 2000 (например, функции OPENXML или FOR XML). В более поздних выпусках (начиная с версии 9 - 2005) XML стал полноценным, изначально поддерживаемым типом данных . Его реализация в SQL Server представляет собой серию специальных функций, таких как встроенные методы для создания документов XML, специальные типы индексов и поисковые системы FLWOR (XQuery).

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

Собственный тип данных XML может использоваться в разных местах, например

• при указании типов столбцов в определениях таблиц - для хранения документов XML в реляционной базе данных.
• в определении переменных - для обработки документов.
• в качестве значений, возвращаемых функцией или параметрами хранимых процедур.

Мы можем создавать XML-документы непосредственно из реляционной формы. Команды FOR XML используются для расширения синтаксиса языка SQL. Но эта статья не о них :)

С другой стороны - из XML-формы в реляционную форму мы можем трансформироваться с помощью функции OPENXML () или встроенных XML-методов. Использование выражений XPath и языка запросов XQuery. В этой статье я ограничусь обсуждением способов загрузки интересующих нас данных из XML-документов.

Пример документа XML

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

Фрагмент такого файла ниже:

Несколько слов о самой конструкции. Как правило, XML дает пользователю большую свободу в определении его собственных документов. Мы можем определить любую структуру, используя элементы и связанные атрибуты

<Element Attribute = "attribute_value"> item_value </ Element>

Каждому из них может быть присвоено значение, а их тип, область действия или символ (обязательный / необязательный) могут быть определены схемой.

Допустимый документ XML должен иметь корень. В нашем примере это родительский элемент с именем <course_table>. XML всегда строго определен, поэтому он чувствителен к регистру и необходимости закрывать теги элементов:

<имя_элемента> значение </ имя_имя>

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

Поиск файла XML в SQL

Первым примером будет запрос XML-файла из T-SQL с использованием функции OPENXML и простых выражений XPath. Для этого я буду использовать документ XML, который мы можем скачать со страницы ресурсов НБП - опубликованы средние курсы валют 14 марта 2014 г. ,

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

Схема таблицы, в которой хранятся курсы валют в реляционной форме, будет выглядеть следующим образом:

ИСПОЛЬЗУЙТЕ TEMPDB GO CREATE TABLE [dbo]. [KursyWalut] ([Won Code] [char] (3) NOT NULL, [Date] [date] NOT NULL, [Average_Trans] [smallmoney] NOT NULL, [Convertor] [smallint] NOT NULL ПО УМОЛЧАНИЮ 1, ОГРАНИЧЕНИЕ [PK_KursyWalut] ПЕРВИЧНЫЙ КЛЮЧ КЛАСТЕР ([код безопасности] ASC, [дата] ASC))

Конечно, при построении приложения необходимо будет снабжать базу каждый день. Это можно сделать несколькими способами, используя пакеты служб SSIS, пользовательские функции CLR или написав простые сценарии в Visual Basic. В нашем примере я предполагаю, что у нас есть доступный XML-документ на локальном сервере на диске D.

Загрузка файла XML в переменную в T-SQL

Первым шагом будет загрузка XML-файла на сервер и загрузка его в переменную уровня T-SQL. Мы будем использовать функцию OPENROWSET () для этой цели.

ОБЪЯВИТЬ @xmldata XML SELECT @xmldata = BulkColumn FROM OPENROWSET (BULK 'D: \ DataXML \ NBP \ a051z140314.xml', S IN GLE_BLOB) как T1 SELECT @xmldata в качестве документа

Весь документ был загружен в переменную типа XML. Мы можем поместить его непосредственно в соответствующий столбец таблицы или извлечь из него только соответствующую информацию.

Объекты типа XML отображаются в Management Studio в виде ссылки. Мы можем просмотреть такой документ, просто нажав на такую ​​ссылку.

Чтение XML-файла с помощью OPENXML ()

Функция OPENXML довольно старая и уже была доступна в SQL Server 2000. С ней напрямую связаны две специальные хранимые процедуры: sp_xml_preparedocument и sp_xml_removedocument .

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

Алгоритм работы с этой функцией включает три этапа:

1. Подготовка документа - то есть загрузка всей (!) В память сервера CACHE. Для этой цели используется процедура sp_xml_preparedocument , которой мы передаем в качестве параметра XML-документ. Он возвращает «дескриптор» документа - указатель на память CACHE, под которой он доступен.
2. Правильный поиск в документе с использованием OPENXML, используя простые выражения XPath для навигации по узлам.
3. Очистка памяти CACHE - освобождение ресурсов с помощью sp_xml_removedocument . Не забудьте не оставлять мусор.

В свою очередь, функция OPENXML () принимает 3 (необязательных 4) параметра:

• Первый - это «дескриптор» (указатель) на документ в памяти сервера кэша (возвращается sp_xml_preparedocument).
• Второй - путь (XPath), указывающий контрольную точку, которая будет использоваться при загрузке данных. К этому месту в структуре мы сможем обратиться к блоку WITH () этой функции.
• Третий - флаг, указывающий тип объектов (элементов, атрибутов или их смеси), значения которых мы хотим вернуть.
• Четвертое, необязательное определение пространства имен струи

Кроме того, с функцией OPENXML связан блок WITH () , в котором мы можем перемещаться по элементам и атрибутам (используя XPath), извлекая из XML значения, представляющие для нас интерес.

Давайте напишем скрипт, который извлекает данные из файла XML. В соответствии с алгоритмом, описанным ранее, мы подготовим документ для обработки, извлечем интересующую нас информацию и поместим ее в таблицу dbo. Курсы оценки. Наконец, мы освободим ресурсы, занятые нашим документом, в памяти CACHE.

DECLARE @xmldata XML - 0. Загрузка файла в переменную SELECT @xmldata = BulkColumn FROM OPENROWSET (BULK 'D: \ DataXML \ NBP \ a051z140314.xml', S IN GLE_BLOB) в виде T1 - 1. Подготовка документа DECLARE int @docH EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmldata - 2. OPENXML - мы берем в качестве ссылки / course_table / position - нас интересуют значения элементов (flag = 2) и загружаем в таблицу dbo. Курсы VALUE INSERT INTO dbo. KursyWalut SELECT debut_code, Data_publikacji, приведение (Заменить (kurs_sredni, ',', '.') На smallmoney) на Rate, оценить ОТ ОТКРЫТОГО (@docHandle, '/ tabela_kursow / item', 2) С (data_publikacji varchar (10) ../data_publikacji ', - повысить уровень char_code char (3), средний_пароль varchar (100), int преобразование) - 3. Очистка памяти EXEC sp_xml_removedocument @docHandle GO SELECT * из dbo. KursyWalut

Фрагмент содержимого таблицы dbo. Оценивается после загрузки данных из файла XML:

Навигация по узлам с использованием выражений XPath довольно интуитивна и похожа на указание пути, например, в файловой системе или объектах HTML. В любом случае, стандарт HTML является производным от того же семейства (SGML).

XML-поиск с использованием нативных методов

Представленная функция OPENXML очень плоха по сравнению с полной реализацией типа данных XML.

Тип XML имеет пять встроенных методов, которые предоставляют вам широкие возможности для поиска и изменения. Все они основаны на языке запросов XQuery и выражениях, связанных с FLWOR. Операция первых трех, которая обычно используется для извлечения информации из документов XML, я представлю на примерах.

Тип данных XML имеет следующие собственные методы:

• value () - возвращает одно значение, результат запроса XQuery в форме скалярного типа данных.
• query () - возвращает фрагмент (отсечение) XML-документа.
• node () - разбивает XML-документ на множество более мелких, подчиненных.
• exist () - ищет в документе XML наличие определенных элементов в нем. Обычно используются в строительных условиях, логические выражения в ГДЕ.
• modify () - используется для обновления XML-документа.

Запрос для документа XML, показанный в предыдущем примере с использованием функции OPENXML (), может быть выполнен с использованием гораздо более быстрых (!) , Собственных методов value () и node (). Их преимущество видно на первый взгляд. Они понятнее и удобнее. Они покажут свою истинную силу, когда вы дополнительно примените специальные типы XML-индексов.

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

ОБЪЯВИТЬ @xmldata XML SELECT @xmldata = BulkColumn FROM OPENROWSET (BULK 'D: \ DataXML \ NBP \ a051z140314.xml', S IN GLE_BLOB) как T1 - Использование узлов XQuery - meotda () разделяет табличку SELECT. Элемент .value ('code_value [1]', 'char (3)') как code_value, Tab. элемент .value ('../data_publikacji[11)', 'varchar (10)') как data_publikacji, Cast (Заменить (Plate. element .value ('course_social [1]', 'varchar (10)'), ') , ','. ') как smallmoney) как average_transition, Tab. элемент .value ('resolver [1]', 'int') как преобразование -, Tabliczka.element.value ('.././@type [1]', 'varchar (10)') как тип FROM @ XMLDATA. узлы ('/ tabela_kursow / item') как Tab (элемент) - метод query () возвращает фрагмент XML-документа SELECT @xmldata. query ('/ tabela_kursow / pozycka [lot_code = "USD"] / average_transfer) как Query - значение метода () - скалярное значение SELECT @xmldata. значение ('(/ tabela_kursow / miejscu [sam_code = "USD"] / average_directorial) [1]', 'varchar (10)') как значение

Работа метода node () аналогична табличным функциям, поэтому мы можем использовать ее в FROM. Он превратил документ XML в коллекцию более мелких элементов. Чтобы лучше представить, каждый из них может выглядеть примерно так:

<валюта_имя> доллар США </ валюта_имя> <конверсия_значение> 1 </ конверсия> <код валюты> доллар США </ код_валюты> <средняя_трата> 3 0481 </ средний_куррент>

Это упрощает метод поиска и ссылки на фрагменты документа XML.

Что очень важно - массив элементов (Plate), созданный в результате узлов (), не составляет отдельных документов. Каждый из них обрабатывается в контексте основного документа . Лучшая интерпретация операции node () заключается в создании вектора узлов (указателей) внутри основного документа.

В узлах, созданных этой функцией, мы можем охватить любые элементы, например, более высокие в иерархии - data_publication и тип атрибута (это прокомментировано в этом примере).

Еще два запроса - простые примеры использования методов query () и value () для поиска элементов с определенным значением.

Поиск документов XML с определенным пространством имен

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

Используя нативные методы, мы можем определять их по-разному. Каждый из следующих запросов вернет правильный результат:

ИСПОЛЬЗОВАНИЕ AdventureWorks2008 GO - way 1 SELECT hj. Возобновить .value ('объявить пространство имен MyNS = "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; (/ MyNS: Resume / MyNS: Name / MyNS: Name.First) [ 1] ',' nvarchar (100) ') как FirstName, hj. Возобновить .value ('объявить пространство имен MyNS = "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; (/ MyNS: Resume / MyNS: Name / MyNS: Name.Last) [ 1] ',' nvarchar (100) ') как LastName FROM [HumanResources]. [JobCandidate] hj; - метод 2 С XMLNAMESPACES (N'http: //schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume 'as MyNS) ВЫБРАТЬ hj. Возобновить .value ('(/MyNS:Resume/MyNS:Name/MyNS:Name.First)[1]', 'nvarchar (100)') как FirstName, hj. Возобновить .value ('(/MyNS:Resume/MyNS:Name/MyNS:Name.Last)[1]', 'nvarchar (100)') как LastName FROM [HumanResources]. [JobCandidate] hj; - метод 3 С XMLNAMESPACES (ПО УМОЛЧАНИЮ N 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume') ВЫБРАТЬ hj. Возобновить .value ('(/Resume/Name/Name.First)[1)', 'nvarchar (100)') как FirstName, hj. Возобновить .value ('(/Resume/Name/Name.Last)[1)', 'nvarchar (100)') как LastName FROM [HumanResources]. [JobCandidate] HJ

В свою очередь, определение пространства имен в примере, использующем функцию OPENXML, сводится к предоставлению этой информации во время подготовки документа, то есть к вызову процедуры sp_xml_preparedocument. Опционально принимает дополнительный параметр.

Поскольку функция OPENXML () тесно связана с хранимыми процедурами, применение ее для достижения аналогичного результата (чтение нескольких объектов XML) требует действий с курсорами.

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

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

DECLARE @idoc int, @rootxmlns varchar (100) DECLARE @doc xml ВЫБРАТЬ ТОП 1 @doc = Возобновить ОТ [HumanResources]. [JobCandidate] hj SET @rootxmlns = '<root xmlns: MyNS = "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume" />' EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, @rootxmlns SELECT * FROM OPENXML (@idoc, '/ MyNS: Resume / MyNS: Name', 1) WITH (FirstName nvarchar (100) './MyNS:Name.First', LastName nvarchar (100) './MNS: Name.Last ') EXEC sp_xml_removedocument @idoc GO

суммирование

Вы можете написать много статей о языке запросов XQuery и самом XML. По определению, эта глава должна была быть кратким введением и представлять готовые решения для запросов к документам XML. Вот почему я ограничился несколькими базовыми примерами, использующими функцию OPENXML и методы типа данных XML - node (), value () и query (). Если вы ищете более широкие знания XML - я приглашаю вас на мои курсы.

Похожие

Комментарии