Hi,
Following on from my question the other day (Adding multiple different nodes from one xmltype into another), I now have a slightly more complex requirement that I cannot work out where to start, assuming that it's something that can reuse some/all of yesterday's work (thanks again, @"odie_63"!). ETA: I'm on 11.2.0.3
So, here's the (slightly amended) xml along with yesterday's solution:
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>
<multinode>
<name>fred</name>
<type>book</type>
<head>1</head>
</multinode>
<multinode>
<name>bob</name>
<type>car</type>
<head>0</head>
</multinode>
</a>') xml_to_extract_from
from dual)
select xmlserialize(document
xmlquery(
'copy $d := $old
modify (
insert node element extrainfo {
$new/a/b
, $new/a/d
, $new/a/f
, $new/a/h
} as first into $d/root
)
return $d'
passing sd.xml_to_update as "old"
, sd.xml_to_extract_from as "new"
returning content
)
indent
)
from sample_data sd;
That gives me:
<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>
However, I now need to add in a set of new nodes based on information from the <multinode> nodes, something like:
<root>
<extrainfo>
<b>valb</b>
<d>
<d1>vald1</d1>
<d2>vald2</d2>
</d>
<f>valf</f>
<h>
<h1>valh1</h1>
<h2>valh2</h2>
</h>
<newnode>
<name>fred</name>
<type>book</type>
</newnode>
<newnode>
<name>bob</name>
<type>car</type>
</newnode>
</extrainfo>
<xmlnode>
<subnode1>val1</subnode1>
<subnode2>val2</subnode2>
<type>book</type>
</xmlnode>
<xmlnode>
<subnode1>val3</subnode1>
<subnode2>val4</subnode2>
<type>car</type>
</xmlnode>
</root>
If it's easier, I *think* we would be ok with something like:
...
<newnode>
<type name="fred">book</type>
<type name="bob">car</type>
</newnode>
...
The closest I've come is:
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>
<multinode>
<name>fred</name>
<type>book</type>
<head>1</head>
</multinode>
<multinode>
<name>bob</name>
<type>car</type>
<head>0</head>
</multinode>
</a>') xml_to_extract_from
from dual)
select xmlserialize(document
xmlquery(
'copy $d := $old
modify (
insert node element extrainfo {
$new/a/b
, $new/a/d
, $new/a/f
, $new/a/h
, element newnode {
$new/a/multinode/name
,$new/a/multinode/type
}
} as first into $d/root
)
return $d'
passing sd.xml_to_update as "old"
, sd.xml_to_extract_from as "new"
returning content
)
indent
) fred
from sample_data sd;
Which produces:
...
<newnode>
<name>fred</name>
<name>bob</name>
<type>book</type>
<type>car</type>
</newnode>
...
- obviously not right!
Can anyone provide any hints? I've tried searching for similar examples, but I mustn't be putting in the right search terms or something!