ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
15 сообщений из 15, страница 1 из 1
Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
    #39750097
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Коллеги, приветствую!

Имеется произвольный xml, например такой:
Код: xml
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
<root>
  <a tp1="test1">
    <b />
    <c>test3</c>
  </a>
  <a tp2="test2">
    <b>
      <c tp3="test3" />
    </b>
  </a>
</root>


Можно ли его преобразовать одним запросом в EAV таблицу?
Должно получиться примерно следующее:

IDpathvalue0001/rootNULL0001/0001/root/aNULL0001/0001/0001/root/a/@tp1test10001/0001/0002/root/a/bNULL0001/0001/0003/root/a/ctest30001/0002/root/aNULL0001/0002/0001/root/a/@tp2test20001/0002/0002/root/a/bNULL0001/0002/0002/0001/root/a/b/cNULL0001/0002/0002/0001/0001root/a/b/c/@tp3test3
...
Рейтинг: 0 / 0
19.12.2018, 12:20
| Ответить | Цитировать | Написать  
Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
    #39750152
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
uaggster,

от чего-то типа такого можно "оттолкнуться"

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
declare @XML_Doc_Handle int
declare @XML_Doc xml=
'<root>
  <a tp1="test1">
    <b />
    <c>test3</c>
  </a>
  <a tp2="test2">
    <b>
      <c tp3="test3" />
    </b>
  </a>
</root>'

--
EXEC sp_xml_preparedocument @XML_Doc_Handle OUTPUT, @XML_Doc;
--
SELECT * into #t
FROM OPENXML (@XML_Doc_Handle, '.', 2)
--
EXEC sp_xml_removedocument @XML_Doc_Handle;

--select * from #t

;with cte as (
	select 
		*
		,IDpath	=cast(id as varchar(max)) 
		,[path]	=cast(localname as varchar(max))
		,value	=cast(null as varchar(max)) 
		,flag	=1 	
	from #t 
	where id=0

	union all

	select
		t.*
		,case when t.localname='#text' then cte.IDpath else cte.IDpath+'/'+cast(t.id as varchar(max)) end
		,case when t.localname='#text' then cte.path else cast(cte.path+'/'+case when t.nodetype=2 then '@' else '' end+t.localname as varchar(max)) end
		,case when t.localname='#text' then cast(t.text as varchar(max)) else null end
		,case when t.localname='#text' then 0 else 1 end
	from #t t
	inner join cte on t.parentid=cte.id 

)
select top 1 with ties 
	IDpath, [path], value 
from cte 
order by row_number()over(partition by IDpath order by flag)  

drop table #t



IDpathpathvalue0rootNULL0/2root/aNULL0/2/3root/a/@tp1test10/2/4root/a/bNULL0/2/5root/a/ctest30/6root/aNULL0/6/7root/a/@tp2test20/6/8root/a/bNULL0/6/8/9root/a/b/cNULL0/6/8/9/10root/a/b/c/@tp3test3
...
Рейтинг: 0 / 0
19.12.2018, 13:23
| Ответить | Цитировать | Написать  
Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
    #39750153
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
declare @x xml = N'<root>
  <a tp1="tescte">
    <b />
    <c>test3</c>
  </a>
  <a tp2="test2">
    <b>
      <c tp3="test3" />
    </b>
  </a>
</root>';

with cte(x, name, value, id) as
(
 select
  t.n.query('./*'),
  t.n.value('local-name(.)', 'nvarchar(max)'),
  t.n.value('text()[1]', 'nvarchar(max)'),
  cast(row_number() over (order by (select 1)) as varchar(max))
 from
  @x.nodes('/*') t(n)

 union all

 select
  t.n.query('./*'),
  a.name + '/' + t.n.value('local-name(.)', 'nvarchar(max)'),
  t.n.value('text()[1]', 'nvarchar(max)'),
  a.id + '/' + cast(row_number() over (order by (select 1)) as varchar(max))
 from
  cte a cross apply
  a.x.nodes('*') t(n)
)
select name, value, id from cte

union all

select
  a.name + '/@' + t.n.value('local-name(.)', 'nvarchar(max)'),
  t.n.value('.', 'nvarchar(max)'),
  a.id + '/' + cast(row_number() over (order by (select 1)) as varchar(max))
from
 cte a cross apply
 a.x.nodes('*/@*') t(n);
