Openxml - xml integration with sql server

Сохранение XML-данных в Oracle XML DB

Для сохранения и управления XML-данными в реляционной таблице применяется специальный тип данных XMLType. Этот тип, а также тип XDBURIType, который представляет собой еще один встроенный тип, предназначенный для работы с данными XML, позволяет возлагать ответственность за выполнение синтаксического анализа, хранение и извлечение XML-данных на базу данных Oracle. Использовать тип XMLType можно точно так же, как и обычные типы данных в базе данных Oracle. Благодаря ему, правильно оформленный XML-документ теперь можно сохранять в базе данных в виде XML-теста с использованием базового типа данных CLOB.

Ниже приведен пример применения типа данных XMLType:

Тип данных XMLType поставляется с набором специальных XML-методов, которые можно использовать для работы с объектами XMLType. Эти методы можно применять для выполнения как типичных операций в базе данных, наподобие проверки на предмет существования узла или извлечения узла, так и нескольких специальных операций, позволяющих получать доступ к XML-данными и манипулировать ими в виде части обычного SQL-оператора. Эти специальные операции являются отражением развивающегося стандарта SQL/XML. За счет использования уже хорошо известной нотации XPath, они осуществляют обход по структурам XML и отыскивают тот узел или узлы, к которым им следует применить указанные SQL-операторы. Ниже перечислены некоторые наиболее важные операции SQL/XML.

Extract(). Извлекает подмножество узлов, содержащихся в XMLType.
ExistsNode(). Проверяет, существует ли конкретный узел в XMLType.
Validating(). Проверяет правильность содержимого XMLType по схеме XML.
Transform(). Выполняет XSL-преобразование.
ExtractValue(). Возвращает узел, соответствующий выражению XPath.

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

Сохранять XML-данные в XML-базе данных Oracle можно двумя способами.

С использованием SQL или PL/SQL для вставки данных. Такой подход подразумевает сначала преобразование исходных данных в экземпляр XMLType с помощью конструкторов XMLType.
С использованием репозитория Oracle XML DB для сохранения XML-данных.

Ниже приведен простой пример, в котором для демонстрации выполнения основанной на SQL операции DML в отношении поддерживающей XML таблицы используется таблица sales_catalog_table. В листинге 2 в эту таблицу вставляется XML-документ.

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

После создания из таблицы sales_catalog_table можно очень легко извлекать данные с помощью любого из описанных выше методов. Ниже показан пример применения метода extract(). В этом примере выражения XPath и операции SQL/XML extractValue и existsNode применяются для поиска названия того отправления, у которого в узле SalesOrder/SalesLocation/text() содержится значение Dallas.

What is OpenXML

SQL Server 2000 provides a system-defined function, OpenXML, that creates Rowsets from XML documents.
OPENXML allows the data in XML document to be treated just like the columns and rows of your database table
The OPENXML function allows developers to parse XML data so that it can be stored as relational data in tabular form. This function supports the XML data type and the sp_xml_preparedocument system stored procedure accepts this new data type. This procedure is used by OPENXML function. Whereas SQL Server 2000 allowed the use of varchar, nvarchar, text or ntext variables to generate a document handle using the abovementioned stored procedure, SQL Server 2005 allows developer to use the xml variable additionally.

JSON_Value, JSON_Query, JSON_Modify

In :

declare @json nvarchar(4000) 
set @json =	N'
{
    "FirstName": "Илья",
    "LastName": "Гавриленко",
    "YearOfBirth": 2006,
    "Gender": "м",
    "Club": {
        "Name": "ГЦОР Трактор",
        "City": "Минск"
    },
    "Category": "II",
    "Coach": 
} 
'

select FirstName   = json_value(@json, '$.FirstName'),
       LastName    = json_value(@json, '$.LastName'),
       YearOfBirth = json_value(@json, '$.YearOfBirth'),
       Team        = json_value(@json, '$.Club.City') + ' ' + json_value(@json, '$.Club.Name'),
       YearOfBirth = json_query(@json, '$.Coach')   --json_value не подходит

(1 row affected)
Total execution time: 00:00:00.0046983

Out :

In :

declare @json nvarchar(4000) 
set @json =	N'
{
    "FirstName": "Илья",
    "LastName": "Гавриленко",
    "YearOfBirth": 2006,
    "Gender": "м",
    "Club": {
        "Name": "ГЦОР Трактор",
        "City": "Минск"
    },
    "Category": "II",
    "Coach": 
} 
'

--корректируем ошибку в фамилии тренера
set @json = json_modify(@json, '$.Coach.LastName', N'Барташевич')   

--добавляем дополнительный атрибут
set @json = json_modify(@json, '$.Club.Address', N'Долгобродская улица, 37. ст.м. Тракторный Завод')   

--добавляем дополнительный элемент в массив
set @json = json_modify(
                @json, 
                'append $.Coach', 
                json_query(N'{"FirstName": "Наталья", "LastName": "Бученкова"}', '$')
            )   

select @json
(1 row affected)
Total execution time: 00:00:00.0081801

Out :

{
"FirstName": "Илья",
"LastName": "Гавриленко",
"YearOfBirth": 2006,
"Gender": "м",
"Club": { "Name": "ГЦОР Трактор", "City": "Минск" ,"Address":"Долгобродская улица, 37. ст.м. Тракторный Завод"},
"Category": "II",
"Coach": 
}

Автор материала – Тимофей Гавриленко, преподаватель Тренинг-центра ISsoft.

Образование: окончил с отличием математический факультет Гомельского Государственного Университета им. Франциска Скорины.

Microsoft Certified Professional (70-464, 70-465).

Работа: c 2011 года работает в компании ISsoft (ETL/BI Developer, Release Manager, Data Analyst/Architect, SQL Training Manager), на протяжении 10 лет до этого выступал как Sysadmin, DBA, Software Engineer.

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

Преобразование данных

Последнее обновление: 29.07.2017

Когда мы присваиваем значение одного типа столбцу, который хранит данные другого типа, либо выполняем операции, которые вовлекают данные разных типов,
SQL Server пытается выполнить преобразование и привести используемое значение к нужному типу. Но не все преобразования SQL Server может выполнить автоматически.
SQL Server может выполнять неявные преобразования от типа с меньшим приоритетом к типу с большим приоритетом. Таблица приоритетов (чем выше, тем больший приоритет):

datetime

smalldatetime

float

real

decimal

money

smallmoney

int

smallint

tinyint

bit

nvarchar

nchar

varchar

char

То есть SQL Server автоматически может преобразовать число 100.0 (float) в дату и время (datetime).

В тех случаях, когда необходимо выполнить преобразования от типов с высшим приоритетом к типам с низшим приоритетом, то надо выполнять явное приведение типов.
Для этого в T-SQL определены две функции: CONVERT и CAST.

Функция CAST преобразует выражение одного типа к другому. Она имеет следующую форму:

CAST(выражение AS тип_данных)

Для примера возьмем следующие таблицы:

CREATE TABLE Products
(
    Id INT IDENTITY PRIMARY KEY,
    ProductName NVARCHAR(30) NOT NULL,
    Manufacturer NVARCHAR(20) NOT NULL,
    ProductCount INT DEFAULT 0,
    Price MONEY NOT NULL
);
CREATE TABLE Customers
(
    Id INT IDENTITY PRIMARY KEY,
    FirstName NVARCHAR(30) NOT NULL
);
CREATE TABLE Orders
(
    Id INT IDENTITY PRIMARY KEY,
    ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
    CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
    CreatedAt DATE NOT NULL,
    ProductCount INT DEFAULT 1,
    Price MONEY NOT NULL
);

Например, при выводе информации о заказах преобразует числовое значение и дату в строку:

SELECT Id, CAST(CreatedAt AS nvarchar) + '; total: ' + CAST(Price * ProductCount AS nvarchar) 
FROM Orders

Convert

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

CONVERT(тип_данных, выражение )

Третий необязательный параметр задает стиль форматирования данных. Этот параметр представляет числовое значение, которое для разных типов данных имеет разную интерпретацию.
Например, некоторые значения для форматирования дат и времени:

или — формат даты «Mon dd yyyy hh:miAM/PM» (значение по умолчанию)
или — формат даты «mm/dd/yyyy»
или — формат даты «dd/mm/yyyy»
или — формат даты «Mon dd, yyyy hh:miAM/PM»
или — формат даты «hh:mi:ss»
или — формат даты «mm-dd-yyyy»
или — формат даты «hh:mi:ss:mmmm» (24-часовой формат времени)

Некоторые значения для форматирования данных типа money в строку:

— в дробной части числа остаются только две цифры (по умолчанию)
— в дробной части числа остаются только две цифры, а для разделения разрядов применяется запятая
— в дробной части числа остаются только четыре цифры

SELECT CONVERT(nvarchar, CreatedAt, 3), 
	   CONVERT(nvarchar, Price * ProductCount, 1) 
FROM Orders

TRY_CONVERT

При использовании функций CAST и CONVERT SQL Server выбрасывает исключение, если данные нельзя привести к определенному типу. Например:

SELECT CONVERT(int, 'sql')

Чтобы избежать генерации исключения можно использовать функцию TRY_CONVERT. Ее использование аналогично функции CONVERT за тем исключением, что
если выражение не удается преобразовать к нужному типу, то функция возвращает NULL:

SELECT TRY_CONVERT(int, 'sql')		-- NULL
SELECT TRY_CONVERT(int, '22')		-- 22

Дополнительные функции

Кроме CAST, CONVERT, TRY_CONVERT есть еще ряд функций, которые могут использоваться для преобразования в ряд типов:

STR(float ]): преобразует число в строку. Второй параметр указывает на длину строки, а третий — сколько знаков в дробной части числа надо оставлять
CHAR(int): преобразует числовой код ASCII в символ. Нередко используется для тех ситуаций, когда необходим символ, который нельзя ввести с клавиатуры
ASCII(char): преобразует символ в числовой код ASCII
NCHAR(int): преобразует числовой код UNICODE в символ
UNICODE(char): преобразует символ в числовой код UNICODE

SELECT STR(123.4567, 6,2)	-- 123.46
SELECT CHAR(219)			--  Ы
SELECT ASCII('Ы')			-- 219
SELECT NCHAR(1067)			-- Ы
SELECT UNICODE('Ы')		-- 1067

НазадВперед

Introduction to XML

Extensible Markup Language(XML) is a simplified markup language. It is an open-source W3C standard language and mostly used to store and transport data. It was derived from the Standard Generalized Markup Language (SGML). It is different from the HTML but has some similarities with it.

HTML is static while XML is in dynamic in nature
In HTML, we use pre-defined tags while XML uses user-defined tags
XML is a case sensitive language while HTML is not
HTML focuses on the data presented while XML focus is on storing and transporting data

XML Schema Definition(XSD) is used to validate, format and describe an XML file. A document should be validated against the XSD to be a valid XML file. An XML document is valid if the XSD defines the tags, attributes for it. XSD is also a recommendation of W3C (World Wide Web Consortium)

Sometimes, we work with the various data format and gets the requirement to load the data into SQL Server. Source of data might be a different relational database, Excel, CSV, HTML.

We might get a requirement to load XML data into SQL Server tables. In the article, Working with XML Data in SQL Server, we talked about SQL Server data into XML formats. I encourage you to go through this article to understand data conversion in XML format from SQL tables.

SQL Server Integration package (SSIS Package) is an ETL (Extract-Transform-Load) tool to work with complex data structures, transform and load into SQL Server or any other destinations. In this article, we will explore the process of using an SSIS package to load XML files into SQL Server tables.

2 ответа

Лучший ответ

Это потому, что вы пытаетесь получить данные как атрибуты, но int ваши данные xml находятся внутри элементов. Попробуйте следующее:

Или вы можете сделать это без openxml:

07 окт. 2013, в 03:27
Поделиться

Проблема — это «значение элемента» vs «атрибут элемента».

На этой странице есть хорошие примеры:

07 окт. 2013, в 04:41
Поделиться

Ещё вопросы

