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 |
Posts
28 comments:
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!
Glad to hear that I could help :-) It took me a while to find this out, too.
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.
Your document isn't well-formed (no closing tags for <nfeProc> and <NFe>), I don't think Oracle can handle this.
I didn´t send the entire document because it´s so big. This is only one part. Imagine the document is right.
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.
Ok, I´ll try.
Tks.
Vitor.
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 !!!
Hi Gunnar
Just the exemple I was looking for. Very good help for me :-)
/Torben N
Hi All,
be aware that extractValue() is deprecated in Oracle 11g Release 2 (11.2.0.1).
See Oracle's documentation.
Hi Puchtec.
Do you know another way to do it??
I trying with ExtractValue, but without success.
Thanks.
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
Thanks a lot, I was having trouble with this until I read your post; very simple and straight to the point!
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"
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"
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.
09/15/2014
It really worked for me. Thanks for posting this.
helped solve my problem. thank you for this hint!
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;
Xml File :
101
John,Miller
MCR
100.66
1
BCBS
200.20
2
102
Louis,Phillipe
TUFTS
1010.50
1
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
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
http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2//PurchaseOrderService/createPurchaseOrderResponse
urn:uuid:04b70bff-5a77-4e24-a7a8-e5b217aa4b4a
300000000694092
27
300000000639014
SUCCESS
Thanks a Ton!!!!! ultimate post...
Thank you so much! It really helped.
Regards,
Mihir
Thank you so much for the explanation
Post a Comment