...
Рейтинг: 0 / 0
19.12.2018, 13:23
| Ответить | Цитировать | Написать  
Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
    #39750400
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Коллеги, спасибо!
invm , отдельное спасибо!
Это именно то, что нужно.

Правда, (потенциально) быстродействие запроса меня ввергает в уныние.
:-)
...
Рейтинг: 0 / 0
19.12.2018, 17:59
| Ответить | Цитировать | Написать  
Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
    #39750642
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
invm, нет, всё таки ошибка!
Код: xml
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
declare @x xml = N'<root>
  <a tp1="tescte" tp3="tescte3">
    <b />
    <c>test3</c>
  </a>
  <a tp2="test2">
    <b>
      <c tp3="test3" />
    </b>
  </a>
</root>';


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
with cte(x, name, value, id) as
(
 select
  t.n.query('./*'),
  t.n.value('local-name(.)', 'nvarchar(max)'),
  t.n.value('text()[1]', 'nvarchar(max)'),
  cast(row_number() over (order by (select 1)) as varchar(max))
 from
  @x.nodes('/*') t(n)

 union all

 select
  t.n.query('./*'),
  a.name + '/' + t.n.value('local-name(.)', 'nvarchar(max)'),
  t.n.value('text()[1]', 'nvarchar(max)'),
  a.id + '/' + cast(row_number() over (order by (select 1)) as varchar(max))
 from
  cte a cross apply
  a.x.nodes('*') t(n)
)
select name, value, id from cte

union all

select
  a.name + '/@' + t.n.value('local-name(.)', 'nvarchar(max)'),
  t.n.value('.', 'nvarchar(max)'),
  a.id + '/' + cast(row_number() over (order by (select 1)) as varchar(max))
from
 cte a cross apply
 a.x.nodes('*/@*') t(n);


Выдает:
namevalueidrootNULL1root/aNULL1/1root/aNULL1/2root/a/bNULL1/2/1root/a/b/cNULL1/2/1/1root/a/bNULL1/1/1root/a/ctest31/1/2root/@tp1tescte1/1root/@tp3tescte31/2root/@tp2test21/3root/a/b/@tp3test31/2/1/4
Проблема тут:
root/@tp1tescte1/1root/@tp3tescte31/2root/@tp2test21/3

Подозреваю, что проблема тут:
select
t.n.query('./*'),
a.name + '/' + t.n.value('local-name(.)', 'nvarchar(max)'),
t.n.value('text()[1]', 'nvarchar(max)'),
a.id + '/' + cast(row_number() over (order by (select 1)) as varchar(max))
from
cte a cross apply
a.x.nodes('*') t(n)
Но исправить не могу. Не понимаю как.
...
Рейтинг: 0 / 0
20.12.2018, 10:53
| Ответить | Цитировать | Написать  
Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
    #39750715
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
uaggster,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
with cte(x, name, value, id) as
(
 select
  t.n.query('.'),
  t.n.value('local-name(.)', 'nvarchar(max)'),
  t.n.value('text()[1]', 'nvarchar(max)'),
  cast(row_number() over (order by (select 1)) as varchar(max))
 from
  @x.nodes('/*') t(n)

 union all

 select
  t.n.query('.'),
  a.name + '/' + t.n.value('local-name(.)', 'nvarchar(max)'),
  t.n.value('text()[1]', 'nvarchar(max)'),
  a.id + '/' + cast(row_number() over (order by (select 1)) as varchar(max))
 from
  cte a cross apply
  a.x.nodes('*/*') t(n)
)
select name, value, id from cte

union all

select
  a.name + '/@' + t.n.value('local-name(.)', 'nvarchar(max)'),
  t.n.value('.', 'nvarchar(max)'),
  a.id + '/' + cast(row_number() over (partition by a.id order by (select 1)) as varchar(max))
from
 cte a cross apply
 a.x.nodes('*/@*') t(n);
...
Рейтинг: 0 / 0
20.12.2018, 12:30
| Ответить | Цитировать | Написать  
Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
    #39750753
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
invm, и всё равно ошибка :-)

namevalueidrootNULL1root/aNULL1/1root/aNULL1/2root/a/bNULL1/2/1root/a/b/cNULL1/2/1/1root/a/bNULL1/1/1root/a/ctest31/1/2root/a/@tp1tescte1/1/1root/a/@tp3tescte31/1/2root/a/@tp2test21/2/1root/a/b/c/@tp3test31/2/1/1/1

