Update XML with value from another (non-xml) column in T-SQL

Question

I have a table with two fields of type NUMERIC and one field of type XML. Here is a rough sample:

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

The XML value will look something like, say,

<?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>

Now my question is, using xml.modify(), how can I add two xpaths under Store with the price and discount with value from books.price and 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>

This is a rough example, so please don't worry about where the XML data came from. Lets just say the book column has the XML data already present.

I know how to update the table with static values with,

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

Performance is not a consideration here.

Gottfried Lesigang · Accepted Answer · 2018-08-08 07:46:28Z

4

It is not possible to do two modifications in one statement.

In this case you might trick this out by first combining both values and then insert them at once.

I use an updateable CTE to achieve this:

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

--Fill the table with data

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>');

--This is the actual query

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]');

--Check the result

SELECT *
FROM books;

--Clean-Up (carefull with real date!)

GO
--DROP TABLE books;

One hint

Your XML column, if it is really XML, will - for sure! - not contain an XML starting with <?xml version="1.0" encoding="UTF-8"?>. The internal encoding is always unicode (ucs-2, which is almost utf-16) and one cannot change this. If you pass in a declaration, it is either ommited or you'll get an error.

UPDATE

Another approach was to first read the XML's values and then to rebuild it:

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;

edited Aug 8, 2018 at 7:46

answered Oct 4, 2016 at 2:15

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jay Over a year ago

You are right, I don't have the <?xml .. ?> in my XML. I copied it from an editor which automatically adds the line. I will try out your solution and let you know how it goes. In reality its a much bigger XML with some 20 fields to be added :). I will give a upvote for how well you have dug in to the problem. Thanks.

Jay Over a year ago

The first solution worked in my case since all the nodes I wanted to insert were under the same parent (Store). There must be another solution out there which uses sql:variable but I can't find any which will let me insert multiple values. So marking this as answer. Thanks.

Collectives™ on Stack Overflow

Update XML with value from another (non-xml) column in T-SQL

1 Answer 1

One hint

UPDATE

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

One hint

UPDATE

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related