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

Откуда:
Сообщений: 151
добрый день

есть таблица

create table #tblStructure(
		elementName varchar(255),
		parentElementName varchar(255),
		elementValue varchar(max)
)


надо из нее получить xml вида

<f15A>
  <f20>20</f20>
  <f21>21</f21>
  <f30V>30V</f30V>
</f15A>


желдательно чтобы можно было обрабатывать деревья любой вложенности.
для плоской таблицы делал так

declare	
	@sql varchar(max)


select @sql = 
	(select 
		'''' + elementValue + ''' as [' + elementName + '], ' 
		from #tblStructure
		for xml path(''))
		
print substring(@sql, 1, len(@sql) - 1)		
		
select @sql = 'select ' + char(13) + substring(@sql, 1, len(@sql) - 1) + char(13) + ' for xml path('''')'

execute (@sql) 	
	



Спасибо
9 окт 12, 17:52    [13292204]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
Referee
Member

Откуда:
Сообщений: 151
Пардон...
таблица заполнена данными

insert into #tblStructure(elementName, parentElementName, elementValue)
values ('f15A', null, null)  
insert into #tblStructure(elementName, parentElementName, elementValue)
values ('f20', 'f15A', '20') 
insert into #tblStructure(elementName, parentElementName, elementValue)
values ('f21', 'f15A', '21')
insert into #tblStructure(elementName, parentElementName, elementValue)
values ('f30V', 'f15A', '30V')
9 окт 12, 17:55    [13292230]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
Referee
Member

Откуда:
Сообщений: 151
Коллеги, нет ни у кого мыслей? хотя бы в какую сторону копать...
10 окт 12, 10:25    [13294057]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Причём тут SQL?
Обращайтесь в форум на котором пишите клиент и в Разработка информационных систем.
10 окт 12, 13:43    [13295707]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
Referee
Member

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

Спасибо за ответ. Но...

А при чем здесь разработка информацияонных систем?

xml надо сформировать на сервере ms sql
10 окт 12, 17:27    [13297907]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
qwerty112
Guest
Referee
Mnior,

Спасибо за ответ. Но...

А при чем здесь разработка информацияонных систем?

+1
тоже проникся вопросом ...

---
Referee,

13151626
10 окт 12, 18:44    [13298446]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Referee
xml надо сформировать на сервере ms sql
Требование взятое с потолка.
Кстати, как и таблица #tblStructure - тоже (это бессмысленное недо-предтавление XML, полученное через ЗДЦ).
Нет таких бизнес задач.

Вы должны чётко понимать что есть бизнес задача и есть ёпнутые "решения" недо-программистов (коих 99%).
Хотя решение по крайней мере в 100 раз проще.

Понимаете, я могу доказать что нет таких "задач", расписать 100500 листов причин невозможности, но вы просто "отмахнётесь" - "возможно вы, правы, не читал, т.к. возможно всё". И после этого будет стоять только значёк FacePalm.jpg
100500 раз проходили это, хватит.
10 окт 12, 20:36    [13298776]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
Referee
Member

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

я вас не понимаю, коллега, отчего же нет задачь таких?

есть слудеющая архитектура обмена сообщениями в формате xml

Источник данных - шина - xsd - БД

задача сделать источником данных ms sql server с минимальными затратами.

задача вполне конктреная - древовидная структура данных, хранимая в вертикальном виде в БД.

в таблицах вида
FieldName - FieldValue

и есть приемник данных который понимает описаный выше формат xml.
Все это уже существует и изменения не подлежит.
временная таблица взята для удобства, чтобы хоть какие то данные представить на форуме.

я понимаю что пожно добавитиь слой между ними и с помощью шаблона xslt получить какой угодно xml на выходе. к сожалению ms sql server не может работать с xslt.

так что выделенное Вами "надо" можно трактовать как "очень желательно"
11 окт 12, 09:18    [13300358]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Referee
Все это уже существует и изменения не подлежит.
Что не подлежит?
Это извращение которое ничего не решает и ничего кроме проблем не создаёт?
Вот именно "извращение" это не бизнес задача. Если нужно забить сервер ерундой, можно и пустые циклы запустить, или рандом в /dev/null пустить.
Referee
архитектура обмена сообщениями в формате xml
Источник данных -> шина -> xsd -> БД
xsd это не данные, это их схема.
Referee
задача сделать источником данных ms sql server с минимальными затратами
И? В чём проблема? Написать FOR XML?
Referee
задача вполне конктреная - древовидная структура данных, хранимая в вертикальном виде в БД.
Это не задача, это ёпнутое решение. Вы что не можете отделить понятие "бизнес задача" и "техническое решение"?
Referee
и есть приемник данных который понимает описаный выше формат xml
Формат XML? Вы про XSD?
Формат обсолютно бесполехзная вещь для генерации данных ибо это формат (требование), а не связка данных одного формата (RDBS, метаданные) с другим (XML, XSD).
К примеру можно зная формат XML руками составить запрос используя FOR XML или используя ADO SQLXML провайдер написать запрос используя SQL заметки в XSD и/или XPath условия. Бла-бла-бла
И в этой структуре запроса FOR XML или SQLXML и будет прописана связка данных разных представлений.
Вот как пишутся связки при закачке из XML: XSD with SQL Annotation.
Referee
временная таблица взята для удобства, чтобы хоть какие то данные представить на форуме.
Смущает не @/# в названии а структура и представление данных.
Referee
я понимаю что пожно добавитиь слой между ними и с помощью шаблона xslt
Накуя? Можно сгенерировать любой XML.
Да, на SQL генерировать не желательно, и для этого поставляется вместе с сервером SQLXML провайдер. Который может делать очень много чего.
Основная задача упростить закачку/выкачку данных и взаимодействие приложений в формате XML.
Referee
так что выделенное Вами "надо" можно трактовать как "очень желательно"
Неа. Оно трактуется просто: изврат и более не менее.
Например забивать гвозди микроскопом это изврат, притом когда есть сундук молотков всевозможных размеров и типов.
11 окт 12, 12:52    [13301804]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
Referee
Member

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

еще раз - есть таблицы (они уже живут на сервере и какими бы они вам "извращенскими" не казались их не переделать)
-- не именно таке но суть та же...
declare @messageBody table (
	fieldName varchar(255),
	fieldValue varchar(255)
) 
	 
declare @subFields table (
	fieldName varchar(255),
	sequence int,
	subFieldValue varchar(255)
)

insert into @messageBody
select 'f15A', null union all
select 'f21', '21' union all
select 'f22', '22' union all
select 'f23', 'usd23'


insert into @subFields 
select 'f23', 1, 'usd' union all
select 'f23', 2, '23'


select * from @messageBody
select * from @subFields


Mnior
Накуя? Можно сгенерировать любой XML.


для того на форум и обратился чтобы понять как сгенерировать любой xml??

<f15A>
  <f21>21</f21>
  <f22>22</f22>
  <f23>
    <f1>usd</f1>
    <f2>23</f2>
  </f23>
</f15A>



использовать explicit? у меня не получилось так вывернуть данные...
делать для каждого поля вложенный запрос? гемор
11 окт 12, 14:56    [13302917]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Referee
"извращенскими" не казались
Они не "кажутся" кому-то извращенскими.
Они объективно извратные. Банально не нормализованы.
Referee
их не переделать
Это неверное утверждение. Переделать можно что угодно. Более того написать извратный костыль для них сложнее чем их переделать. Просто вы этого не хотите делать и готовы годами писать посты на форумах, чем просто взять и переделать.
Более того, вы никогда ничего не переделывали и не знаете когда и что надо учитывать при этом и насколько многими способами можно это сделать.

Вы не понимаете что схема и xml не резиновая и не каждое выражение пойдёт в качестве тега или аттрибута
SELECT	 1	AS [@xml]
	,2	AS [@бла-бла]
	,3	AS [@бла бла]
FOR XML Path('xml')
Даже если не будет пробелов, и xml сгенерируется, то не факт что он строго валидный. Там туева хуча ограничений.
Зачем было выбрано хранение в виде атрибутов XML, почему схема XML не определена жёстко?
Да потому что кто-то решил прохалявить и поизвращаться.
Скорее всего данные делятся на несколько групп схем. И должны были хранится не в виде { Field, Value }, а в виде полей таблица в одной или нескольких таблах. А если вообще к данным нет обращений, можно тупо в виде XML и хранить.

Если бы набор данных был реально динамический, то и передача данных в XML и была в виде структуры явных атрибутов FieldName и ValueName.

Где у вас наборы XSD?

Но это вы будете отмазываться ибо понимаете что messageBody и subFields это халтура от непонимания бизнесс задач.
=========================
Ok. Вы признаёте, что задача была решена через *опу и нужно для этого извращения написать другое извращение.

Вы можете написать динамический запрос, который определяет по всем именам полей соответствующую колонку. Пивотом или кейсом - не важно.

+ Намётки этого говнокода
;--------------------------------------- Имеем говно-структуру
CREATE TABLE #Field (
	 ID		Int
	 CONSTRAINT [PK_#Field]	PRIMARY KEY
--	,[Schema]	Int		NOT NULL	-- хотя бы
	,Name		VarChar(50)	NOT NULL
	 CONSTRAINT [UQ_#Field]	UNIQUE
	 CONSTRAINT [CK_#Field]	CHECK	(Name NOT LIKE '%[^a-z0-9]%' AND Name LIKE '[a-z]%')
--	,[Type]		Int		NOT NULL	-- Ну и так понятно, бесполездно объяснять
)
CREATE TABLE #Value (
	 Object	Int
--	 CONSTRAINT [FK_#Value_Object]	REFERENCES #Object(ID)
	,Field	Int
	 CONSTRAINT [FK_#Value_Field]	REFERENCES #Field (ID)
	,CONSTRAINT [PK_#Value]	PRIMARY KEY (
		 Object
		,Value
	)
	,Value	VarChar(250)	NOT NULL	-- SQL_Variant
)
;--------------------------------------- Имеем говно-данные
INSERT	#Field VALUES
 (1,'Field1')
,(2,'Field2')
,(3,'Field3')
INSERT	#Value VALUES
 (1,1,'Val1')
,(1,2,'Val2')
,(2,1,'Val3')
,(2,3,'Val4')
;--------------------------------------- "Решение"-костыль для этого извращения
DECLARE	 @Select	NVarChar(max) = Stuff((
		SELECT	',P.',QuoteName(ID),' AS ',QuoteName('@' + Name)
		FROM	#Field
		FOR XML Path(''),Type).value('text()[1]','NVarChar(max)'),1,1,'')
	,@Pivot		NVarChar(max) = Stuff((
		SELECT	',',QuoteName(ID)
		FROM	#Field
		FOR XML Path(''),Type).value('text()[1]','NVarChar(max)'),1,1,'')
EXEC ('
SELECT	' + @Select + '
FROM	#Value V PIVOT (Max(Value) FOR Field IN (' + @Pivot + ')) P
FOR XML Path(''row''),Root(''Root''),Type
')
Результат
<Root>
  <row Field1="Val1" Field2="Val2" />
  <row Field1="Val3" Field3="Val4" />
</Root>
Ну и допиливайте для вашего случая.
11 окт 12, 16:42    [13303788]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
Exproment
Member

Откуда:
Сообщений: 416
Referee,
ну можно как-то так:

declare @sql nvarchar(max),
			@xml xml;

set @sql = '';
--рекурсивное обобщенное табличное выражение генерит теги в нужном виде
with cte
as 
(
	select	ElementName,
	        ParentElementName,
		ElementValue,
		Tag = cast(ElementName as varchar(max))
	from	#tblStructure	
	where	ParentElementName is null and ElementValue is null

	union all

	select  TS.ElementName,
	        TS.ParentElementName,
	        TS.ElementValue,
		Tag = cast(C.Tag as varchar(max)) + '/' + cast(TS.ElementName as varchar(max))
	from	#tblStructure TS 
		join cte C on C.ElementName = TS.ParentElementName
)
select	@sql +=	case when @sql = '' then '' else ',' end + 
		'''' + C.ElementValue +  ''' as [' + C.Tag + ']' + nchar(10)
from	CTE C
where	C.ElementValue is not null;

set @sql = 'set @xml = (select' +  nchar(10) + @sql +  'for xml path(''xml''))'

print	@sql -- можно посмотреть какой запрос получился
exec sp_executesql	@stmt = @sql, 
				@params = N'@xml xml out ',
				@xml = @xml out
select @xml

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

А с Mnior полностью согласен. Вам бы книжечку почитать об XML =)
11 окт 12, 21:49    [13305183]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
Mnior
Это неверное утверждение. Переделать можно что угодно. Более того написать извратный костыль для них сложнее чем их переделать.
Mnior, не придирайтесь :-)

"Не переделать" - имеется в виду, очевидно, что кто то что то напроектировал, а потом сотни программистов в течении многих лет получают задания типа "надо из нее получить xml вида"
Mnior
Просто вы этого не хотите делать и готовы годами писать посты на форумах, чем просто взять и переделать.
Хозяин-барин - платят-то за написание годами постов, а не за решение бизнес-задачи. Не всем получается посылать начальников и безнаказанно отказываться от заданий...
11 окт 12, 22:38    [13305307]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
alexeyvg
Не всем получается посылать начальников и безнаказанно отказываться от заданий...
Естественно, "начальники" такие же халопы-стрелочники и самодуры.

В итоге, лучше даже не "посылать", а отвертеться и побыстрее уйти в "тихое место", а лучше к адекватным людям, хоть это и не так просто.
С хомяками каши не сваришь. Даже здесь на форуме - борьба с ветряными.
11 окт 12, 23:23    [13305435]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
Referee
Member

Откуда:
Сообщений: 151
Коллеги, спасибо всем за ответы!

я сам за красивые решения, и пытаюсь после себя оставлять код лучше чем он был до меня.

но не всегда с чем сталкиваешься получается красиво решить.

описаная архитектура - это SWIFT сообщения, отправляемые между банками в текстовом виде, именно для этого (я так полагаю) и создавалась такая архитектура - с ее основной задачей она вполне справляется. за долгие годы вокруг этой архитектура написано уже без малого сотня процедур и функций. код начинает "пахнуть".
к сожалению в компании пока еще не развиты идеи автоматического тестирования и рефактооринга. коллектив еще в начале этого тернистого пути.

теперь же поставлена задача отправки xml сообщений между несколькими подсистемами. схема же xml задано в основном приемником данных, точнее его xml парсером, который просто не может работать с аттрибутами.

по поводу xml я понимаю что не любой текст может быть наименованием элемента или атрибута. и поэтому его стремно брать из данных БД (малоли что туда запишут)