1Выдача SSL для серверов Weblogic по HTTPS
1Отправка MMS в Android
Анализ ответа ASMX с помощью PHP и SimpleXML
Углы повторения, ошибки формы и проверки
1Как мне сообщить Джерси, где находится мой MessageBodyWriter?
Бинарное дерево поиска
1Цепочка обещаний не в состоянии
1LINQ OrderBy с несколькими слоями
Как я могу проверить разметку HTML с помощью JavaScript, а затем показать ее предварительный просмотр?
Двоичное дерево поиска остается пустым?
1Пытаясь понять Sax Parser Android
1Формат в JSON с регулярным выражением
1Как получить активность входа в систему только один раз, когда я запускаюсь в первый раз, и в следующий раз должны появиться разные действия
1Как правильно переназначить мое пространство имен XAML?
Mysql update auto count по количеству обновлений строк
PHPMailer не может отправить письмо
1Общая переменная между приложением C # и приложением VBA
Angular — вызов функции, когда все JS загружены
Magento — способ доставки carrier.php
Настройте пользовательскую дату для использования в качестве отправной точки в устройстве выбора даты
1Как получить определенный дочерний элемент (ы) из узла базы данных Firebase вместо моментального снимка всего в узле?
1MapView не отображает плитки — стратегии отладки?
1Вызов PICK_CONTACT Намерение не от Действия
1Есть ли опасность принудительного добавления исключения во время выполнения в поток с Thread.stop (Throwable)?
Как заставить Doctrine 1.2 запускать Versionable Behavior при обновлении на некоторых полях?
1Python Flask: нажатие кнопки ничего не делает
1Как читать и записывать данные в P6DB через веб-сервис P6 с использованием C #
Запрос = неизвестная системная переменная ‘c3’
Конструкторы C ++, константы, наследование и предотвращение повторения
1Maven-зависимость-плагин скопировать пакет в несколько мест
1Ошибка преобразования при преобразовании значения varchar ‘Password’ в тип данных int
1Использование python для создания гистограммы с нечисловой осью X
Как создать уникальную область видимости каждый раз, когда вызывается директива (AngularJS)
1Jbutton не очищает мои текстовые поля
Почему простая функция щелчка не находит селектор?
1Реализация алгоритма SAD (сумма абсолютных разностей)
1Summernote JS — ввод кода и параметров в одной и той же инициализации
1Python Recursive Call
шифрование пароля в скрипте оболочки
1Скопировать часть холста в изображение
1добавление картинок flickr в галерею Android и их отображение
Синтаксическая ошибка AngularJS / ожидаемое выражение?
1ListActivity добавление изображений?
1Добавить массив в gSheets?
вид не обновляется в угловых JS, когда я прохожу динамические идентификаторы
Печать данных из многомерного массива
1Создание и вставка документов с использованием асинхронного цикла while
1Выполнение процесса в Java с паролем
ошибка подключения к функции mysql в python
1ограничения интерфейса смартфона

[Розница 8. Аптека] Загрузка приходных накладных из файлов DBF Промо

Внешняя обработка для загрузки документов «Поступление товаров» из файлов поставщиков в формате DBF для Розница 8. Аптека редакция 2.1.
Для работы с дополнительными реквизитами и сведениями используется БДРС: http://infostart.ru/public/288722/
Для купивших БДРС предусмотрена скидка (второй файл — …без БДРС — это для Вас).
При написании использовался шаблон http://infostart.ru/public/303564/. Описание настройки интерфейса и регистрации обработки подробно можно посмотреть там.

20.03.2018 — Версия 3.2 для Розница — Аптека 2.2 (используется новая версия БДРС)

2 стартмани

Регулярные выражения

И последнее и наименее рекомендованное — можно извлекать данные из HTML с помощью регулярных выражений . В целом использование регулярных выражений в HTML не рекомендуется.

Большинство фрагментов кода, которые вы найдете в интернете для соответствия разметке, некорректны. В большинстве случаев они работают только с определенными фрагментами HTML. Небольшие изменения разметки, такие как добавление пробелов, добавление или изменение атрибутов в теге, могут привести к сбою RegEx, если он неправильно написан. Вы должны знать, что делаете, прежде чем использовать RegEx в HTML.

Парсеры HTML уже имеют синтаксические правила HTML. Регулярные выражения необходимо обучать для каждого конкретного случая. В некоторых случаях RegEx подходят, но это действительно зависит от вашего варианта использования.

Преобразование табличных данных с помощью JSON

Выражение (текст JSON на основе UNICODE)Путь (выражение пути JSON, необязательный аргумент, используемый для указания фрагмента входного выражения)

Msg 208, Level 16, State 1, Line 78
Invalid object name ‘OPENJSON’.
(Неверное имя объекта ‘OPENJSON’.)

Key (имя свойства или индекс элемента, тип столба — NOT NULL VARCHAR(4000));
Value (значение свойства или индекса элемента, тип столбца — NOT NULL NVARCHAR(MAX));
Type (тип данных JSON-значения, тип столбца — TINYINT).

