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

28 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!

Unknown 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.

Unknown 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.

Unknown 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"

Anonymous said...

Your example helped me a lot. Thank you! I'm wondering if it is possible to put the ExtractValue portion within your WHERE clause to filter out null values. I tried doing it but for some reason it is erroring out with a ORA-06502 error. I also tried using the WITH clause and that also didn't work. Any help would be appreciated.

Anonymous said...

09/15/2014

It really worked for me. Thanks for posting this.

Anonymous said...

helped solve my problem. thank you for this hint!

Sunil said...

Hi,

Hope you will solve my problem.
I have the following xml file and xml query. My query returns null values. Can you help me in getting the error resolved.

My XML file: textfile.xml



101
John,Miller


MCR
100.66
1


BCBS
200.20
2




102
Louis,Phillipe


TUFTS
1010.50
1





I have written the following query which gives me two empty rows with out any data.

WITH g AS (SELECT xmltype(bfilename('SAMP_XML_DIR','textfile.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol1 FROM dual)
SELECT
extractValue(value(x),'ATB/CLAIMDATA/Claimno') claimno
,extractValue(value(x),'ATB/CLAIMDATA/patname') patname
,extractValue(value(x),'ATB/CLAIMDATA/payors/payordata/inscode[1]') ins1code
,extractValue(value(x),'ATB/CLAIMDATA/payors/payordata/insbal[1]') ins1bal
,extractValue(value(x),'ATB/CLAIMDATA/payors/payordata/inspriority[1]') ins1priority
,extractValue(value(x),'ATB/CLAIMDATA/payors/payordata/inscode[2]') ins2code
,extractValue(value(x),'ATB/CLAIMDATA/payors/payordata/insbal[2]') ins2bal
,extractValue(value(x),'ATB/CLAIMDATA/payors/payordata/inspriority[2]') ins2priority
FROM g,TABLE(XMLSequence(extract(g.xmlcol1,'ATB/CLAIMDATA'))) x;

Anonymous said...

Xml File :



101
John,Miller


MCR
100.66
1


BCBS
200.20
2




102
Louis,Phillipe


TUFTS
1010.50
1




Anonymous said...

Awesome - Was at wits end until I found this page. Excellent example, thanks.
Now all I need to do is make the data available for Cognos!

JP

Murali said...

HI Gunnar,

I tried same way that explained but still could not able get sucess, what wrong in my query
iam not getting value output

CREATE TABLE REQUESTS (
ID NUMBER(10,0) PRIMARY KEY,
REQUEST XMLTYPE,
RESPONSE XMLTYPE
);



INSERT INTO REQUESTS (ID, REQUEST, RESPONSE)
VALUES (
2,
'

http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2//PurchaseOrderService/createPurchaseOrderResponse
urn:uuid:04b70bff-5a77-4e24-a7a8-e5b217aa4b4a




300000000694092
27
300000000639014

SUCCESS



', null);




SELECT
req.id,
extractValue(
req.request,
'/env:Envelope/env:Body/ns0:createPurchaseOrderResponse/ns1:result/ns0:OrderNumber',
'xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns0="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/types/" xmlns:ns1="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/types/" xmlns:ns0="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ') Title
FROM
requests req
WHERE
id = 2

Anonymous said...



http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2//PurchaseOrderService/createPurchaseOrderResponse
urn:uuid:04b70bff-5a77-4e24-a7a8-e5b217aa4b4a




300000000694092
27
300000000639014

SUCCESS



Anonymous said...

Thanks a Ton!!!!! ultimate post...

Anonymous said...

Thank you so much! It really helped.

Regards,
Mihir

Anonymous said...

Thank you so much for the explanation

mesgra said...
This comment has been removed by the author.
mesgra said...
This comment has been removed by the author.