How to add an xml attribute after another specific attribute using T-SQL

user_3965590 0
Viewed 28

I have xml

declare @xml xml = 
'<property>
<address addr="123 Main St" state="FL">
</address>
</property>'

set @xml.modify('insert attribute zip {"33312"} as last into (/property/address)[1]')

select @xml

I'm trying to put zip as the last attribute. I know in XML it doesn't technically matter, but I do have lots of xml that would be much easier to read if I could arrange the attributes as I like. It seems "as first" and "as last" have no effect. How do I accomplish this? Or at least put an attribute at the end?

Thank you.

xml tsql
1 Answers

It really doesn't matter, but if you insist.

SQL

DECLARE @xml xml 
= N'<property>
    <address addr="123 Main St" state="FL">
    </address>
</property>';

SET @xml = @xml.query('<property>
<address>
{
    for $x in /property/address/@*
    return $x, attribute zip {"33312"}
}
</address>
</property>
');

--test
SELECT @xml;

Output

<property><address addr="123 Main St" state="FL" zip="33312" /></property>
user_19323110
Related