Home » Developer & Programmer » JDeveloper, Java & XML » PL/SQL XML extractvalue
PL/SQL XML extractvalue [message #162246] Thu, 09 March 2006 06:24 Go to next message
ianbrewster
Messages: 8
Registered: March 2006
Location: MLFC, London
Junior Member
Hi,

I am using ORACLE 9.2.0.6.

I have been following examples to extract some data from an XMLTYPE column but am not getting any returned.

The data is stored in a table called temp_xml and the column is called xml:

<TRSValidationReport xmlns="http://www.fsa.gov.uk/XMLSchema/FSATRSValidationRepo
rt-v1-0">
<SubmttedReportDetails>
<FileName>bloggs.xml</FileName>
<ReportType>Markets</ReportType>
<FeedTargetSchemaVersion>1.2</FeedTargetSchemaVersion>
<SubmittingFirm>123456</SubmittingFirm>
<SubmittingDept>ABC123</SubmittingDept>
<ReportCreationDate>2004-04-01</ReportCreationDate>
<ReportIdentifier>ABC/123/22</ReportIdentifier>
</SubmttedReportDetails>
<ValidationResult>
<ValidationStatus>XML Fail</ValidationStatus>
<ErrorDescription>XML Failure error description</ErrorDescription>
</ValidationResult>
</TRSValidationReport>

and I'm trying to extract data (any at the moment) using extractvalue.
Here's one of my attempts:

select extractValue(xml, '/TRSValidationReport/SubmttedReportDetails/FileName')
from temp_xml;

Results:

EXTRACTVALUE(XML,'/TRSVALIDATIONREPORT/SUBMTTEDREPORTDETAILS/FILENAME')
--------------------------------------------------------------------------------


CFDD2>

I have tried many variations on the above select but without success.

Anyone got any ideas on where I'm going wrong.

Thanks in advance.
Ian.

Report message to a moderator

Re: PL/SQL XML extractvalue [message #162595 is a reply to message #162246] Sat, 11 March 2006 13:25 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello.

You've simply forgotten to include the namespace on the extract, i.e.
SQL> select extractValue(x, '/TRSValidationReport/SubmttedReportDetails/FileName',
  2  'xmlns="http://www.fsa.gov.uk/XMLSchema/FSATRSValidationReport-v1-0"')
  3  from temp_xml
  4  /

EXTRACTVALUE(X,'/TRSVALIDATIONREPORT/SUBMTTEDREPORTDETAILS/FILENAME','XMLNS="HTTP://WWW.FSA.GOV.UK/X
----------------------------------------------------------------------------------------------------
bloggs.xml

Rgds

Report message to a moderator

Re: PL/SQL XML extractvalue [message #162697 is a reply to message #162246] Mon, 13 March 2006 03:21 Go to previous messageGo to next message
ianbrewster
Messages: 8
Registered: March 2006
Location: MLFC, London
Junior Member
Hi,

That's great.
I had already managed to work that out but am on the next problem.

I am now trying to extract the repeating data but can't get the syntax for this right. Hopefully it's just a simple syntax issue.
I want to get the information below the TransactionErrorDetails out, of which there can be many.

<?xml version="1.0" encoding="utf-8" ?>
- <TRSValidationReport xmlns="http://www.fsa.gov.uk/XMLSchema/FSATRSValidationReport-v1-0">
- <SubmttedReportDetails>
<FileName>bloggs.xml</FileName>
<ReportType>HSFPP</ReportType>
<FeedTargetSchemaVersion>1.2</FeedTargetSchemaVersion>
<SubmittingFirm>147150</SubmittingFirm>
<SubmittingDept>CFDTech</SubmittingDept>
<ReportCreationDate>2005-12-29</ReportCreationDate>
<ReportIdentifier>000015</ReportIdentifier>
</SubmttedReportDetails>
- <ValidationResult>
<ValidationStatus>Partial Failure</ValidationStatus>
<TotalTransactions>100</TotalTransactions>
<SuccessfulTransactions>98</SuccessfulTransactions>
</ValidationResult>
- <TransactionErrorDetails>
<Position>53</Position>
<FirmFSARef>123456</FirmFSARef>
<TransRef>ABC12312</TransRef>
<Cancellation>false</Cancellation>
- <TxnFailureDetails>
<FailureType>Business</FailureType>
<FailureReason>Business Failure description</FailureReason>
</TxnFailureDetails>
</TransactionErrorDetails>
- <TransactionErrorDetails>
<Position>87</Position>
<FirmFSARef>123456</FirmFSARef>
<TransRef>ABC123/232</TransRef>
<Cancellation>false</Cancellation>
- <TxnFailureDetails>
<FailureType>Schema</FailureType>
<FailureReason>Schema failure description</FailureReason>
</TxnFailureDetails>
</TransactionErrorDetails>
</TRSValidationReport

Many Thanks.

Report message to a moderator

Re: PL/SQL XML extractvalue [message #162817 is a reply to message #162246] Mon, 13 March 2006 14:20 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello.

It's slightly more involved, because you need to use the XMLSEQUENCE function to convert the extracted document into a
series of rows (since there can be more than one), therefore, you would want something like :
SQL> SELECT
  2    EXTRACTVALUE(VALUE(s), '/TransactionErrorDetails/Position', :xmlns) Position,
  3    EXTRACTVALUE(VALUE(s), '/TransactionErrorDetails/FirmFSARef', :xmlns) FirmFSARef,
  4    EXTRACTVALUE(VALUE(s), '/TransactionErrorDetails/TransRef', :xmlns) TransRef
  5  FROM
  6    TABLE(XMLSEQUENCE(
  7      EXTRACT((SELECT x FROM temp_xml), '/TRSValidationReport/TransactionErrorDetails', :xmlns)))
 s
  8  /

POSITION
----------------------------------------------------------------------------------------------------
FIRMFSAREF
----------------------------------------------------------------------------------------------------
TRANSREF
----------------------------------------------------------------------------------------------------
53
123456
ABC12312

87
123456
ABC123/232


Rgds

Report message to a moderator

Re: PL/SQL XML extractvalue [message #162934 is a reply to message #162246] Tue, 14 March 2006 03:58 Go to previous messageGo to next message
ianbrewster
Messages: 8
Registered: March 2006
Location: MLFC, London
Junior Member
Hi,

Thanks for that.
I'd already found a way to get the data out.
Probably not as efficient as yours but we're only talking about a handful of records:

select
TO_NUMBER(NVL(extractValue(tfrx.xml, '/TRSValidationReport/TransactionErrorDetails[1]/Position','xmlns="http://www.fsa.gov.uk/XMLSchema/FSATRSValidationReport-v1-0"'),0))
,NVL(extractValue(tfrx.xml, '/TRSValidationReport/TransactionErrorDetails[1]/FirmFSARef','xmlns="http://www.fsa.gov.uk/XMLSchema/FSATRSValidationReport-v1-0"'),0)
,NVL(extractValue(tfrx.xml, '/TRSValidationReport/TransactionErrorDetails[1]/TransRef','xmlns="http://www.fsa.gov.uk/XMLSchema/FSATRSValidationReport-v1-0"'),0)
,NVL(extractValue(tfrx.xml, '/TRSValidationReport/TransactionErrorDetails[1]/Cancellation','xmlns="http://www.fsa.gov.uk/XMLSchema/FSATRSValidationReport-v1-0"'),0)
,NVL(extractValue(tfrx.xml, '/TRSValidationReport/TransactionErrorDetails[1]/TxnFailureDetails/FailureType','xmlns="http://www.fsa.gov.uk/XMLSchema/FSATRSValidationReport-v1-0"'),0)
,NVL(extractValue(tfrx.xml, '/TRSValidationReport/TransactionErrorDetails[1]/TxnFailureDetails/FailureReason','xmlns="http://www.fsa.gov.uk/XMLSchema/FSATRSValidationReport-v1-0"'),0)
from temp_xml tfrx;

I'm actually running this in a loop, using a counter from 1 to number of records to retrieve and using this in the [1] part.

I have tried your solution but can't seem to get it woking:


xmlns VARCHAR2(100) := 'xmlns="http://www.fsa.gov.uk/XMLSchema/FSATRSValidationReport-v1-0"';



CURSOR get_data_cursor IS
SELECT
EXTRACTVALUE(VALUE(s), '/TransactionErrorDetails/Position', xmlns) Position,
EXTRACTVALUE(VALUE(s), '/TransactionErrorDetails/FirmFSARef', xmlns) FirmFSARef,
EXTRACTVALUE(VALUE(s), '/TransactionErrorDetails/TransRef', xmlns) TransRef
FROM
TABLE(XMLSEQUENCE(EXTRACT((SELECT 'x' FROM trs_feed_Result_xml), '/TRSValidationReport/TransactionErrorDetails', xmlns))) S;


I'm getting the follownig error:


EXTRACT((SELECT 'x' FROM trs_feed_Result_xml), '/TRSValidationReport/Tran
sactionErrorDetails', xmlns))) S;
*
ERROR at line 30:
ORA-06550: line 30, column 8:
PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
ORA-06550: line 24, column 5:
PL/SQL: SQL Statement ignored


I think your solution would be preferable if I can get it woking.

Many Thanks.
Ian.

Report message to a moderator

Re: PL/SQL XML extractvalue [message #163019 is a reply to message #162246] Tue, 14 March 2006 10:58 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello.

You've put quotes around the "x" in the following SELECT :
SELECT 'x' FROM trs_feed_Result_xml

This should be the XML column, not a scalar value, my column
is simply called "x"! Wink

Rgds

Report message to a moderator

Re: PL/SQL XML extractvalue [message #163023 is a reply to message #162246] Tue, 14 March 2006 11:11 Go to previous message
ianbrewster
Messages: 8
Registered: March 2006
Location: MLFC, London
Junior Member
Doh.

You are quite right!

Thanks for your help.

I'll change my code to use the select and run from a cursor to process each set of data in turn.

Much nicer than my current (slow) retrieval method.

Regards.
Ian.

Report message to a moderator

Previous Topic: How to fetch tag data from XML string?
Next Topic: Reading of names of XML files
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Apr 27 23:49:09 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.