root/a/bNULL1/1/1root/a/ctest31/1/2root/a/@tp1tescte1/1/1root/a/@tp3tescte31/1/2
ID одинаковые.
Хотя, наверное, для атрибутов логично иметь одинаковый для всех уровень 0.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
with cte(x, name, value, id) as
(
 select
  t.n.query('.'),
  t.n.value('local-name(.)', 'nvarchar(max)'),
  t.n.value('text()[1]', 'nvarchar(max)'),
  cast(row_number() over (order by (select 1)) as varchar(max))
 from
  @x.nodes('/*') t(n)

 union all

 select
  t.n.query('.'),
  a.name + '/' + t.n.value('local-name(.)', 'nvarchar(max)'),
  t.n.value('text()[1]', 'nvarchar(max)'),
  a.id + '/' + cast(row_number() over (order by (select 1)) as varchar(max))
 from
  cte a cross apply
  a.x.nodes('*/*') t(n)
)
select name, value, id from cte

union all

select
  a.name + '/@' + t.n.value('local-name(.)', 'nvarchar(max)'),
  t.n.value('.', 'nvarchar(max)'),
  a.id + '/0/' + cast(row_number() over (partition by a.id order by (select 1)) as varchar(max))
from
 cte a cross apply
 a.x.nodes('*/@*') t(n);



Так?
...
Рейтинг: 0 / 0
20.12.2018, 12:57
| Ответить | Цитировать | Написать  
Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
    #39750775
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
uaggsterТак?Если устраивает, то почему нет?

Но, имхо, будет проще через edge-table. к предложил court . Только надо слегка поправить:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
;with cte as (
	select 
		*
		,IDpath	=cast(row_number() over (order by (select 1)) as varchar(max)) 
		,[path]	=cast(localname as varchar(max))
		,value	=cast(null as varchar(max)) 
		,flag	=1 	
	from #t 
	where id=0

	union all

	select
		t.*
		,case when t.localname='#text' then cte.IDpath else cte.IDpath+'/'+cast(row_number() over (order by (select 1)) as varchar(max)) end
		,case when t.localname='#text' then cte.path else cast(cte.path+'/'+case when t.nodetype=2 then '@' else '' end+t.localname as varchar(max)) end
		,case when t.localname='#text' then cast(t.text as varchar(max)) else null end
		,case when t.localname='#text' then 0 else 1 end
	from #t t
	inner join cte on t.parentid=cte.id 

)
select top 1 with ties 
	IDpath, [path], value 
from cte 
order by row_number()over(partition by IDpath order by flag)
...
Рейтинг: 0 / 0
20.12.2018, 13:18
| Ответить | Цитировать | Написать  
Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
    #39750847
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Еще вариант
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
with cte(x, name, value, id) as
(
 select
  t.n.query('*'),
  case when n.pn > '' then n.pn + '/@' + n.ln else n.ln end,
  v.v,
  right('0000000000' + cast(row_number() over (order by (select 1)) as varchar(max)), 10)
 from
  @x.nodes('/*, /*/@*') t(n) cross apply
  (select t.n.value('local-name(.)', 'nvarchar(max)'), t.n.value('local-name(..)', 'nvarchar(max)')) n(ln, pn) cross apply
  (select case when n.pn > '' then t.n.value('.', 'nvarchar(max)') else t.n.value('./text()[1]', 'nvarchar(max)') end) v(v)

 union all

 select
  t.n.query('*'),
  a.name + '/' + case when n.pn > '' then n.pn + '/@' + n.ln else n.ln end,
  v.v,
  a.id + '/' + right('0000000000' + cast(row_number() over (order by (select 1)) as varchar(max)), 10)
 from
  cte a cross apply
  a.x.nodes('/*, /*/@*') t(n) cross apply
  (select t.n.value('local-name(.)', 'nvarchar(max)'), t.n.value('local-name(..)', 'nvarchar(max)')) n(ln, pn) cross apply
  (select case when n.pn > '' then t.n.value('.', 'nvarchar(max)') else t.n.value('./text()[1]', 'nvarchar(max)') end) v(v)
)
select id, name, value from cte order by id;
...
Рейтинг: 0 / 0
20.12.2018, 14:30
| Ответить | Цитировать | Написать  
Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
    #39750929
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
invm, нет, крайний вариант не подходит.
Т.к. атрибуты имеют ИД на уровне с тегами, а (ну, логично же?) - они должны иметь ИД на уровне дочернего тега. Т.к. это, по факту, дочерние сущности тега.
Т.е:
0001/0001root/a0001/0001/0001root/a/@attrib10001/0001/0002root/a/@attrib20001/0001/0003root/a/c/
т.е., получается, и вариант "так" не подходит!
...
Рейтинг: 0 / 0
20.12.2018, 16:08
| Ответить | Цитировать | Написать  
Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
    #39750956
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
uaggsterТ.к. атрибуты имеют ИД на уровне с тегами, а (ну, логично же?) - они должны иметь ИД на уровне дочернего тега.А если нет дочернего тега?
...
Рейтинг: 0 / 0
20.12.2018, 16:56
| Ответить | Цитировать | Написать  
Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
    #39751127
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
invm, ну... атрибут - своего рода "дочерний тег".
Разве нет?
...
Рейтинг: 0 / 0
21.12.2018, 07:45
| Ответить | Цитировать | Написать  
Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
    #39751138
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
uaggsterinvm, ну... атрибут - своего рода "дочерний тег".
Разве нет?Да. Так он и имеет ИД на этом уровне.
Мне пока не понятно, что не так с результатом.
...
Рейтинг: 0 / 0
21.12.2018, 08:56
| Ответить | Цитировать | Написать  
Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
    #39751150
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
invmМне пока не понятно, что не так с результатом
Речь об этом:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
declare @x xml = N'<root>
  <a tp1="tescte" tp3="tescte3">
    <b />
    <c>test3</c>
  </a>
  <a tp2="test2">
    <b>
      <c tp3="test3" />
    </b>
  </a>
</root>';

with cte(x, name, value, id) as
(
 select
  t.n.query('*'),
  case when n.pn > '' then n.pn + '/@' + n.ln else n.ln end,
  v.v,
  right('0000000000' + cast(row_number() over (order by (select 1)) as varchar(max)), 10)
 from
  @x.nodes('/*, /*/@*') t(n) cross apply
  (select t.n.value('local-name(.)', 'nvarchar(max)'), t.n.value('local-name(..)', 'nvarchar(max)')) n(ln, pn) cross apply
  (select case when n.pn > '' then t.n.value('.', 'nvarchar(max)') else t.n.value('./text()[1]', 'nvarchar(max)') end) v(v)

 union all

 select
  t.n.query('*'),
  a.name + '/' + case when n.pn > '' then n.pn + '/@' + n.ln else n.ln end,
  v.v,
  a.id + '/' + right('0000000000' + cast(row_number() over (order by (select 1)) as varchar(max)), 10)
 from
  cte a cross apply
  a.x.nodes('/*, /*/@*') t(n) cross apply
  (select t.n.value('local-name(.)', 'nvarchar(max)'), t.n.value('local-name(..)', 'nvarchar(max)')) n(ln, pn) cross apply
  (select case when n.pn > '' then t.n.value('.', 'nvarchar(max)') else t.n.value('./text()[1]', 'nvarchar(max)') end) v(v)
)
select id, name, value from cte order by id;



idnamevalue0000000001rootNULLOk0000000001/0000000001root/aNULLOk0000000001/0000000001/0000000001root/a/bNULLOk0000000001/0000000001/0000000002root/a/ctest3Ok0000000001/0000000002root/aNULLOk0000000001/0000000002/0000000001root/a/bNULLOk0000000001/0000000002/0000000001/0000000001root/a/b/cNULLOk0000000001/0000000002/0000000001/0000000002root/a/b/c/@tp3test3Неверно;атрибут @tp3-принадлежит с и должен иметь ид типа 0000000001/0000000002/0000000001/0000000002/00000000010000000001/0000000003root/a/@tp1tescteНеверно;атрибут @tp1-принадлежит первому a и должен иметь ид типа 0000000001/0000000001/00000000000000000001/0000000004root/a/@tp3tescte3Неверно;атрибут @tp3-принадлежит первому a и должен иметь ид типа 0000000001/0000000001/00000000010000000001/0000000005root/a/@tp2test2Неверно;атрибут @tp2-принадлежит второму a и должен иметь ид типа 0000000001/0000000002/0000000000
Неверные ИД - для примера. Разумеется, конкретные значения должны быть неповторяющимися, с учетом верно обработанных тегов.
...
Рейтинг: 0 / 0
21.12.2018, 09:26
| Ответить | Цитировать | Написать  
Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
    #39751355
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
uaggster,

Да, в этом варианте не выйдет каменный цветок...
...
Рейтинг: 0 / 0
21.12.2018, 14:37
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]