Search code examples
sqlxmloraclexqueryxquery-update

Move a node and its descendents from parent to a sibling


I have an XML tree as follows:

<root>
    <a>
        <a1>A1</a1>
        <a2>A2</a2>
        ...
        ...
        <an>An</an>
    </a>
    <b>
        <b1>B1</b1>
        <b2>B2</b2>
        ...
        ...
        <bm>Bm</bm>
    </b>
    <x>
        <x1>X1</x1>
        <x2>X2</x2>
        ...
        ...
    </x>
    <y>
        ...
        ...
    </y>
    ...
    ...
</root>

Using Oracle XMLQuery and XQuery, I want to change this XML to:

<root>
    <a>
        <a1>A1</a1>
        <a2>A2</a2>
        ...
        ...
        <an>An</an>
        <b>
            <b1>B1</b1>
            <b2>B2</b2>
            ...
            ...
            <bm>Bm</bm>
        </b>
    </a>
    <x> <!-- This x node and all its siblings have to stay where they are. -->
        <x1>X1</x1>
        <x2>X2</x2>
        ...
        ...
    </x>
    <y>
        ...
        ...
    </y>
    ...
    ...
</root>

In short, I want to put the entire b node and its descendents under a by appending.

What I have tried so far is:

WITH xdata AS (
SELECT XMLTYPE('<root>
                    <a>
                        <a1>A1</a1>
                        <a2>A2</a2>
                    </a>
                    <b>
                        <b1>B1</b1>
                        <b2>B2</b2>
                    </b>
                    <x>
                        <x1>X1</x1>
                        <x2>X2</x2>
                    </x>
                </root>') AS xmldata
  FROM dual
)
SELECT XMLQuery ('for $a in $x1/root/a, $b in $x1/root/b
                  return <root>{$a}{$b}</root>'
                 PASSING x.xmldata AS "x1" RETURNING CONTENT) AS output
  FROM xdata x;

This obviously gives me a very wrong result. Maybe what I'm trying is completely wrong. Please help.


Solution

  • SQL> WITH xdata AS (
      2  SELECT XMLTYPE('<root>
      3                      <a>
      4                          <a1>A1</a1>
      5                          <a2>A2</a2>
      6                      </a>
      7                      <b>
      8                          <b1>B1</b1>
      9                          <b2>B2</b2>
     10                      </b>
     11                  </root>') AS xmldata
     12    FROM dual
     13  )
     14  SELECT XMLQuery ('for $v in $x1/root return <root><a>{$x1//root/a/*}{$x1//root/b}</a></root>'
     15                   PASSING x.xmldata AS "x1" RETURNING CONTENT) AS output
     16    FROM xdata x;
    
    OUTPUT                                                                          
    --------------------------------------------------------------------------------
    <root><a><a1>A1</a1><a2>A2</a2><b><b1>B1</b1><b2>B2</b2></b></a></root>    
    

    For more complex case:

    SQL> WITH xdata AS (
      2  SELECT XMLTYPE('<root>
      3                      <a>
      4                          <a1>A1</a1>
      5                          <a2>A2</a2>
      6                      </a>
      7                      <b>
      8                          <b1>B1</b1>
      9                          <b2>B2</b2>
     10                      </b>
     11                      <x>
     12                          <x1>X1</x1>
     13                          <x2>X2</x2>
     14                      </x>
     15                      <y>
     16                          <y1>Y1</y1>
     17                          <y2>Y2</y2>
     18                      </y>
     19                  </root>') AS xmldata
     20    FROM dual
     21  )
     22  SELECT XMLQuery ('for $v in $x1/root
     23  return <root><a>{$x1/root/a/*}{$x1/root/b}</a>
     24  {for $t in $x1/root/* where $t!=$v/a and $t!=$v/b return $t}</root>'
     25  PASSING x.xmldata AS "x1" RETURNING CONTENT) AS output
     26  FROM xdata x
     27  /
    
    OUTPUT                                                                          
    --------------------------------------------------------------------------------
    <root><a><a1>A1</a1><a2>A2</a2><b><b1>B1</b1><b2>B2</b2></b></a><x><x1>X1</x1><x
    2>X2</x2></x><y><y1>Y1</y1><y2>Y2</y2></y></root>