/img/star (2).png.webp)
/img/news (2).png.webp)
/img/event.png.webp)
Время прочтения: 4 мин.
В данной публикации рассматривается метод по парсингу информации из xml полей в таблицах различных баз данных в СУБД MS SQL.
В таблицах баз данных встречаются текстовые поля nvarchar(max), которые содержат в себе xml-формат представления данных. Фактически это таблица в таблице со своими столбцами и строками. В качестве примера попробую преобразовать в табличный вид xml строки, пример одной xml строки в таблице ниже:
<description>
<text>Детальная информация:</text>
<grid>
<columns>
<column>
<name>c1</name>
<title>Номер счета</title>
</column>
<column>
<name>c2</name>
<title>ID</title>
</column>
<column>
<name>c3</name>
<title>Номер договора</title>
</column>
<column>
<name>c4</name>
<title>Дата договора</title>
</column>
<column>
<name>c5</name>
<title>Страховой продукт</title>
</column>
<column>
<name>c6</name>
<title>Отсутствующие документы по страховке</title>
</column>
<column>
<name>c7</name>
<title>ФИО сотрудника</title>
</column>
</columns>
<data>
<row>
<c1>**********822</c1>
<c2>921921921921_1</c2>
<c3>123456789_1</c3>
<c4>2022-08-10</c4>
<c5>ДСЖ</c5>
<c6>Заявление на страхование и Поручение владельца счета</c6>
<c7>Фамилия Имя Отчество</c7>
</row>
<row>
<c1>**********823</c1>
<c2>921921921921_2</c2>
<c3>123456789_2</c3>
<c4>2022-08-11</c4>
<c5>ДСЖ</c5>
<c6>Заявление на страхование и Поручение владельца счета</c6>
<c7>Фамилия2 Имя2 Отчество2</c7>
</row>
</data>
</grid>
</description>
Видно, что xml-строки в таблице содержат в себе более 1 строки. Разделение строк в xml идет через <row></row>.
Представим, что данные xml хранятся в таблице xml_data в виде текста в атрибуте xml_type_nv.
Структура таблицы xml_data:
Id (int),
xml_type_nv (nvarchar(max)),
comment (nvarchar(255)
Можно заметить, что в рамках одной строки таблицы имеется id (в нашем случае нумерация), а в поле comment хранится комментарий к одному из xml-файлов.
Благодаря скрипту ниже, реализованному на MS SQL, можно преобразовать xml в табличный вид, что позволяет более оперативно обработать данные и связать их с таблицами в базе данных.
declare @a int; -- вводим переменную a (для цикла)
declare @b int; -- вводим переменную b (для цикла)
declare @docHandle int; -- вводим переменную для функции openxml
declare @xmlDoc nvarchar(max); -- вводим переменную для работы с xml
set @a = 1; -- значение переменной a в цикле
set @b = 1; -- значение переменной b в цикле
while @b >= @a –- когда b будет >= a, тогда работа скрипта закончится.
begin
set @xmlDoc = (select [xml_type_nv] from [SANDBOX].[User].[xml_data] q where q.[id] = @a); --- счетчик, берет из таблицы [xml_data] по 1 xml.
exec sp_xml_preparedocument @docHandle output, @xmlDoc;
with xml_need as (
select *, lag([text]) over (order by id,parentid) www
from openxml(@docHandle, N'/description/grid/data/row') – задаем уровень вложенности в xml, фактический то, что будет идти в таблицу.
),
t_done as (
select
*,
ROW_NUMBER() over (order by xml_s) ro, -- сортировка для учета сдвига от строк
@a as global_ro -- id из таблицы [xml_data] для соединения
from (
select
b.localname,
a.[text],
(select count(*) from (select * from (
select parentid, [text] from xml_need) a
join (select id, localname from (select *, lag([text]) over (order by id,parentid) www
from openxml(@docHandle, N'/description/grid/data/row')) a) b
on a.parentid = b.id where localname = 'c1') eee) as xml_s -- количество строк в xml (по количеству вхождений c1 атрибута)
from (
select parentid, [text] from xml_need) a
join (select id, localname from (select *, lag([text]) over (order by id,parentid) www
from openxml(@docHandle, N'/description/grid/data/row')) a) b
on a.parentid = b.id --- построение связи xml, на этом этапе данные уже почти готовы, однако их требует транспонировать
) v
)
--insert into [SANDBOX].[User].[xml_data_done]
select *
--into [SANDBOX].[User].[xml_data_done]
from (
select
localname,
case
when localname = 'c1'
then [text]
end as 'c1 (Номер счета)',
lead(
case when localname = 'c2'
then [text]
end
, 1) over (order by ro)
as 'c2 (ID)',
lead(
case when localname = 'c3'
then [text]
end
, 2) over (order by ro)
as 'c3 (Номер договора)',
lead(case when localname = 'c4'
then [text]
end, 3) over (order by ro) as 'c4 (Дата договора)',
lead(case when localname = 'c5'
then [text]
end, 4) over (order by ro) as 'c5 (Страховой продукт)',
lead(case when localname = 'c6'
then [text]
end, 5) over (order by ro) as 'c6 (Отсутствующие документы по страховке)',
lead(case when localname = 'c7'
then [text]
end, 6) over (order by ro) as 'c7 (ФИО сотрудника)',
xml_s as 'количество строк',
global_ro as 'id в таблице [xml_data]'
from t_done
where localname <> 'row'
) w
where [c1 (Номер счета)] is not NULL
set @a += 1;
end
Итоговый выход данных в таблице [SANDBOX].[User].[xml_data_done]:
Видно, что все переменные связались верно, далее с таблицей [xml_data] можно связать таблицу [SANDBOX].[User].[xml_data_done] по ключу id и достать комментарии.
При использовании скрипта требуется помнить, что вложенность xml может отличаться, поэтому в следующем коде
from openxml(@docHandle, N'/description/grid/data/row')) a) bвложенность description/grid/data/row подлежит замене на нужную вложенность.
Также localname может иметь другие имена полей и подлежит замене на необходимые названия полей.