XMLQuery to INSERT/DELETE XML Elements

Question

I am trying to get a taste of XML manipulation in Oracle PLSQL. All the examples are of modifying XML stored in table column but I am trying to modify a XML variable.

Here is my code -

declare
l_xml XMLTYPE;
l_clob CLOB;

begin
l_xml:=XMLTYPE('<bookstore>
<book category="cooking">
<title lang="en">Everyday Italian</title>
<author>Giada De Laurentiis</author>
<year>2005</year>
<price>30.00</price>
</book>
<book category="children">
<title lang="en">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
<book category="web">
<title lang="en">XQuery Kick Start</title>
<author>James McGovern</author>
<author>Per Bothner</author>
<author>Kurt Cagle</author>
<author>James Linn</author>
<author>Vaidyanathan Nagarajan</author>
<year>2003</year>
<price>49.99</price>
</book>
<book category="web" cover="paperback">
<title lang="en">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
</bookstore>');

select XMLQuery('copy $tmp := $p modify insert node 
<Pages>260 </Pages>
as last into $tmp/bookstore/book
return $tmp
'
passing l_xml as "p"
returning CONTENT).getCLobVal() into l_clob from dual;
DBMS_OUTPUT.PUT_LINE(l_clob);

end;

Can someone please let me know what I am doing wrong- if possible can you please show couple of examples..

What are you expecting to see? – OldProgrammer Oct 23 '19 at 17:48 — OldProgrammer, Oct 23 '19 at 17:48
a child node inserted under book node. – redsoxlost Oct 23 '19 at 17:53 — redsoxlost, Oct 23 '19 at 17:53

VBoka · Answer 1 · 2019-10-23T20:12:57.493

I hope this will help(I use Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production) and it is ok for me:

Here is the code that will insert tag 'Pages' in the right place:

select INSERTCHILDXML(l_xml,
   '/bookstore/book', 'Pages',
   XMLType('<Pages>260</Pages>')) into l_xml
   from dual;

And here is that inside of the begin-end:

/*from this part it is the same code as you have given us*/
declare
l_xml XMLTYPE;
l_clob CLOB;

begin
l_xml:=XMLTYPE('<bookstore>
<book category="cooking">
<title lang="en">Everyday Italian</title>
<author>Giada De Laurentiis</author>
<year>2005</year>
<price>30.00</price>
</book>
<book category="children">
<title lang="en">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
<book category="web">
<title lang="en">XQuery Kick Start</title>
<author>James McGovern</author>
<author>Per Bothner</author>
<author>Kurt Cagle</author>
<author>James Linn</author>
<author>Vaidyanathan Nagarajan</author>
<year>2003</year>
<price>49.99</price>
</book>
<book category="web" cover="paperback">
<title lang="en">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
</bookstore>');
/*to this part it is the same code as you have given us*/

/*this is just one example how you can add <Pages> tag*/
select INSERTCHILDXML(l_xml,
   '/bookstore/book', 'Pages',
   XMLType('<Pages>260</Pages>')) into l_xml
   from dual;

/*this part is so you can see what you did :)*/
select l_xml.getClobVal() 
into l_clob from dual;

DBMS_OUTPUT.PUT_LINE(l_clob);

end;

Thanks... This is one solution. But the implemention I am looking at using xmlquery because oracle has deprecated the use of standard oracle functions — redsoxlost, Oct 24 '19 at 03:29

score 1 · Accepted Answer · answered Oct 24 '19 at 06:42

1

Your query is returning Invalid target expression. Because this part $tmp/bookstore/book is returning collection of book node not a single element.

Few examples how to do this correctly
1) $tmp/bookstore/book[@category eq "cooking"] insert into book where attribute category = "cooking"
2) $tmp/bookstore/book[2] insert into 2-nd boook in your xml.
3) Insert pages into each books

XMLQuery('copy $tmp := $p modify 
(for $book in $tmp/bookstore/book return insert node <Pages>260 </Pages> into $book)
return $tmp'
passing l_xml as "p"
returning CONTENT)

Note
To get clob value from xml is better to use xmlserialize

answered Oct 24 '19 at 06:42

Arkadiusz Łukasiewicz

6,241
1
11
17

Can you please explain what you did here - XMLQuery('copy $tmp := $p modify (for $book in $tmp/bookstore/book return insert node 260 into $book) return $tmp' passing l_xml as "p" returning CONTENT) I can see 2 return statements here. – redsoxlost Oct 24 '19 at 18:40
1

Beauty of Xquery :), In this statement there are two block of code. 1st- Internal (` for .. return `) and 2-nd external `copy modift return`. Return in 1st block is passing values to external block, Rturn in 2nd is passing value to "sql". – Arkadiusz Łukasiewicz Oct 25 '19 at 08:50

XMLQuery to INSERT/DELETE XML Elements

2 Answers2