OPENJSON

DECLARE @JSON NVARCHAR(MAX) = N'
{
	"JobTitle": "Chief Executive Officer",
	"City": "Newport Hills",
	"PersonName": {
		"FirstName": "Ken",
		"LastName": "Sánchez"
	}
}';
SELECT * FROM OPENJSON(@JSON)

DECLARE @JSON NVARCHAR(MAX) = N'
{
	"JobTitle": "Chief Executive Officer",
	"City": "Newport Hills",
	“SOMETHING_WRONG”,
	"PersonName": {
		"FirstName": "Ken",
		"LastName": "Sánchez"
	}
}';
SELECT * FROM OPENJSON(@JSON)

DECLARE @JSON NVARCHAR(MAX) = N'
{
	"JobTitle": "Chief Executive Officer",
	"City": "Newport Hills",
	"PersonName": {
		"FirstName": "Ken",
		"LastName": "Sánchez"
	}
}';
SELECT * FROM OPENJSON(@JSON, '$.PersonName')

DECLARE @JSON NVARCHAR(MAX) = N'
{
	"JobTitle": "Chief Executive Officer",
	"City": "Newport Hills",
	"PersonName": {
		FirstName: "Ken",
		"LastName": "Sánchez"
	}
}';
SELECT * FROM OPENJSON(@JSON, '$.PersonName')

USE AdventureWorks2019
DECLARE @NationalIDNumberIDs AS VARCHAR(100) = '295847284,245797967';
SELECT 
      ,
      ,
      ,
      ,
  FROM . Emp
    INNER JOIN ( SELECT value FROM OPENJSON('') ) 
                   AS JSONTbl ON Emp.NationalIDNumber = JSONTbl.value;

dbForge SQL Complete

Аргументы

idoc
Дескриптор документа внутреннего представления XML-документа. Внутреннее представление XML-документа создается путем вызова процедуры sp_xml_preparedocument.

rowpattern
Шаблон XPath, используемый для указания узлов, которые будут обработаны в качестве строк. Узлы поступают из XML-документа, дескриптор которого передается в параметре idoc.

flags
Указывает на сопоставление, используемое между XML-данными и реляционным набором строк, а также на порядок заполнения переполненного столбца. Аргумент flags является необязательным входным параметром и может принимать одно из указанных ниже значений.

Байтовое значение	Описание
По умолчанию используется атрибутивная модель сопоставления.
1	Использовать атрибутивную модель сопоставления. Может быть совмещено с XML_ELEMENTS. В этом случае сначала применяется атрибутивная модель сопоставления. Затем для всех остальных столбцов применяется элементная модель сопоставления.
2	Использовать сопоставление с использованием элементов. Может быть совмещено с XML_ATTRIBUTES. В этом случае сначала применяется атрибутивная модель сопоставления. Затем для всех остальных столбцов применяется элементная модель сопоставления.
8	Может быть совмещено (логическое OR) с XML_ATTRIBUTES или XML_ELEMENTS. В контексте получения этот флаг указывает, что используемые данные не должны копироваться в свойство переполнения @mp:xmltext.

SchemaDeclaration
Определение схемы в следующем формате: ColNameColType …]

ColName
Название столбца в наборе строк.

ColType
Тип данных SQL Server столбца в наборе строк. Если типы столбцов отличаются от базовых типов данных xml атрибута, происходит приведение типов.

ColPattern
Необязательный общий шаблон XPath, который описывает, как узлы XML должны быть сопоставлены столбцам. Если аргумент ColPattern не указан, применяется сопоставление по умолчанию (атрибутивная или элементная модель сопоставления, как указано в аргументе flags).

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

Общий шаблон XPath, заданный как аргумент ColPattern, также поддерживает метасвойства.

MetaProperty
Одно из метасвойств, предоставляемых OPENXML. Если задано свойство MetaProperty, столбец содержит сведения, предоставленные метасвойством. Метасвойства позволяют извлекать сведения (такие как относительное положение и сведения о пространстве имен) об узлах XML. По сравнению с текстовым представлением эти метасвойства позволяют увидеть больше сведений.

TableName
Имя таблицы, которое может быть указано (вместо аргумента SchemaDeclaration), если таблица с необходимой схемой уже существует и не требует никаких шаблонов столбцов.