juniordeveloper87 juniordeveloper87 - 3 months ago 13
SQL Question

Insert specific XML node in specific location using SQL

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

Answer

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>
Comments