Hi,
Given the following two bits of XML:
<root>
<xmlnode>
<subnode1>val1</subnode1>
<subnode2>val2</subnode2>
</xmlnode>
<xmlnode>
<subnode1>val3</subnode1>
<subnode2>val4</subnode2>
</xmlnode>
</root>
<a>
<b>valb</b>
<c>valc</c>
<d>
<d1>vald1</d1>
<d2>vald2</d2>
</d>
<e>vale</e>
<f>valf</f>
<g>
<g1>valg1</g1>
<g2>valg2</g2>
</g>
<h>
<h1>valh1</h1>
<h2>valh2</h2>
</h>
</a>
I need to insert some of the nodes from the second xml into a new node at the top of the first xml, like so:
<root>
<extrainfo>
<b>valb</b>
<d>
<d1>vald1</d1>
<d2>vald2</d2>
</d>
<f>valf</f>
<h>
<h1>valh1</h1>
<h2>valh2</h2>
</h>
</extrainfo>
<xmlnode>
<subnode1>val1</subnode1>
<subnode2>val2</subnode2>
</xmlnode>
<xmlnode>
<subnode1>val3</subnode1>
<subnode2>val4</subnode2>
</xmlnode>
</root>
Back when I thought I only had to add a couple of nodes (eg. b and f), I used:
with sample_data as (select xmltype('<root>
<xmlnode>
<subnode1>val1</subnode1>
<subnode2>val2</subnode2>
</xmlnode>
<xmlnode>
<subnode1>val3</subnode1>
<subnode2>val4</subnode2>
</xmlnode>
</root>') xml_to_update,
xmltype('<a>
<b>valb</b>
<c>valc</c>
<d>
<d1>vald1</d1>
<d2>vald2</d2>
</d>
<e>vale</e>
<f>valf</f>
<g>
<g1>valg1</g1>
<g2>valg2</g2>
</g>
<h>
<h1>valh1</h1>
<h2>valh2</h2>
</h>
</a>') xml_to_extract_from
from dual)
select sd.xml_to_update,
sd.xml_to_extract_from,
insertXMLbefore(sd.xml_to_update,
'/root/xmlnode[1]',
xmltype('<extrainfo>'||
'<b>'||
extractvalue(sd.xml_to_extract_from, '/a/b/text()')||
'</b><f>'||
extractvalue(sd.xml_to_extract_from, '/a/f/text()')||
'</f>'||
'</extrainfo>')) updated_xml
from sample_data sd;
which did work, but as I now need to add several other nodes, I don't think this is the wisest way to keep using. I've tried googling and searching the forums but my search-fu must be weak today, as I haven't found any similar examples that I can copy base my solution on. I'm pretty sure there's a funky way of doing it using xquery or something, but I have to admit to not having a clue as to where to even start with xquery!
We're on 11.2.0.3, and the xmltype columns are stored as clobs (sorry @"MarcoGralike"!).
Can anyone help to point me in the right direction, please?