Еще раз спаисбо за ответы.
12 окт 12, 13:09    [13308029]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
Referee
еперь же поставлена задача отправки xml сообщений между несколькими подсистемами. схема же xml задано в основном приемником данных, точнее его xml парсером, который просто не может работать с аттрибутами
И чего бы было не использовать стандарт на кодирование транзакций SWIFT в XML? http://i20022.com/

Выбирать модель данных исходя их возможностей парсера XML - это верх креатива!

Вот она, практика набирать для новой разработки талантливую молодёжь без единого сделанного проекта :-) Уж взяли бы хоть одного старпёра, который уже сделал несколько проектиков с передачей в XML, а ещё лучьше один из них, связанный со SWIFT, и не было бы таких проблем.
12 окт 12, 13:19    [13308116]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
Referee
Member

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

alexeyvg
Вот она, практика набирать для новой разработки талантливую молодёжь без единого сделанного проекта


Коллега, не стоит считать человека говном на лопате если он не имеет опыта в области в которой имете опыт вы.
12 окт 12, 13:38    [13308268]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
Referee
Коллега, не стоит считать человека говном на лопате если он не имеет опыта в области в которой имете опыт вы.
Я не считаю говном, с чего вы взяли??? А уж в свифте я вообще не ориентируюсь.

Просто это безумие и дилетантизм менеджеров, формировать такие команды.

Это же абсурд - формировать операционную бригаду в больнице из вчерашних студентов, так никто не делает. И никто этих студентов не зазывает "говном на лопате", просто в бригаде должен быть опытный гуру, пара человек с опытом 5-10 лет и пара студентов - вот это будет отличная сбалансированная команда.
12 окт 12, 15:00    [13308886]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
alexeyvg
Выбирать модель данных исходя их возможностей парсера XML - это верх креатива!
Всё уместить в одной фразе.
Ну почему я так не умею.
12 окт 12, 15:41    [13309227]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
Exproment
Member

Откуда:
Сообщений: 416
Referee
Еще раз спаисбо за ответы.

Ну так как вы сделали ? мой костыль подошел ? или сделали свой ? Какой свой ?

Ненавижу когда создают тему, а что в итоге вышло не пишут.
12 окт 12, 15:52    [13309324]     Ответить | Цитировать Сообщить модератору
 Re: сформировать xml по дереву  [new]
Referee
Member

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

Да спасибо большое! за сонову взял ваш костыль.

alexeyvg,

Прошу прощения если обидел, просто я себя не считаю уж такой "талантливой молодежью".. конечно не гуру тоже :)

Коллеги, всем спасибо за ответы! тему закрываем.
12 окт 12, 17:19    [13310003]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить