Insert xmltype to xmltype at a specified location [PL / SQL]
I have a problem with inserting an xmltype into another xmltype at the specified location in pl / sql.
The first variable v_xml looks like this:
<ord>
<head>
<ord_code>123</ord_code>
<ord_date>01-01-2015</ord_date>
</head>
</ord>
And the second v_xml2:
<pos>
<pos_code>456</pos_code>
<pos_desc>description</pos_desc>
</pos>
My goal is to get something like this:
<ord>
<head>
<ord_code>123</ord_code>
<ord_date>01-01-2015</ord_date>
</head>
<!-- put the second variable in this place - after closing <head> tag -->
<pos>
<pos_code>456</pos_code>
<pos_desc>description</pos_desc>
</pos>
</ord>
What should I do with my code?
declare
v_xml xmltype;
v_xml2 xmltype;
begin
-- some code
-- some code
-- v_xml and v_xml2 has the form as I define above
end;
Can anyone help me with this problem? As I know, there are functions like insertchildxml, appendchildxml or something like this ... I've found some solutions in pure SQL, but I don't know how to do it in PL / SQL.
Thank!
+3
source to share
2 answers
You can use the mentioned appendChildXML like here:
declare
v_xml xmltype := xmltype('<ord>
<head>
<ord_code>123</ord_code>
<ord_date>01-01-2015</ord_date>
</head>
</ord>');
v_xml2 xmltype:= xmltype('<pos>
<pos_code>456</pos_code>
<pos_desc>description</pos_desc>
</pos>');
v_output xmltype;
begin
select appendChildXML(v_xml, 'ord', v_xml2)
into v_output from dual;
-- output result
dbms_output.put_line( substr( v_output.getclobval(), 1, 1000 ) );
end;
Output:
<ord>
<head>
<ord_code>123</ord_code>
<ord_date>01-01-2015</ord_date>
</head>
<pos>
<pos_code>456</pos_code>
<pos_desc>description</pos_desc>
</pos>
</ord>
+2
source to share
appendChildXML is deprecated in 12.1
So here is a solution using XMLQuery
DECLARE
l_head_xml XMLTYPE := XMLTYPE.CREATEXML('<ord>
<head>
<ord_code>123</ord_code>
<ord_date>01-01-2015</ord_date>
</head>
</ord>');
l_pos_xml XMLTYPE := XMLTYPE.CREATEXML('<pos>
<pos_code>456</pos_code>
<pos_desc>description</pos_desc>
</pos>');
l_complete_xml XMLTYPE;
BEGIN
SELECT XMLQUERY('for $i in $h/ord/head
return <ord>
{$i}
{for $j in $p/pos
return $j}
</ord>'
PASSING l_head_xml AS "h",
l_pos_xml AS "p"
RETURNING CONTENT)
INTO l_complete_xml
FROM dual;
dbms_output.put_line(l_complete_xml.getstringval());
END;
+2
source to share