I have the following piece of XML:
<per:Person xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.something.com/2014/11/bla/webservice.xsd"
xmlns:per="http://www.something.com/2014/11/bla/person">
<per:Initials>E.C.</per:Initials>
<per:FirstName>Erik</per:FirstName>
<per:LastName>Flipsen</per:LastName>
<per:BirthDate>1980-07-01</per:BirthDate>
<per:Gender>Male</per:Gender>
</per:Person>
From this xml I want to extract some data in PL/SQL. I'd like to use XMLTABLE, since the EXTRACT and EXTRACTVALUE functions are deprecated.
I am able to extract the data using this query:
select pers.Initials,
pers.Firstname
into lsInitials,
lsFirstname
from
XMLTABLE ('*:Person' passing pxRequest
columns Initials PATH '*:Initials',
Firstname PATH '*:FirstName'
) pers;
I'm using wildcards for the namespaces since I don't really care what abbreviations the sending party is using for the namespace, I know the exact path where to get my data anyway.
With this code I have two things that puzzle me:
Edit:
I found out that when I remove the namespaces for the elements, and made them uppercase, it works. So it seems like the column names need to match the xml elements names to make it work. I didn't yet figure out how to make it work with namespaced XML.
Runnable code sample:
SET SERVEROUTPUT ON;
DECLARE
pxRequest xmltype := xmltype('<per:Person xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.something.com/2014/11/bla/webservice.xsd"
xmlns:per="http://www.something.com/2014/11/bla/person">
<per:Initials>E.C.</per:Initials>
<per:FirstName>Erik</per:FirstName>
<per:LastName>Flipsen</per:LastName>
<per:BirthDate>1980-01-01</per:BirthDate>
<per:Gender>Male</per:Gender>
</per:Person>');
lsInitials varchar2(100);
lsFirstname varchar2(100);
begin
select pers.Initials,
pers.Firstname
into lsInitials,
lsFirstname
from
XMLTABLE ('*:Person' passing pxRequest
columns Initials PATH '*:Initials',
Firstname PATH '*:FirstName'
) pers;
dbms_output.put_line(lsInitials);
dbms_output.put_line(lsFirstname);
end;
As per your first question, the documentation you linked has this to day about omitting PATH
:
The optional
PATH
clause specifies that the portion of the XQuery result that is addressed by XQuery expression string is to be used as the column content.If you omit
PATH
, then the XQuery expression column is assumed. For example:
(... COLUMNS xyz)
is equivalent to
XMLTable(... COLUMNS xyz PATH 'XYZ')
You can use different PATH clauses to split the XQuery result into different virtual-table columns.
The reason the column xyz
is assumed to be 'XYZ'
is because Oracle, by default, is case insensitive (defaults to all-caps). If you had defined your column as "aBcD"
then the PATH
value will be assumed to be 'aBcD'
As for your second question about specifying data types: if the data you're extracting is always going to be text data, you might be able to get away with not specifying a data type.
However, if you start dealing with things like dates, timestamps, floating point numbers, etc, then you may run into issues. You'll either need to manually convert them using the TO_*
functions or you can specify their data types in the column definitions. If you don't, Oracle is free to implicitly cast it however it feels fit, which may have unexpected consequences.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With