Application development with DB2 pureXML - DB2 Express-C: The Developer for XML, PHP, C/C++, Java, and .NET - page 90

2.2.13 XML type support in stored procedures
Stored procedures are enabled to accept the XML parameter values.
SQL stored procedures
In SQL, stored procedure parameters of type XML look just like the variables of
other data type.
Example 2-26 shows a simple SQL stored procedure, which takes three input
parameters. The first parameter is of XML type and the other two are of integer
type. The procedure checks if the ID value in the XML parameter review is equal
to the ID values passed as the argument ID . If yes, the procedure inserts the
review in the MOVIEREVIEW table with the review ID equal to the value of the
reviewid parameter.
Example 2-26 Stored procedure with XML data type input
create procedure proc1 (IN review XML, IN id int, IN reviewid int)
language SQL
BEGIN
DECLARE var1 XML;
if(XMLEXISTS('$x/movie[@id=$d]' passing review as "x", id as "d"))
then
insert into moviereview values(reviewid, review);
end if;
end
Example 2-27 calls the procedure that was created in Example 2-26.
Example 2-27 Call stored procedure passing XML parameter
call proc1(XMLPARSE(DOCUMENT('<movie id="111">
<reviews>
<UserReview>
<user name="Andy">Three is a crowd, yet there are not enough stars to support
this catastrophe.</user>
<user name="Linda">Three is a crowd, yet there are not enough stars to support
this catastrophe.</user>
</UserReview>
<CriticsReview>
<newspaper name="ABC Times">The best mystery thriller of the year. </newspaper>
<newspaper name="San Francisco Post"> The best mystery thriller of the year.
</newspaper>
</CriticsReview>
</reviews></movie>')),111,222)@