Tell me, is there any analogue of CASE WHEN when working with XML? Suppose I need to do different insertions in XML depending on the value of the XML node.

Closed due to the fact that the essence of the question is not clear to the participants user207618, cheops , Suvitruf ♦ , Streletz , HamSter 2 Oct '16 at 13:42 .

Try to write more detailed questions. To get an answer, explain what exactly you see the problem, how to reproduce it, what you want to get as a result, etc. Give an example that clearly demonstrates the problem. If the question can be reformulated according to the rules set out in the certificate , edit it .

  • Um .. Xml is turned into a table by some kind of command. What exactly is the problem? - Qwertiy ♦
  • There is a node, for example, flag. Depending on its content, certain children are deleted. Is it possible to do some branching in xml.modify ('delete')? - iluxa1810

1 answer 1

In XQuery, you can use the traditional if-then-else conditional statement .

declare @foo table ( bar xml ) insert @foo values(' <root> <flag>1</flag> <a>a</a> <b>b</b> </root>') select * from @foo update @foo set bar.modify('delete if (data(//flag) = "1") then (//a) else (//b)') select * from @foo

To change the xml, given in the comments, you need to use the cycle :

 update @foo set bar.modify(' delete for $row in /root/TableRow return if ($row/flag = 1) then $row/a else $row/b ')

We loop through all TableRow nodes, and depending on the value of the flag node, we return the node a or b , which will be deleted.

  • But in such an XML, how to do that in the first case the node a would be deleted, and in the second, the node b? <Root> <TableRow> <flag> 1 </ flag> <a> a </a> <b> b < / b> </ TableRow> <TableRow> <flag> 2 </ flag> <a> a </a> <b> b </ b> </ TableRow> </ root> Altova XmlSpy gives me in both cases a . - iluxa1810
  • @ iluxa1810 - Do not put the code in the comments. Edit the question by adding code and other additional information. And in the comments specify: "Updated the question." PS: Updated the answer. - Alexander Petrov
  • Is it possible to perform the replace value of the same through the same cycle? - iluxa1810
  • @ iluxa1810 - As far as I know, no, it is impossible. The fact is that replace value of processes only one node at a time. If you really need an answer, create a new topic. Maybe someone else will tell, and in the comments your question other than me (and, perhaps, moderators) is unlikely to be noticed. - Alexander Petrov