Insert specific XML node in specific location using SQL

Question

I am looking for a way to insert a specific XML node in a specific location. Below is the example:

<Car>
     <Brand>Toyota</Brand>
     <Color>Red</Color>
     <Price>10000</Color>
</Car>

Say if I were to want to insert a node <Year>2012</Year> in between <Color> and <Price> node, what should I do?

Do note that replacing whole XML doc is not an option in my current scenario. Thanks

@NoChance Hi, was looking for away to do it through T-SQL directly instead. Any idea? Thanks — thebernardlim
– thebernardlim, Commented Aug 15, 2016 at 2:54
What flavor of SQL? SQL Server, Oracle, MySQL, Postgre, or something else? — har07
– har07, Commented Aug 15, 2016 at 4:44

har07 · Accepted Answer · 2016-08-15 07:37:39Z

5

You can use XQuery insert ... after ... statement to insert Year element right after Color :

declare @xml XML = '<Car>
     <Brand>Toyota</Brand>
     <Color>Red</Color>
     <Price>10000</Price>
</Car>'

SET @xml.modify('
    insert <Year>2012</Year>
    after (//Color)[1]
')

SELECT @xml

output :

<Car>
  <Brand>Toyota</Brand>
  <Color>Red</Color>
  <Year>2012</Year>
  <Price>10000</Price>
</Car>

answered Aug 15, 2016 at 7:37

har07

89.5k12 gold badges87 silver badges143 bronze badges

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

Collectives™ on Stack Overflow

Insert specific XML node in specific location using SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related