Adding multiple attributes in XML node

Question

I want to add two new attributes for all nodes. The attributes name and values are the same for all elements.

I have the following XML structure:

<question id="1001"></question>
<question id="1002"></question>
<question id="1003"></question>
<question id="1004"></question>

and want to transform it as follows:

<question id="1001" test1="1" test2="2"></question>
<question id="1002" test1="1" test2="2"></question>
<question id="1003" test1="1" test2="2"></question>
<question id="1004" test1="1" test2="2"></question>

I was not able to find an example in the documentation of how multiple attributes are added,so I started with adding only the first one:

SELECT T.c.modify('insert attribute test1 {"1"} into (/question)[1]')
FROM @SetQuestionsAndAnswersDetailsXML.nodes('/question') T(c)

But I get the following error:

Msg 8137, Level 16, State 1, Line 89
Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in this context.

Has anyone have an idea what I am doing wrong?

gotqn · Accepted Answer · 2013-05-02 10:06:51Z

1

Unfortunately,

The modify() method of the xml data type can only be used in the SET clause of an UPDATE statement.

So, I need to create a table variable, to insert the records there and then to modify the xmls.

In order to add multiple attributes in one modify insert statement you can use the syntax below:

UPDATE @TempTable
SET XMLColumn.modify('insert (attribute test1 {"1" },attribute test2 {"2"}) into (/question)[1]')

answered May 2, 2013 at 10:06

gotqn

44k47 gold badges167 silver badges256 bronze badges

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

Collectives™ on Stack Overflow

Adding multiple attributes in XML node

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related