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

Откуда:
Сообщений: 770
Коллеги приветствую!
Помогите преобразовать в хмл произвольную EAV таблицу:
if OBJECT_ID('tempdb..#eav') is not Null
	drop table #eav

Create table #eav ([id] nvarchar(100), [field] nvarchar(100), [value] nvarchar(100))

insert into #eav
	Values   ('0001', 'root1', Null)
			,('0001/0001', 'fiel1', '1')
			,('0001/0002', 'fiel2', '2')
			,('0001/0002/0001', 'fiel3', '3')
			,('0001/0002/0001/0001', 'fiel5', '5')
			,('0001/0002/0002', 'fiel4', '4')

На выходе должен получиться вот такой xml:
<root>
  <field1>1</field1>
  <field2>
    <field3>
      <field5>5</field5>
3</field3>
    <field4>4</field4>
2</field2>
</root>

Дада, mixed content, и всё такое.
Значение поля [field], в общем случае - произвольное!

Решаема ли такая задача средствами TSQL?
Мне кажется, что не решаема.
Но тогда, может быть, есть возможность что-то добавить в #eav, чтобы она стала решаемой?

Кстати, совершенно необязательно использовать один запрос. Можно динамический sql, или временные таблицы.
Хотелось бы избежать только курсора.
Хотя, если нет другого выхода...
14 фев 19, 14:17    [21809467]     Ответить | Цитировать Сообщить модератору
 Re: Помогите преобразовать EAV таблицу в хмл  [new]
felix_ff
Member

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

drop table if exists #eav, #tmp, #tmp2;
Create table #eav ([id] nvarchar(100), [field] nvarchar(100), [value] nvarchar(100))

insert into #eav
	Values   ('0001', 'root1', Null)
			,('0001/0001', 'field1', '1')
			,('0001/0002', 'field2', '2')
			,('0001/0002/0001', 'field3', '3')
			,('0001/0002/0001/0001', 'field5', '5')
			,('0001/0002/0002', 'field4', '4');

update tmp
   set [id] = '/'+xx.val
  from #eav tmp
      cross apply (select x.data.value('.', 'varchar(max)') from (select cast(cast(t.value as int) as varchar) + '/' from string_split(tmp.id, '/') t for xml path(''), type) x(data)) xx (val);

select cast(id as hierarchyid) as rid, cast(id as hierarchyid).GetAncestor(1) as parentid, * into #tmp from #eav;

alter table #tmp add [rowid] int identity(1,1);


declare @fieldlist varchar(max) = '';
select @fieldlist = @fieldlist + '{val' + cast([rowid] as varchar) + '} as ' + quotename([field]+'!'+cast([rowid] as varchar)+'!') + ', ' from #tmp

select
      iif(t.[rowid] = 1, 'select ', ' union all select ') + cast(t.[rowid] as varchar) +' as [Tag], ' + isnull(cast(p.[rowid] as varchar), 'NULL') +' as [Parent], ' + replace(@fieldlist, '{val' + cast(t.[rowid] as varchar)+'}', isnull(t.value, 'NULL')) as [stmt]
      into #tmp2
from #tmp t
    outer apply (select t2.rowid from #tmp t2 where t2.rid = t.parentid) p;
    
declare @sql varchar(max) = '';
with x (data) as (
    select replace(replace(replace(replace(replace(replace(left(stmt, len(stmt)-1), '{val1}', 'NULL'), '{val2}', 'NULL'), '{val3}', 'NULL'), '{val4}', 'NULL'), '{val5}', 'NULL'), '{val6}', 'NULL')
    from #tmp2 for xml path(''), type
) 
select @sql = data.value('.', 'varchar(max)')
from x;

set @sql += ' for xml explicit';

exec (@sql);


В ближайшем рассмотрении что то типа того для версии >= 2016
14 фев 19, 18:47    [21809857]     Ответить | Цитировать Сообщить модератору
 Re: Помогите преобразовать EAV таблицу в хмл  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
понятное дело что все эти multiple-replace это костыль, думаю можно сделать так что бы не прописывать вручную
14 фев 19, 18:48    [21809859]     Ответить | Цитировать Сообщить модератору
 Re: Помогите преобразовать EAV таблицу в хмл  [new]
court
Member

Откуда:
Сообщений: 1956
if OBJECT_ID('tempdb..#eav') is not Null
	drop table #eav

Create table #eav ([id] nvarchar(100), [field] nvarchar(100), [value] nvarchar(100))

insert into #eav
	Values   ('0001', 'root1', null)
			,('0001/0001', 'fiel1', '1')
			,('0001/0002', 'fiel2', '2')
			,('0001/0002/0001', 'fiel3', '3')
			,('0001/0002/0001/0001', 'fiel5', '5')
			,('0001/0002/0002', 'fiel4', '4')

--	***********************
declare @SQL nvarchar(max)
declare @xml xml

;with cte as (
	select
		[id] 
		,[path]	=cast([field] as varchar(max))
		,lvl	=0
		,[value]
	 from #eav
	 where [id] not like '%/%'

	 union all

	select
		t.[id] 
		,[path]	=cte.[path]+'/'+cast([field] as varchar(max))
		,lvl	=cte.lvl+1
		,t.[value]
	 from #eav t inner join cte on t.id like cte.id+'/%'
	 where len(t.[id])-len(replace(t.[id],'/','')) = cte.lvl+1
)
--select * from cte 

select @SQL=(select ''''+[value]+''' as ['+[path]+'], ' from cte order by [path], lvl for xml path(''))

--print @SQL
set @SQL =N'set @xml=(select '+left(@SQL,len(@SQL)-1)+' for xml path(''''),elements)' 
--print @SQL

--
exec sp_executesql @SQL, N'@xml xml out', @xml=@xml out 

select @xml 
/*
<root1>
	<fiel1>1</fiel1>
	<fiel2>2
		<fiel3>3
			<fiel5>5</fiel5>
		</fiel3>
		<fiel4>4</fiel4>
	</fiel2>
</root1>
*/
14 фев 19, 19:21    [21809888]     Ответить | Цитировать Сообщить модератору
 Re: Помогите преобразовать EAV таблицу в хмл  [new]
uaggster
Member

Откуда:
Сообщений: 770
court, бесподобно!
Большое спасибо. Именно то, что надо.
15 фев 19, 16:11    [21810904]     Ответить | Цитировать Сообщить модератору
 Re: Помогите преобразовать EAV таблицу в хмл  [new]
uaggster
Member

Откуда:
Сообщений: 770
felix_ff, на первый взгляд - это не то, т.к. названия тегов - могут быть абсолютно любые, и их не захардкодишь в запросе.
Однако, попытаюсь вкурить.
Спасибо.
15 фев 19, 16:12    [21810906]     Ответить | Цитировать Сообщить модератору
 Re: Помогите преобразовать EAV таблицу в хмл  [new]
felix_ff
Member

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

по сути идея та же: получить иерархию предок-потомок и потом завернуть в правльный for xml.

только я чет не додумался до создания полей для path, а пошел методом создания таблицы для for xml explicit что накладывает жутко неудобные конструкции именования полей.
15 фев 19, 16:41    [21810958]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить