Adding multiple attributes in XML node

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?

Answers


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

Need Your Help

textarea fills table cell in jsfiddle but not in real application

jquery html css

This is strange but I have a jsfiddle where when the user clicks on the "Add Question" button to append a table row, under the "Question" column in the appended row, the height and the width of the

When should ThreadLocal be used instead of Thread.SetData/Thread.GetData?

c# multithreading c#-4.0

Prior to .net 4.0, I implemented a solution using named data slots in System.Threading.Thread. Now, in .net 4.0, there is the idea of ThreadLocal. How does ThreadLocal usage compare to named data

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.