dattaprasaddhuri Ответов: 1

Как обновить значение нескольких узлов XML в SQL server?

Пожалуйста, обратитесь к следующему xml-файлу:-
<test>
<Address>
    <ID>1</ID>
    <CITY>Mumbai</CITY>
 </Address>
<Address>
    <ID>12/ID>
    <CITY>Puna</CITY>
 </Address>
<Address>
    <ID>13/ID>
    <CITY>puna</CITY>
 </Address>
</test>


Я хочу обновить City =Mumbai во всех городских узлах в sql.

Что я уже пробовал:

Я попробовал следующий код в sql, но он обновляет только одно значение, и я хочу заменить все значения в sql.
Пожалуйста помочь

 Declare @xml xml
declare @C varchar(50)
set @C='Mumbai'
set @xml='<test>
<Address>
    <ID>1</ID>
    <CITY>Mumbai</CITY>
 </Address>
<Address>
    <ID>12/ID>
    <CITY>Puna</CITY>
 </Address>
<Address>
    <ID>13/ID>
    <CITY>puna</CITY>
 </Address>
</test>'

SET  @xml.modify('replace value of (/test/Address/CITY/text())[1] with sql:variable("@C")')


приведенный выше оператор xml set обновляет только одно значение, и я хочу обновить все городские узлы

1 Ответов

Рейтинг:
0

CHill60

Во - первых, в вашем xml есть опечатки-он плохо сформирован - см. закрывающие теги ID 12 и ID 13. Так и должно быть 

declare @C varchar(50) = 'Mumbai'
declare @xml xml = N'<test>
<Address><ID>1</ID><CITY>London</CITY></Address>
<Address><ID>12</ID><CITY>Puna</CITY></Address>
<Address><ID>13</ID><CITY>puna</CITY></Address>
</test>'
Во-вторых, трудно сказать, что вы обновили только одно значение, так как "Мумбаи" был значением первого экземпляра города в любом случае. Я изменил это на "Лондон" в моем тестовом коде.

Теперь перейдем к решению. Вы можете изменить только один узел за один раз, используя .modify поэтому вам нужно будет использовать петлю. Обычно я бы не рекомендовал использовать цикл для чего-либо в SQL, но это не таблица как таковая.

Чтобы получить количество раз, которое вам нужно выполнить цикл, вам нужно изучить идентификационные узлы. Это не так просто, как просто пересчитать их - когда я пытался использовать 
SET @xml.modify('replace value of (//test/Address/CITY/text())[sql:variable("@COUNT")] with (sql:variable("@C"))')
Я получил постоянные ошибки типа "цель 'replace' должна быть не более одного узла", поэтому мне нужно найти самое высокое значение ID, с которым я могу это сделать 
DECLARE @COUNT INT
SET @COUNT = @XML.value ('max(/test/Address/ID)', 'int');
Затем мне нужно поместить обновление в цикл с небольшим выбором xquery, как это
WHILE @COUNT > 0
BEGIN
	SET @xml.modify('replace value of (//test/Address[ID=sql:variable("@COUNT")]/CITY/text())[1] with (sql:variable("@C"))')
    SET @COUNT = @COUNT - 1
END
Обратите внимание, что поскольку значения идентификаторов не являются смежными, цикл ничего не будет делать на большинстве итераций, т. е. когда @COUNT = 11,10,9,8,7,6,5,4,3 и 2

--------------------------------------

Я сказал, что мне не нравится использовать циклы в SQL, и я действительно имел это в виду, так что вот альтернативный и, для меня, гораздо более элегантный подход.

1) создайте таблицу из XML-файла
DECLARE @temp table (ID int, CITY varchar(max))
INSERT INTO @temp
SELECT [ID] = Node.Data.value('(ID)[1]', 'INT'),
       [CITY] = Node.Data.value('(CITY)[1]', 'VARCHAR(MAX)')
FROM    @XML.nodes('/test/Address') Node(Data)
2) Обновите таблицу в виде набора
UPDATE @temp SET CITY = @C
3) преобразовать таблицу в XML
SELECT ID, CITY
FROM @temp as [Address]
FOR XML auto,ROOT('test'),ELEMENTS