Add an attribute to the root element

Question

How can I add attribute to the root element of any XML documents which is a column in a table called book

this table contains bookid int
                    title  varchar(10)
                    Author varchar(10)
                    Order XML

order is an xml object in that table and it contains the folllowing

<buyOrder ordernum="10">
  <date>2010-12-3</date>
  <shippingdate>2010-12-5</shippingdate>
  <delivery>2010-12-12</delivery>
</buyOrder>

I want to add attribute called cost to the root element ?

How can I do that please?

you mean you want to update what is already in the Order field with the exact same XML but with the new attribute? — Leslie, Feb 28 '11 at 16:11

score 2 · Answer 1 · answered May 22 '11 at 18:01

2

declare @Cost money = 10  

update book set
  [Order].modify('insert attribute cost {sql:variable("@Cost")} into (buyOrder[1])')

answered May 22 '11 at 18:01

Mikael Eriksson

1 Answers1