Alright guys, here goes a good ol' rant about the XML functionalities of SQL Server, and how I could, or should implement some like operations with PL-SQL/Oracle.
As some of you already know, I'm migrating a SQL Server database to Oracle 12c. This means translating the scripts.
SQL Server has a .value() extension function, and it also treats scalar queries like variables. So I often see calls to .value() similar to this one:
var := (select '
<cat>
<meow>
<burrito num="7">Extra cheese</burrito>
</meow>
</cat>
' from dual).value('(/cat/meow/burrito/@num)[1]', 'int')
Once executed, 'var' will contain the attribute value of the burrito node, so 7. The arguments being supplied to .value() are as follows: (According to the Microsoft documentation here.)
'(/cat/meow/burrito/@num)[1]'
Is the XQuery expression, a string literal, that retrieves data inside the XML instance.
- Per static typing requirements, [1] is added at the end of the path expression in the value() method to explicitly indicate that the path expression returns a singleton.
'int'
Is the preferred SQL type to be returned. The return type of this method matches the SQLType parameter. SQLType cannot be an xml data type, a common language runtime (CLR) user-defined type, image, text, ntext, or sql_variant data type. SQLType can be an SQL, user-defined data type.
Enough About value(). Let's get back to the question at hand.
Let's say I have a CLOB type variable that I'm using to store the same XML string in, in a PL-SQL procedure. How should I extract that same attribute value into another variable? And, better yet: How Should I extract a child node and it's children into another variable?
DECLARE
var clob := '
<cat>
<meow>
<burrito num="7">Extra cheese</burrito>
</meow>
</cat>
';
num number;
BEGIN
select ? into num from dual;
END;
/
I've been poking around Stack Overflow for answers, but I'd rather discuss this here. There's totally a library for this sort of thing. Hopefully someone is well-versed with it and can school us on XML in Oracle.
Cheers.