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 Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
Re: PL/SQL XML extractvalue [message #162595 is a reply to message #162246] |
Sat, 11 March 2006 13:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: PL/SQL XML extractvalue [message #162697 is a reply to message #162246] |
Mon, 13 March 2006 03:21 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
Re: PL/SQL XML extractvalue [message #162817 is a reply to message #162246] |
Mon, 13 March 2006 14:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: PL/SQL XML extractvalue [message #162934 is a reply to message #162246] |
Tue, 14 March 2006 03:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
|
Re: PL/SQL XML extractvalue [message #163023 is a reply to message #162246] |
Tue, 14 March 2006 11:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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.
|
|
|
Goto Forum:
Current Time: Sat Sep 07 21:01:20 CDT 2024
|