CODEWITHSUNDEEP
sql-serverxmlt-sqlxml-parsing
Mar1009
Mar1009

Reputation: 811

How to insert in XML value

I have a XML type field and I need to update a value inside that XML field, when I done updating the element the rest of the element should be renumbered in it.

Sample structure

create table sampleTable (recID int, recValue xml)

declare @xmlVal xml = ' 
  <row>
  <c1>1234</c1>
  <c2>John</c2>
  <c3>Doe</c3>
  <c4>India</c4>
  </row>'

insert into sampleTable 
values(1,@xmlVal)

Here I need to insert a new element inside that XML lets say, I need to another 'C' element with a value c2 = 'brother' and subsequent c should be renumbered to c3,c4 and c5.

excepted output on how to insert a new value in xml field;

 <row>
  <c1>1234</c1>
  <c2>brother</c2>
  <c3>John</c3>
  <c4>Doe</c4>
  <c5>India</c5>
  </row>

here how do I write a query to achieve this outout, please advice..thanks in advance

Upvotes: 0

Views: 212

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

This is a reall, really bad approach...

You should never ever name-number your tags (e.g. <c1><c2>...).

The worst idea was to do this without any reasonable goal. The consumer of your XML will not know, which information is <c3>. Is it the sur-name or the first-name?

Furthermore, there is absolutely no advantage. XML has a given node-order per definition. Using just <c> for all your elements was exactly the same...

If you really want to carry a positional information with the element you should rather use something along this:

<c pos="1">blah1</c>
<c pos="2">blah2</c>
...

There are approaches to solve your issue in the way you like it, but - honestly - I would have to wash my hands after writing this. So please tell me first, if you really need this (maybe due to unchangable / third-party environments) or if you are open for a structural change.

Upvotes: 1

Related Questions