Обновите XML со значением из другого (не XML) столбца в T-SQL

У меня есть таблица с двумя полями типа NUMERIC и одним полем типа XML. Вот грубый образец:

CREATE TABLE books (
        ID INT NOT NULL,
        price NUMERIC(4,2),
        discount NUMERIC(2,2),
        book XML
);

Значение XML будет выглядеть примерно так, скажем,

<?xml version="1.0" encoding="UTF-8"?>
<book>
    <title>Harry Potter</title>
    <author>J K Rowling</author>
    <Store>
        <Name>Burke and Burkins</Name>
        <Address>Some St, Somewhere, Some City</Address>
    </Store>
</book>

Теперь мой вопрос: используя xml.modify(), как я могу добавить два xpath в разделе Store с ценой и скидкой со значением из books.price и books.discount?

<?xml version="1.0" encoding="UTF-8"?>
<book>
    <title>Harry Potter</title>
    <author>J K Rowling</author>
    <Store>
        <Name>Burke and Burkins</Name>
        <Address>Some St, Somewhere, Some City</Address>
        <Price>value from books.price from the same row</Price>
        <Discount>value from books.discount from the same row</Discount>
    </Store>
</book>

Это грубый пример, поэтому, пожалуйста, не беспокойтесь о том, откуда взялись XML-данные. Скажем так, в столбце book уже присутствуют XML-данные.

Я знаю, как обновить таблицу со статическими значениями,

UPDATE books
SET book.modify('insert <Price>10.99</Price><Discount>20.00</Discount> after (/book/Store/Address)[1]')

Производительность здесь не при чем.


xml sql sql-server tsql dml
person Jayachandran    schedule 04.10.2016    source источник

Ответы (1)


arrow_upward
4
arrow_downward

Невозможно сделать две модификации в одном выражении.

В этом случае вы можете обмануть это, сначала объединив оба значения, а затем вставив их сразу.

Для этого я использую обновляемый CTE:

CREATE TABLE books (
        ID INT NOT NULL,
        price NUMERIC(4,2),
        discount NUMERIC(2,2),
        book XML
);

--Заполнить таблицу данными

INSERT INTO books VALUES(1,10.5,.5,
'<book>
    <title>Harry Potter</title>
    <author>J K Rowling</author>
    <Store>
        <Name>Burke and Burkins</Name>
        <Address>Some St, Somewhere, Some City</Address>
    </Store>
</book>');

--Это фактический запрос

WITH CTE AS
(
    SELECT *
          ,(SELECT price AS Price,discount AS Discount FOR XML PATH(''),TYPE) AS XmlNode 
    FROM books
)
UPDATE CTE SET book.modify('insert sql:column("XmlNode") after (/book/Store/Address)[1]');

--Проверить результат

SELECT *
FROM books;

--Clean-Up (осторожнее с настоящей датой!)

GO
--DROP TABLE books;

Один намек

Ваш столбец XML, если это действительно XML, точно будет! - не содержать XML, начинающийся с <?xml version="1.0" encoding="UTF-8"?>. Внутренняя кодировка всегда юникод (ucs-2, что почти utf-16), и это нельзя изменить. Если вы передаете объявление, оно либо пропускается, либо вы получите сообщение об ошибке.

ОБНОВИТЬ

Другой подход заключался в том, чтобы сначала прочитать значения XML, а затем перестроить его:

WITH CTE AS
(
    SELECT *
          ,(SELECT b.value('title[1]','nvarchar(max)') AS [title]
                  ,b.value('author[1]','nvarchar(max)') AS [author]
                  ,b.value('(Store/Name)[1]','nvarchar(max)') AS [Store/Name]
                  ,b.value('(Store/Address)[1]','nvarchar(max)') AS [Store/Address]
                  ,price AS [Store/Price]
                  ,discount AS [Store/Discount]
            FROM book.nodes('book') AS A(b)
            FOR XML PATH('book'),TYPE
            ) AS bookNew
    FROM books
)
UPDATE CTE SET book=bookNew;
person Shnugo    schedule 04.10.2016
comment
Вы правы, у меня нет ‹?xml .. ?› в моем XML. Я скопировал его из редактора, который автоматически добавляет строку. Я попробую ваше решение и дам вам знать, как оно идет. На самом деле это гораздо больший XML с добавлением около 20 полей :). Я проголосую за то, насколько хорошо вы вникли в проблему. Спасибо. - person Jayachandran; 04.10.2016
comment
Первое решение сработало в моем случае, поскольку все узлы, которые я хотел вставить, находились под одним и тем же родителем (Store). Должно быть другое решение, которое использует sql:variable, но я не могу найти ни одного, которое позволило бы мне вставить несколько значений. Поэтому отметьте это как ответ. Спасибо. - person Jayachandran; 04.10.2016