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

Откуда:
Сообщений: 1196
Всем привет!
В таблице есть XML-поле примерно такого содержания:
<AdditionalInfo>
  <Specifications>
    <Item Name="data1" Value="ADA: No" />
    <Item Name="data2" Value="Apron Front: Yes" />
    <Item Name="data3" Value="Basin Depth: 9" />
    <Item Name="data4" Value="ADA: No" />
    <Item Name="data5" Value="Apron Front: Yes" />
    <Item Name="data6" Value="Basin Depth: 9" />
    <Item Name="data7" Value="ADA: No" />
    <Item Name="data8" Value="Apron Front: Yes" />
    <Item Name="data9" Value="Basin Depth: 9" />
  </Specifications>
</AdditionalInfo>


Нужно убрать элементы с повторяющимися значениями атрибутов, т.е. получить следующее:

<AdditionalInfo>
  <Specifications>
    <Item Name="data1" Value="ADA: No" />
    <Item Name="data2" Value="Apron Front: Yes" />
    <Item Name="data3" Value="Basin Depth: 9" />
  </Specifications>
</AdditionalInfo>


При этом число уникальных значений может изменяться.
Для меня самое простое - конвертировать в реляционные данные, группировка и обратно в XML.
Но хотелось бы через XQUERY, потому что объём данных значителен и на OLTP системе загружать сильно процессор не хочется.
7 дек 11, 16:36    [11723161]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Jovanny, наивный как 2 копейки. XQuery это уже всё - хамба.
Но хамба была когда один умник засунул реляционыне данные в XML поле. Теперь вам расхлёбывать. Поздравляю.

Словестный вопрос не сходтся с примером. В премере нет записей с дублями по аттрибутам (Name везде отличается).

Может быть даже эффентивнее по ресурсам:
1. всё выгрузить в файл
2. загрузить в таблицу (через XmlBulkLoad)
3. Избавить от мусора
4. Запросом заменить XML, генерируемый из этой таблы (FOR XML)
Для уменьшения локировок заменять кусками (UPDATE Top(NNN))

Но ещё лучге не заменять, а дропнуть колонку и выдавать данные из таблы.
А ещё то что мне это сделать очегь легко, а вы споткнётесь на каждом пункте.
7 дек 11, 20:01    [11724960]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Jovanny, а так
declare @x as xml = N'
<AdditionalInfo>
  <Specifications>
    <Item Name="data1" Value="ADA: No" />
    <Item Name="data2" Value="Apron Front: Yes" />
    <Item Name="data3" Value="Basin Depth: 9" />
    <Item Name="data4" Value="ADA: No" />
    <Item Name="data5" Value="Apron Front: Yes" />
    <Item Name="data6" Value="Basin Depth: 9" />
    <Item Name="data7" Value="ADA: No" />
    <Item Name="data8" Value="Apron Front: Yes" />
    <Item Name="data9" Value="Basin Depth: 9" />
  </Specifications>
</AdditionalInfo>';
select @x.query('for $o in //Item
                 where empty(for $i in //Item
                             where $i/@Name < $o/@Name
                               and $i/@Value = $o/@Value
                             return $i)
                 return $o')
;
7 дек 11, 21:20    [11725284]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
andrey odegov, Ок. Допустим выберем не такой эффективный способ. Всё равно же одноразовая задача.
Тогда только про UPDATE Top не забыть, и убедится что процесс не распараллелился (на все процессоры), что врядли.

Если хотите можно примерно и так:
DECLARE	@Table TABLE (
	 ID	Int IDENTITY PRIMARY KEY
	,Data	XML
)INSERT	@Table VALUES ('
<AdditionalInfo>
  <Specifications>
    <Item Name="data1" Value="ADA: No" />
    <Item Name="data2" Value="Apron Front: Yes" />
    <Item Name="data3" Value="Basin Depth: 9" />
    <Item Name="data4" Value="ADA: No" />
    <Item Name="data5" Value="Apron Front: Yes" />
    <Item Name="data6" Value="Basin Depth: 9" />
    <Item Name="data7" Value="ADA: No" />
    <Item Name="data8" Value="Apron Front: Yes" />
    <Item Name="data9" Value="Basin Depth: 9" />
  </Specifications>
</AdditionalInfo>');

UPDATE	T
SET	Data	= (
	SELECT	 Min(X.[@Name])	AS [@Name]
		,X.[@Value]
	FROM	T.Data.nodes('/AdditionalInfo/Specifications/Item') I(X)
		CROSS APPLY (SELECT
			 I.X.value('@Name ','SysName')	AS [@Name]
			,I.X.value('@Value','SysName')	AS [@Value]
				) X
	GROUP BY X.[@Value]
	FOR XML Path('Item'),Root('Specifications'), Type)
FROM	@Table	T
8 дек 11, 00:47    [11726099]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
andrey odegov,

Всё неплохо, только если элементов Item больше 10, получается некрасиво.

declare @x as xml = N'
<AdditionalInfo>
  <Specifications>
    <Item Name="data1" Value="ADA: No" />
    <Item Name="data2" Value="Apron Front: Yes" />
    <Item Name="data3" Value="Basin Depth: 9" />
    <Item Name="data4" Value="Basin Length: 30.75" />
    <Item Name="data5" Value="Basin Rack Included: No" />
    <Item Name="data6" Value="ADA: No" />
    <Item Name="data7" Value="Apron Front: Yes" />
    <Item Name="data8" Value="Basin Depth: 9" />
    <Item Name="data9" Value="Basin Length: 30.75" />
    <Item Name="data10" Value="Basin Rack Included: No" />
    <Item Name="data11" Value="ADA: No" />
    <Item Name="data12" Value="Apron Front: Yes" />
    <Item Name="data13" Value="Basin Depth: 9" />
    <Item Name="data14" Value="Basin Length: 30.75" />
    <Item Name="data15" Value="Basin Rack Included: No" />
  </Specifications>
</AdditionalInfo>';
select @x.query('for $o in //Item
                 where empty(for $i in //Item
                             where $i/@Name < $o/@Name
                               and $i/@Value = $o/@Value
                             return $i)
                 return $o');
8 дек 11, 11:45    [11727678]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Спасибо всем.
У меня получилось что-то такое:

declare @x as xml = N'
<AdditionalInfo>
  <Specifications>
	<Item Name="data1" Value="ADA: No" />
    <Item Name="data2" Value="Apron Front: Yes" />
    <Item Name="data3" Value="Basin Depth: 9" />
    <Item Name="data4" Value="Basin Length: 30.75" />
    <Item Name="data5" Value="Basin Rack Included: No" />
	<Item Name="data6" Value="ADA: No" />
    <Item Name="data7" Value="Apron Front: Yes" />
    <Item Name="data8" Value="Basin Depth: 9" />
    <Item Name="data9" Value="Basin Length: 30.75" />
    <Item Name="data10" Value="Basin Rack Included: No" />
	<Item Name="data11" Value="ADA: No" />
    <Item Name="data12" Value="Apron Front: Yes" />
    <Item Name="data13" Value="Basin Depth: 9" />
    <Item Name="data14" Value="Basin Length: 30.75" />
    <Item Name="data15" Value="Basin Rack Included: No" />
  </Specifications>
</AdditionalInfo>';

WITH 
DS AS (SELECT @x AS DescriptionXml),
DV AS (
		SELECT CONVERT(int, SUBSTRING(T.C.value('@Name', 'varchar(20)'), 5, 100)) AS NameNumber, T.C.value('@Value', 'varchar(500)') AS Value
			FROM DS CROSS APPLY DS.DescriptionXml.nodes('/AdditionalInfo/Specifications/Item') AS T(C)),
DR AS (
	SELECT 'data' + CONVERT(varchar(10), MIN(DV.NameNumber)) AS Name, DV.Value FROM DV
	GROUP BY DV.Value)
SELECT (SELECT DR.Name AS '@Name', DR.Value AS '@Value' FROM DR FOR XML PATH('Item'), ROOT ('Specifications'), TYPE) 
			FROM DS FOR XML PATH('AdditionalInfo'), TYPE;
8 дек 11, 12:11    [11728010]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
мимо
Guest
declare @xml as xml = 
'
<AdditionalInfo>
  <Specifications>
    <Item Name="data1" Value="ADA: No" />
    <Item Name="data2" Value="Apron Front: Yes" />
    <Item Name="data3" Value="Basin Depth: 9" />
    <Item Name="data4" Value="ADA: No" />
    <Item Name="data5" Value="Apron Front: Yes" />
    <Item Name="data6" Value="Basin Depth: 9" />
    <Item Name="data7" Value="ADA: No" />
    <Item Name="data8" Value="Apron Front: Yes" />
    <Item Name="data9" Value="Basin Depth: 9" />
  </Specifications>
</AdditionalInfo>
'

;with a as (
			select 	y.value('@Name','varchar(250)') as Name
					,y.value('@Value','varchar(250)') as Value
					
			from @xml.nodes('AdditionalInfo/Specifications/Item') as z(y)
			)
			, b as 
			(
				select RANK() over (PARTITION by Value order by name) as r
						,name
						,Value
				from a  
			
			)
select 
(			
	select Name as 'Item/@Name'
			,Value as 'Item/@Value'
	from b
	where r = 1
	for xml path(''), root('Specifications'),type
) 
for xml path (''),root('AdditionalInfo') 
8 дек 11, 15:59    [11730490]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
Jovanny
Member

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

Как вариант. Только опять же сортировочка будет корректнее, если брать
select RANK() over (PARTITION by Value order by CONVERT(int, SUBSTRING(Name, 5, 100))) as r
8 дек 11, 17:33    [11731407]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Jovanny, не получиться "в лоб" одну часть строки сравнить по правилам сравнения строк, а другую часть - по правилам сравнения чисел
select min(name) from (values ('data2'), ('data10'), ('data3')) as t(name)
9 дек 11, 08:24    [11733898]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
сорри, туплю
9 дек 11, 08:46    [11733944]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
или не туплю и TC на самом деле нужно, чтобы 'data2' шло перед 'data10'?
9 дек 11, 09:34    [11734098]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Jovanny, а так
declare @t table
( id integer not null primary key
, x xml not null );
declare @x as xml = N'
<AdditionalInfo>
 <Specifications>
  <Item Name="data1" Value="ADA: No" />
  <Item Name="data2" Value="Apron Front: Yes" />
  <Item Name="data3" Value="Basin Depth: 9" />
  <Item Name="data4" Value="Basin Length: 30.75" />
  <Item Name="data5" Value="Basin Rack Included: No" />
  <Item Name="data6" Value="ADA: No" />
  <Item Name="data7" Value="Apron Front: Yes" />
  <Item Name="data8" Value="Basin Depth: 9" />
  <Item Name="data9" Value="Basin Length: 30.75" />
  <Item Name="data10" Value="Basin Rack Included: No" />
  <Item Name="data11" Value="ADA: No" />
  <Item Name="data12" Value="Apron Front: Yes" />
  <Item Name="data13" Value="Basin Depth: 9" />
  <Item Name="data14" Value="Basin Length: 30.75" />
  <Item Name="data15" Value="Basin Rack Included: No" />
 </Specifications>
</AdditionalInfo>';
insert into @t(id, x) values(1, @x);
update @t
set x = @x.query('<AdditionalInfo>
                 <Specifications>
                 {for $o in //Item
                  where empty(for $i in //Item
                              where xs:integer(substring($i/@Name, 5)) < xs:integer(substring($o/@Name, 5))
                                and $i/@Value = $o/@Value
                              return $i)
                  return $o}
                 </Specifications>
                 </AdditionalInfo>')
;
select * from @t;
/*
select min(name) as n, val from (
values ('data1', 'ADA: No')
     , ('data2', 'Apron Front: Yes')
     , ('data3', 'Basin Depth: 9')
     , ('data4', 'Basin Length: 30.75')
     , ('data5', 'Basin Rack Included: No')
     , ('data6', 'ADA: No')
     , ('data7', 'Apron Front: Yes')
     , ('data8', 'Basin Depth: 9')
     , ('data9', 'Basin Length: 30.75')
     , ('data10', 'Basin Rack Included: No')
     , ('data11', 'ADA: No')
     , ('data12', 'Apron Front: Yes')
     , ('data13', 'Basin Depth: 9')
     , ('data14', 'Basin Length: 30.75')
     , ('data15', 'Basin Rack Included: No')) as t(name, val)
group by val
;
*/
9 дек 11, 10:04    [11734268]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
following-sibling Неподдерживаемая функциональность
9 дек 11, 12:28    [11735633]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
это о чем, поясните, пожалуйста
Mnior
following-sibling Неподдерживаемая функциональность
9 дек 11, 14:27    [11736891]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
andrey odegov, по идее можно сильно упростить XQuery. Типа значение не должно совнадать у previouse-sibling (предыдуших значений тогоже уровня).
9 дек 11, 19:50    [11739397]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Mnior, наверное preceding-sibling
и да, с ним код должен упроститься
9 дек 11, 21:54    [11739947]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
Mnior
Jovanny, наивный как 2 копейки. XQuery это уже всё - хамба.
Но хамба была когда один умник засунул реляционыне данные в XML поле. Теперь вам расхлёбывать. Поздравляю.


это все от дурной реализации. в DB2 это автоматом прoецируется в нормальные таблицы
10 дек 11, 02:29    [11740804]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Lepsik
в DB2 это автоматом прoецируется в нормальные таблицы
Вы бы линки хотя бы привели.
Не думаю что там принципиальная разница, синтаксис немного отличается (возможно ближе к оракловому), а всё остальное тоже самое. Не?
10 дек 11, 21:39    [11743049]     Ответить | Цитировать Сообщить модератору
 Re: Убрать повторяющиеся значения в XML  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
andrey odegov,

Спасибо, ваше решение мне больше всего нравится.
Хотя интересно будет повозиться с preceding-sibling.
13 дек 11, 13:29    [11755983]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить