Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?  [new]
uaggster
Member

Откуда:
Сообщений: 757
Коллеги, приветствую!

Имеется произвольный xml, например такой:
<root>
  <a tp1="test1">
    <b />
    <c>test3</c>
  </a>
  <a tp2="test2">
    <b>
      <c tp3="test3" />
    </b>
  </a>
</root>

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

IDpathvalue
0001/rootNULL
0001/0001/root/aNULL
0001/0001/0001/root/a/@tp1test1
0001/0001/0002/root/a/bNULL
0001/0001/0003/root/a/ctest3
0001/0002/root/aNULL
0001/0002/0001/root/a/@tp2test2
0001/0002/0002/root/a/bNULL
0001/0002/0002/0001/root/a/b/cNULL
0001/0002/0002/0001/0001root/a/b/c/@tp3test3
19 дек 18, 12:20    [21768210]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?  [new]
court
Member

Откуда:
Сообщений: 1956
uaggster,

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

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


IDpathpathvalue
0rootNULL
0/2root/aNULL
0/2/3root/a/@tp1test1
0/2/4root/a/bNULL
0/2/5root/a/ctest3
0/6root/aNULL
0/6/7root/a/@tp2test2
0/6/8root/a/bNULL
0/6/8/9root/a/b/cNULL
0/6/8/9/10root/a/b/c/@tp3test3
19 дек 18, 13:23    [21768294]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
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);
19 дек 18, 13:23    [21768295]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?  [new]
uaggster
Member

Откуда:
Сообщений: 757
Коллеги, спасибо!
invm, отдельное спасибо!
Это именно то, что нужно.

Правда, (потенциально) быстродействие запроса меня ввергает в уныние.
:-)
19 дек 18, 17:59    [21768702]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?  [new]
uaggster
Member

Откуда:
Сообщений: 757
invm, нет, всё таки ошибка!
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('./*'),
  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);

Выдает:
namevalueid
rootNULL1
root/aNULL1/1
root/aNULL1/2
root/a/bNULL1/2/1
root/a/b/cNULL1/2/1/1
root/a/bNULL1/1/1
root/a/ctest31/1/2
root/@tp1tescte1/1
root/@tp3tescte31/2
root/@tp2test21/3
root/a/b/@tp3test31/2/1/4

Проблема тут:
root/@tp1tescte1/1
root/@tp3tescte31/2
root/@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)
Но исправить не могу. Не понимаю как.
20 дек 18, 10:53    [21769239]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
uaggster,

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);
20 дек 18, 12:30    [21769360]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?  [new]
uaggster
Member

Откуда:
Сообщений: 757
invm, и всё равно ошибка :-)

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


root/a/bNULL1/1/1
root/a/ctest31/1/2
root/a/@tp1tescte1/1/1
root/a/@tp3tescte31/1/2

ID одинаковые.
Хотя, наверное, для атрибутов логично иметь одинаковый для всех уровень 0.
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);


Так?
20 дек 18, 12:57    [21769411]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
uaggster
Так?
Если устраивает, то почему нет?

Но, имхо, будет проще через edge-table. к предложил court. Только надо слегка поправить:
;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)  
20 дек 18, 13:18    [21769447]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
Еще вариант
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;
20 дек 18, 14:30    [21769583]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?  [new]
uaggster
Member

Откуда:
Сообщений: 757
invm, нет, крайний вариант не подходит.
Т.к. атрибуты имеют ИД на уровне с тегами, а (ну, логично же?) - они должны иметь ИД на уровне дочернего тега. Т.к. это, по факту, дочерние сущности тега.
Т.е:
0001/0001root/a
0001/0001/0001root/a/@attrib1
0001/0001/0002root/a/@attrib2
0001/0001/0003root/a/c/

т.е., получается, и вариант "так" не подходит!
20 дек 18, 16:08    [21769767]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
uaggster
Т.к. атрибуты имеют ИД на уровне с тегами, а (ну, логично же?) - они должны иметь ИД на уровне дочернего тега.
А если нет дочернего тега?
20 дек 18, 16:56    [21769849]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?  [new]
uaggster
Member

Откуда:
Сообщений: 757
invm, ну... атрибут - своего рода "дочерний тег".
Разве нет?
21 дек 18, 07:45    [21770201]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
uaggster
invm, ну... атрибут - своего рода "дочерний тег".
Разве нет?
Да. Так он и имеет ИД на этом уровне.
Мне пока не понятно, что не так с результатом.
21 дек 18, 08:56    [21770215]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?  [new]
uaggster
Member

Откуда:
Сообщений: 757
invm
Мне пока не понятно, что не так с результатом

Речь об этом:
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;


idnamevalue
0000000001rootNULLOk
0000000001/0000000001root/aNULLOk
0000000001/0000000001/0000000001root/a/bNULLOk
0000000001/0000000001/0000000002root/a/ctest3Ok
0000000001/0000000002root/aNULLOk
0000000001/0000000002/0000000001root/a/bNULLOk
0000000001/0000000002/0000000001/0000000001root/a/b/cNULLOk
0000000001/0000000002/0000000001/0000000002root/a/b/c/@tp3test3Неверно;атрибут @tp3-принадлежит с и должен иметь ид типа 0000000001/0000000002/0000000001/0000000002/0000000001
0000000001/0000000003root/a/@tp1tescteНеверно;атрибут @tp1-принадлежит первому a и должен иметь ид типа 0000000001/0000000001/0000000000
0000000001/0000000004root/a/@tp3tescte3Неверно;атрибут @tp3-принадлежит первому a и должен иметь ид типа 0000000001/0000000001/0000000001
0000000001/0000000005root/a/@tp2test2Неверно;атрибут @tp2-принадлежит второму a и должен иметь ид типа 0000000001/0000000002/0000000000

Неверные ИД - для примера. Разумеется, конкретные значения должны быть неповторяющимися, с учетом верно обработанных тегов.
21 дек 18, 09:26    [21770233]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли развернуть xml в EAV - таблицу одним универсальным запросом?  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
uaggster,

Да, в этом варианте не выйдет каменный цветок...
21 дек 18, 14:37    [21770535]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить