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

Откуда: Саратов
Сообщений: 467
Доброго времени суток!
Имеется таблица вида
CREATE TABLE [Reports](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[reportXML] [xml] NULL ...

Поле reportXML представляет собой структуру вида
<TABLE>
  <ROW CssClass="ObjBudget" ID="03103971-5263-4f8a-b2a5-8806f0b507d9"...>
  <ROW CssClass="LocalBudget" ID="1754e7ae-c57b-407b-87e2-3c1194cc5808"...>
  <ROW CssClass="ObjBudget" ID="54e5d447-4897-4ecc-8d92-cb28c573e255"...>
  ...


Необходимо во все элементы ROW добавить атрибут ID64, который хранится в другой таблице OBudgets, связанной с исходной по атрибуту ID элемента ROW.
Пробую делать следующим образом:
UPDATE R
set reportXML.modify('
	insert attribute ID64 {sql:column("o.OriginalID64")} 
	as last
	into 
	(/TABLE/ROW[@ID=sql:column("o.OriginalID")])[1]
	')
FROM  Reports R
CROSS APPLY reportXML.nodes('/TABLE/ROW') T(c)
left join OBudgets o on o.OriginalID = c.value('@ID[1]','uniqueidentifier')
where R.ID = 2975 --для теста изменяю только 1 запись в Reports
and c.value('@CssClass[1]','varchar(50)') = 'ObjBudget' 
and c.value('@ID64[1]','varchar(50)') is null

Команда отрабатывает, но видимых изменений (добавления в конец ROW атрибута ID64) не наблюдается.

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) 
	Jun 28 2012 08:36:30 
	Copyright (c) Microsoft Corporation
	Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
9 июл 13, 15:43    [14541989]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> Необходимо во все элементы ROW добавить атрибут ID64

во все так не получится. надо либо разбирать reportXML на строки и потом строить по ним новый xml, либо по update на каждый ROW (по отдельной команде update).
9 июл 13, 16:17    [14542298]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 467
Что-то я не соображу, как должен выглядеть второй вариант. Какая должна быть команда внутри reportXML.modify ?
9 июл 13, 16:39    [14542504]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
Шамиль Фаридович,

modify(insert) только в одну ноду вставить может. Здесь лучше через FLWOR, наверное - http://msdn.microsoft.com/en-us/library/ms190945.aspx
9 июл 13, 16:56    [14542616]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
Шамиль Фаридович
Что-то я не соображу, как должен выглядеть второй вариант. Какая должна быть команда внутри reportXML.modify ?


не-не. _одной_ командой modify такое как раз не получится.
9 июл 13, 17:02    [14542651]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 467
Это я понял. Если вам не сложно, то набросайте второй вариант.
Первый как я понял - курсор по таблице Reports. Второй видимо тоже курсор, но я чет не пойму, как все это должно выглядеть.
9 июл 13, 17:30    [14542849]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 467
Ennor Tiegael
Здесь лучше через FLWOR, наверное - http://msdn.microsoft.com/en-us/library/ms190945.aspx

А как его можно использовать для изменения, если FLWOR итеративно возвращает результирующий набор и требует return в своей конструкции?
9 июл 13, 17:46    [14542984]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Шамиль Фаридович
Необходимо во все элементы ROW добавить атрибут ID64, который хранится в другой таблице OBudgets, связанной с исходной по атрибуту ID элемента ROW.
Вы нарушаете принцип единственности источника.
Xml - это уровень протокола, выходной формат. А у вас источник данных размазан - что не есть правильно.
Наведите порядок, все данные в одном запросе, и тогда вам не нужно будет эта ерунда.

А XML не катит в качестве оперативного типа данных, внутрях скуля всё должно быть реляционно.
9 июл 13, 20:39    [14543609]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 467
Спасибо конечно за полезный совет, но структуру данных я править не могу. И задачу никто не снимал.
10 июл 13, 02:06    [14544481]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 467
В общем, если пойти по второму предложенному варианту, то получается код с 2мя курсорами.
+

DECLARE @ReportID int,
		@xml xml,
		@guid char(36),
		@id64 bigint
--DECLARE	@tbRows table([guid] char(36), id64 bigint)
DECLARE Outer_cursor CURSOR FOR
SELECT id, reportXML
  FROM slices.Reports
  where ID = 2972 --для теста изменяю только 1 строку в Reports

OPEN Outer_cursor

FETCH NEXT FROM Outer_cursor
   into @ReportID, @xml

WHILE @@FETCH_STATUS = 0
BEGIN

	DECLARE inner_cursor CURSOR FOR
	select c.value('@ID[1]','char(36)'), o.OriginalID64
	FROM  @xml.nodes ('/TABLE/ROW') T(c)
	join slices.OBudgets o on o.OriginalID = c.value('@ID[1]','uniqueidentifier')
	where c.value('@CssClass[1]','varchar(50)') = 'ObjBudget' 
		  and c.value('@ID64[1]','varchar(50)') is null
	  
	OPEN inner_cursor

	FETCH NEXT FROM inner_cursor
	   into @guid, @id64

	WHILE @@FETCH_STATUS = 0
	BEGIN
		set @xml.modify('
			insert attribute ID64 {sql:variable("@id64")} 
			as last
			into (/TABLE/ROW[@ID eq sql:variable("@guid")])[1]
			')
						
	   FETCH NEXT FROM inner_cursor
	   into @guid, @id64 
	END

	CLOSE inner_cursor
	DEALLOCATE inner_cursor
	
	UPDATE slices.Reports
	set reportXML = @xml
	where ID = @ReportID
	
	FETCH NEXT FROM Outer_cursor
	into @ReportID, @xml
END

CLOSE Outer_cursor
DEALLOCATE Outer_cursor


Есть способ лучше?
10 июл 13, 03:50    [14544506]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
Шамиль Фаридович,

2 варианта:
+
declare @reports table (
	Id int primary key,
	ReportXML xml not null
);

declare @budgets table (
	Id int identity(1,1) primary key,
	OriginalID uniqueidentifier not null,
	OriginalID64 bigint null
);

insert into @reports (Id, ReportXML)
select 1, '<TABLE>
	<ROW CssClass="ObjBudget" ID="03103971-5263-4f8a-b2a5-8806f0b507d9" />
	<ROW CssClass="LocalBudget" ID="1754e7ae-c57b-407b-87e2-3c1194cc5808" />
	<ROW CssClass="ObjBudget" ID="54e5d447-4897-4ecc-8d92-cb28c573e255" />
</TABLE>';

insert into @budgets (OriginalID, OriginalID64)
values
	('03103971-5263-4f8a-b2a5-8806f0b507d9', 34583675836583),
	('1754e7ae-c57b-407b-87e2-3c1194cc5808', 5342);


-- Straightforward
select tr.c.value('./@CssClass', 'varchar(100)') as [@CssClass],
	tr.c.value('./@ID', 'uniqueidentifier') as [@ID],
	b.OriginalID64 as [@ID64]
from @reports r
	cross apply r.ReportXML.nodes('/TABLE[1]/ROW') tr(c)
	left join @budgets b on b.OriginalID = tr.c.value('./@ID', 'uniqueidentifier')
where r.Id = 1
for xml path('ROW'), type, root('TABLE');


-- FLWOR
select tr.c.query('
	for $r in /TABLE[1]/ROW
	return <ROW CssClass = "{$r/@CssClass}" ID = "{$r/@ID}" ID64 = "{sql:column("b.OriginalID64")}" />
')
	--where $r/@ID = sql:column("b.OriginalID")
from @reports r
	cross apply r.ReportXML.nodes('/TABLE[1]/ROW') tr(c)
	left join @budgets b on b.OriginalID = tr.c.value('./@ID', 'uniqueidentifier')
where r.Id = 1
for xml path(''), type, root('TABLE');
Как видите, в случае FLWOR происходит декартово произведение - в этом месте сиквел игнорирует условие отбора, указанное в методе nodes() и всегда берет полный XML.

При этом, если я пытаюсь указать where (чуть ниже закомментировано), то перестает возвращаться вообще что-либо. К сожалению, пример по where в BOL очень куцый, и непонятно, как его писать в этом случае. Не знаю, может Роман Голубин сюда заглянет, он наверное напишет :) Ибо я в FLWOR совсем не силен, к сожалению.
10 июл 13, 07:44    [14544591]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Шамиль Фаридович
Спасибо конечно за полезный совет, но структуру данных я править не могу. И задачу никто не снимал.
Не структуру данных, а структуру запроса к серверу. Таблицы трогать не надо.
А в 90% "править не могу" означает "не хочу" ни делать ни понимать.
Велкам.
10 июл 13, 09:43    [14544937]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
IF OBJECT_ID('tempdb..#Reports') IS NOT NULL DROP TABLE #Reports
CREATE TABLE #Reports(
	[ID] [int] ,
	[reportXML] [xml] NULL)
INSERT INTO 	#Reports(ID,reportXML)
VALUES(1,'<TABLE>
  <ROW CssClass="ObjBudget" ID="03103971-5263-4f8a-b2a5-8806f0b507d9"/>
  <ROW CssClass="LocalBudget" ID="1754e7ae-c57b-407b-87e2-3c1194cc5808"/>
  <ROW CssClass="ObjBudget" ID="54e5d447-4897-4ecc-8d92-cb28c573e255"/>
 </TABLE>')

IF OBJECT_ID('tempdb..#OBudgets') IS NOT NULL DROP TABLE #OBudgets
CREATE TABLE #OBudgets(
	OriginalID uniqueidentifier,
	OriginalID64 int )
INSERT INTO 	#OBudgets(OriginalID,OriginalID64)
VALUES('03103971-5263-4f8a-b2a5-8806f0b507d9',1),('1754e7ae-c57b-407b-87e2-3c1194cc5808',2),('54e5d447-4897-4ecc-8d92-cb28c573e255',3)

select * from  #Reports
select * from #OBudgets


UPDATE R0 SET R0.reportXML =
(select 
	 c.value('@CssClass[1]','varchar(100)')  AS [@CssClass]
	,c.value('@ID[1]','uniqueidentifier')      AS [@ID]
	,o.OriginalID64								   AS [@ID64]			
 FROM  #Reports R
 CROSS APPLY R.reportXML.nodes('/TABLE/ROW') T(c)
 left join #OBudgets o on o.OriginalID = c.value('@ID[1]','uniqueidentifier') 
 WHERE R.ID = R0.ID 
 FOR XML PATH ('ROW'), root('TABLE') ,type
) 
FROM  #Reports R0

select * from  #Reports
10 июл 13, 10:31    [14545177]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 467
LexusR, Ennor Tiegael
Ваши варианты работают, но к сожалению, в xml-поле есть еще куча других элементов, кроме row, и row содержит не только указанные атрибуты. У меня появилась еще одна проблема, в xml-поле могут быть элементы с одинаковым ID. При запуске
...
set @xml = '
<TABLE>
  <ROW CssClass="LocalBudget " ID="2ebdabe7-7417-4dc3-a83b-fc2a7b41f849">
  </ROW>
  <ROW CssClass="LocalBudget "  ID="c438e9b5-d281-4fc4-a4aa-b740a3087777">
  </ROW>
  <ROW CssClass="ObjBudget " ID="10497e8c-e69c-4c09-be65-07441945a07d">
  </ROW>
  <ROW CssClass="LocalBudget " ID="2ebdabe7-7417-4dc3-a83b-fc2a7b41f849">
  </ROW>
  <ROW CssClass="ObjBudget " ID="2ebdabe7-7417-4dc3-a83b-fc2a7b41f849">
  </ROW>
  <ROW CssClass="LocalBudget" ID="2ebdabe7-7417-4dc3-a83b-fc2a7b41f849">
  </ROW>  
</TABLE>'
set @equalBudgetsCount = (
		select   count(*)
		FROM  @xml.nodes ('/TABLE/ROW[@ID eq sql:variable("@guid")]') T(c))
set @counter = 1
while @counter <= @equalBudgetsCount
begin

	set @xml.modify('
		insert attribute ID64 {sql:variable("@id64")} 
		as last
		(/TABLE/ROW[@ID eq sql:variable("@guid") and fn:contains(@CssClass,"LocalBudget")])[sql:variable("@counter")]')
	set @counter = @counter + 1
end	

возникает ошибка:
Msg 2226, Level 16, State 1, Line 55
XQuery [modify()]: Целью "insert" должен быть одиночный узел: обнаружено "element(ROW,xdt:untyped) *"

Почему? Ведь я пытаюсь изменить одиночный узел с индексом sql:variable("@counter") ?
12 июл 13, 12:34    [14557141]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
Шамиль Фаридович,

Потому что сиквел воспринимает в качестве порядкового номера ноды только литерал. А ваше
'...[sql:variable("@counter")]'
он рассматривает как еще одно условие фильтрации, в довесок к предыдущим двум.

Вообще, если у вас там такой бардак, то лучше сначала весь XML выгрузить в реляционную схему (временные таблицы, например), а потом собрать заново, в этот раз уже нормально. Ну или скормить это хозяйство внешнему парсеру, допустим CLR-процедуре, которая может иметь больше возможностей по манипуляции XML, нежели сиквел.
12 июл 13, 12:57    [14557359]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 467
Использовал sp_executesql. Правда не получилось сразу все параметры передать в эту хранимку, но работает:
+
DECLARE @ReportID int,
		@xml xml,
		@guid char(36),
		@id64 bigint,
		@equalBudgetsCount smallint,
		@counter int,
		@query nvarchar(max),
		@parameters nvarchar(500)
DECLARE	@tbErrors table(ReportID bigint, descr varchar(500), ID64 bigint, [guid] uniqueidentifier)


set @xml = '
<TABLE>
  <ROW CssClass="LocalBudget " EStyle="RDCLBudgetStyle" LEVEL="1.1.1" ID="2ebdabe7-7417-4dc3-a83b-fc2a7b41f849">
  </ROW>
  <ROW CssClass="LocalBudget " EStyle="RDCLBudgetStyle" LEVEL="1.1.2" ID="c438e9b5-d281-4fc4-a4aa-b740a3087777">
  </ROW>
  <ROW CssClass="ObjBudget " EStyle="RDCOBudgetStyle" LEVEL="1.2" ID="10497e8c-e69c-4c09-be65-07441945a07d">
  </ROW>
  <ROW CssClass="LocalBudget " EStyle="RDCLBudgetStyle" LEVEL="1.2.1" ID="2ebdabe7-7417-4dc3-a83b-fc2a7b41f849">
  </ROW>
  <ROW CssClass="ObjBudget " EStyle="RDCOBudgetStyle" LEVEL="1.2" ID="2ebdabe7-7417-4dc3-a83b-fc2a7b41f849">
  </ROW>
  <ROW CssClass="LocalBudget" EStyle="RDCLBudgetStyle" LEVEL="1.2.1" ID="2ebdabe7-7417-4dc3-a83b-fc2a7b41f849">
  </ROW>  
</TABLE>'

DECLARE inner_cursor CURSOR LOCAL FOR
select   distinct c.value('@ID[1]','char(36)'), o.OriginalID64
FROM  @xml.nodes ('/TABLE/ROW') T(c)
join slices.LBudgets o on cast(o.OriginalID as char(36))= c.value('@ID[1]','varchar(36)')
where c.value('@CssClass[1]','varchar(11)') = 'LocalBudget' 
	  and c.value('@ID64[1]','varchar(50)') is null
  
OPEN inner_cursor

FETCH NEXT FROM inner_cursor
   into @guid, @id64

WHILE @@FETCH_STATUS = 0
BEGIN
	set @equalBudgetsCount = (
			select   count(*)
			FROM  @xml.nodes ('/TABLE/ROW[@ID eq sql:variable("@guid")]') T(c))
	set @counter = 1
	while @counter <= @equalBudgetsCount
	begin

		set @query = N'
			set @xml.modify(''
			insert attribute ID64 {@id64} 
			as last
			into (/TABLE/ROW[@ID eq "@guid"  and fn:contains(@CssClass,"LocalBudget")])[@counter]'')'
	
		set @query = REPLACE(@query, '@id64', @id64);
		set @query = REPLACE(@query, '@guid', @guid);
		set @query = REPLACE(@query, '@counter', @counter);	

		--set @parameters =  N'
		--	@id64 bigint,
		--	@guid char(36),
		--	@counter smallint,
		--	@xml xml output'
		--exec sp_executesql @query,@parameters,@id64, @guid, @counter, @xml out
		set @parameters =  N'@xml xml output'
		exec sp_executesql @query,@parameters, @xml out
		
		set @counter = @counter + 1
	end	

   FETCH NEXT FROM inner_cursor
   into @guid, @id64 
END

CLOSE inner_cursor
DEALLOCATE inner_cursor

select @xml
12 июл 13, 15:38    [14558871]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Ennor Tiegael
Вообще, если у вас там такой бардак, то лучше сначала весь XML выгрузить в реляционную схему ...
Вот именно. А далее надо на всё спокойно посмотреть со стороны и увидеть очевидное:

Надо сразу сгенерировать правильный XML из одного запроса.
А не реляционные данные -> XML -> реляционные данные -> XML ... > XML
Бред же. А самое главное понимание, что данные связанные одним логическим понятием - должны собираться в одном месте, а не размазаны по процедурам с тоннами ненужных трансформаций.

Сколько недель вы будете тратить на этот универсальный монстр и на его поддержку, вместо того чтобы сразу и быстро сделать как надо?
Прикольно другое - в базе появляется куча всего лишнего, но при этом вопят "нельзя ничего менять и трогать". Му-ха-ха.

Ай, что я говорю, дураки умирают дураками. Этого не изменить.
12 июл 13, 17:07    [14559623]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 467
Mnior, у вас говорящий. Хорошо, когда на все есть свое мнение. Но иногда его лучше оставить при себе.
12 июл 13, 18:04    [14560007]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Шамиль Фаридович
Но иногда его лучше оставить при себе.
Не затыкайте никому рот.
И это выглядит как - "Не мешайте грабить, господа, а то парежу ножечком".
12 июл 13, 18:43    [14560204]     Ответить | Цитировать Сообщить модератору
 Re: Изменение нескольких элементов в xml-поле  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 467
Спасибо всем, принявшим участие в дискуссии. Свою задачу я решил.
Mnior, ни одного конструктивного замечания, одно словоблудие. Займитесь делом, или хотя бы не мешайте другим.
17 июл 13, 16:39    [14579255]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить