Using sql to insert an xml attribute into an xml file

Question

Using Microsoft SQL Server Management Studio, I've inserted an element into the xml file and now wish to insert an attribute so it looks like:

<a><b c="2"/></a>

I created b with:

update #tmp set column.modify('insert <b/> into (/a)[1]')

Now I wish to create c as an attribute of b as shown above. So I try:

update #tmp set column.modify('insert attribute c{''2''} into (/a/@b)[1]')

But I get the error:

XQuery [#tmp.column.modify()]: The target of 'insert into' must be an element/document node, found 'attribute(b,xdt:untypedAtomic)

StuartLC · Accepted Answer · 2014-04-29 17:30:05Z

1

b is an element, not an attribute, so you'll need to drop the @:

update #tmp
  set [column].modify('insert attribute c{''2''} into (/a/b)[1]');

SqlFiddle here

answered Apr 29, 2014 at 17:30

StuartLC

108k18 gold badges225 silver badges294 bronze badges

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

2 Comments

Question_Guy Over a year ago

It works, thank you. I thought c is an attribute though? Do I have them flipped or something? What am I missing? I thought b was the element and what's inside would be an attribute?

StuartLC Over a year ago

c is an attribute, b is an element. The into bit requires an element target (as obviously we can't have attributes with attributes :-)

Collectives™ on Stack Overflow

Using sql to insert an xml attribute into an xml file

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related