Saturday, June 12, 2010

Bookmark and Share

With the help of Oracle's extractValue() function one can retrieve values from XML documents stored in a database using XPath expressions.

Generally this function works as expected but it gave me a hard time when XML namespaces came into play. As I didn't find much related information on the web I thought a short example might be helpful for others facing the same problem.

Let's take the web service from the video rental shop from my recent post Integrating JAX-WS with XmlBeans as example.

For auditing purposes all requests and responses of the web service might be logged in a database table REQUESTS created as follows:

1
2
3
4
5
CREATE TABLE REQUESTS (
    ID NUMBER(10,0) PRIMARY KEY,
    REQUEST XMLTYPE,
    RESPONSE XMLTYPE
);

In reality, logging would probably be implemented using a message handler, but for demonstration purposes let's simply insert a sample request of the FindMoviesByDirector() operation using SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
INSERT INTO REQUESTS (ID, REQUEST, RESPONSE)
VALUES (
    1,
    '<soapenv:Envelope 
        xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
        xmlns:typ="http://www.gunnarmorling.de/moapa/videorental/types">

        <soapenv:Header/>
        <soapenv:Body>
            <typ:FindMoviesByDirectorRequest>
                <Director>Bryan Singer</Director>
            </typ:FindMoviesByDirectorRequest>
        </soapenv:Body>
     </soapenv:Envelope>',
    '...';

Here we have two namespace aliases declared, soapenv for the SOAP message and typ for the actual message content.

The key for accessing values from this document using extractValue() is the pretty poorly documented optional parameter namespace_string, which can be used to declare any namespaces. This has to happen in the form xmlns:alias="URI", multiple namespaces must be separated by a space character.

Knowing that, it's easy to issue a SQL query which retrieves the director name from the request above. Just make sure to qualify the element names with their namespace alias in the XPath expression:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
    req.id,
    extractValue(
        req.request,
        '/soapenv:Envelope/soapenv:Body/typ:FindMoviesByDirectorRequest/Director',
        'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://www.gunnarmorling.de/moapa/videorental/types"') Title
FROM 
    requests req
WHERE
    id = 1
;

ID   TITLE 
-----------------
1    Bryan Singer

15 comments:

Ruben said...

I was trying all morning to seperate namespace strings with a comma after reading http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions052.htm . Thanks a lot for pointing out that i should use a space character!

Gunnar Morling said...

Glad to hear that I could help :-) It took me a while to find this out, too.

Anonymous said...

I have a XML like this

< nfeProc versao="2.00" >
< NFe xmlns="http://www.portalfiscal.inf.br/nfe" >
< infNFe xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Id="NFe35100743283811003922550010000234971001098588" versao="2.00" >
< ide >
< cUF >35< /cUF >
< cNF >00109858< /cNF >
< natOp >SAIDA DE VENDA< /natOp >
< indPag >0< /indPag >
< mod >55
< serie >1< /serie >
< nNF >23497< /nNF >
< dEmi >2010-07-16< /dEmi >
< tpNF >1< /tpNF >
< cMunFG >3505708< /cMunFG >
< tpImp >1< /tpImp >
< tpEmis >1< /tpEmis >
< cDV >8< /cDV >
< tpAmb >1< /tpAmb >
< finNFe >1< /finNFe >
< procEmi >0< /procEmi >
< verProc >DllZinha 1.0< /verProc >
< /ide >

And I´m trying to query data from this XML using this:

SELECT EXTRACTVALUE(X.XML_NFE,
'/nfeProc/NFe/infNFe/@Id',
'xmlns="http://www.portalfiscal.inf.br/nfe" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"') CHAVE
FROM NFE_XML X
WHERE X.SEQ_NFE = 643;

But returns null. Do you have any idea what´s wrong?

Tks.
Vitor.

Gunnar Morling said...

Your document isn't well-formed (no closing tags for <nfeProc> and <NFe>), I don't think Oracle can handle this.

Anonymous said...

I didn´t send the entire document because it´s so big. This is only one part. Imagine the document is right.

Gunnar Morling said...

Hmmm, hard to say. I'd recommend to simplify the document as well as the XPath expression to get a very simple example working. From there you could start restoring the original state until you find the problem.

Anonymous said...

Ok, I´ll try.

Tks.
Vitor.

puchtec said...

I would like to leave a little note.

Please be aware that the default namespace does only apply to elements but not to attributes !!!

Example:


If you want to use "http://domain.com" as your default namespace, you could simple use:
extractValue(..,'/Rule','xmlns="http://domain.com"')

The result will be okay as the namespace n1 used in the XML matches the default namespace in your extract.

If you want to fetch the value of the attribute "RuleVersion" the following will NOT work:
extractValue(..,'/Rule/@RuleVersion','xmlns="http://domain.com"')

You CANNOT use the default namespace, you have to use:
extractValue(..,'/att:Rule/@att:RuleVersion','xmlns:att="http://domain.com"')

However, to benefit from the default namespace for elements, it is possible to specify a default namespace and a named namespace, e.g. att, both with the same definition.
extractValue(..,'/Rule/@att:RuleVersion','xmlns="http://domain.com" xmlns:att="http://domain.com"')


Have fun working with XML in the Oracle Database !!!

Anonymous said...

Hi Gunnar
Just the exemple I was looking for. Very good help for me :-)
/Torben N

puchtec said...

Hi All,

be aware that extractValue() is deprecated in Oracle 11g Release 2 (11.2.0.1).

See Oracle's documentation.

Leandro said...

Hi Puchtec.

Do you know another way to do it??

I trying with ExtractValue, but without success.

Thanks.

puchtec said...

Hi Leonardo,

even extractValue() is deprecated in Oracle 11g Release 2 (11.2.0.1) you still can use it.

What does not work? Do you have an example?


Cheers

Anonymous said...

Thanks a lot, I was having trouble with this until I read your post; very simple and straight to the point!

Anonymous said...

Here's one I'm having trouble with. What am I doing wrong?

First a sample of what I'm querying against (I've changed node names, but the rest is spot on to what I'm dealing with)...






james
james
35






and my xpath query:


select extractvalue(
xmlfield,
'/RootNode/soapenv:Body/nodeTwoResponse/nodeTwoReturn/fname',
'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"',
) as "XMLOut"

Anonymous said...

oops. Let's try that again.

Here's one I'm having trouble with. What am I doing wrong?

First a sample of what I'm querying against (I've changed node names, but the rest is spot on to what I'm dealing with)...


< RootNode >
< soapenv:Body xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" >
< nodeTwoResponse xmlns="urn:gemini" >
< nodeTwoReturn xmlns="" >
< fname >james< /fname >
< lname >james< /lname >
< age >35< /age >
< /nodeTwoReturn >
< /nodeTwoResponse >
< /soapenv:Body >
< /RootNode >


and my xpath query:


select extractvalue(
xmlfield,
'/RootNode/soapenv:Body/nodeTwoResponse/nodeTwoReturn/fname',
'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"',
) as "XMLOut"