JDeveloper, Java & XML :: Extract Data From XML

Dec 20, 2010

I want to retrieve values from a XML data stored on CLOB data type and store it in different table.

Retrieving data from Soap Envelope and Soap Body?

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope>
<soap:Header>
<company>US</company>
</soap:Header>
<soap:Body>
Data
</soap:Body>
<Account>ABCD1234</Account>
</soap:Envelope>

View 8 Replies


ADVERTISEMENT

JDeveloper, Java & XML :: How To Extract XML Data Having Namespace

Mar 18, 2013

Below is the XML i have :

<?xml version="1.0" encoding="UTF-8" ?>
- <Transaction xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.test.com/Support/Services/test1/2012" xsi:schemaLocation="http://schemas.test.com/Support/Services/test1/2012 Support.test1.v1.xsd">
- <Request>
<Memid>10</Memid>
<Actid>32</Actid>
[code].......

I want to extract the element values :

The below code will be useful when i have no namespace , what must be done in order to work with namespace and extract element value

v_string_xml :=
' //Request[' || TO_CHAR (counter_xml) || ']/Memid/text()';
v_ssp_table (v_ssp_table.COUNT).memid :=
p_xml_in.EXTRACT (v_string_xml).getnumberval ();
v_string_xml :=
[code].......

View 2 Replies View Related

JDeveloper, Java & XML :: How To Extract Data Using XSD File

Feb 9, 2012

How to extract the data from XML using the xsd file. attached files.

Explanation: first check the EmailMessage tage from order_conf.xml compared with Email.xml(<xsd:element name="EmailMessage">) if exists then go to next node.
EmailMessage(exists tag in order xml file)
->next <ns1:emailNotificationype> this tag should be follow under the EmailMessage tag(<xsd:element ref="emailNotificationype">) in Email.xml
->next <ns1:orderNotification> -> check this tag in <xsd:element name="orderNotification"> in Email.xml.
-> next <ns1:templateFormatInfo> -> it should follow under <xsd:element name="orderNotification"> in Email.xml.
-> next <ns1:templateFormatInfo> -> it should follow these tages <xsd:element name="templateFormatInfo"> <xsd:element ref="templatecode"/>
<xsd:element ref="templateversion"/>

i have to extract the value in between tags.

templatecode -> ORDCONF
templateversion ->1.0

Finally i have to load the data into table.

View 17 Replies View Related

JDeveloper, Java & XML :: Extract Data From Clob Field Using Regular Expression

Oct 6, 2011

I want to extract the data from the Clob field. I have the following table,create table test123(col1 char(24), col2 clob); And following data,

Insert into test123 (col1,col2) values ('ABCDE','<?xml version="1.0" encoding="UTF-8"?>
<Attributes>
<Attribute DataType="Text-40" DisplayName="DropDirectory"
IsNotDeletable="Y" Modifiable="Y" Name="DropDirectory" Value="${Dir1}"/>
<Attribute DataType="Text-40" DisplayName="PrinterAlias"
IsNotDeletable="Y" Modifiable="Y" Name="PrinterAlias" Value="\Printer3Printer4"/>
<Attribute DataType="Text-40" DisplayName="PrintServerHostName"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerHostName" Value=""/>
<Attribute DataType="Count" DisplayName="PrintServerPort"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerPort" Value="2723"/>
</Attributes>');
[code]....

The data will be available like this only, The same XML tag is used everywhere.

View 1 Replies View Related

JDeveloper, Java & XML :: How To Extract All Namespaces

Oct 24, 2012

I have many XML files with multiple namespaces. Is where a way to extract all namespaces? This is one of my XMLs

<?xml version="1.0" encoding="UTF-8"?>
<form:Documents xmlns:form="test1" xmlns:addData="test2">
<Paklausimas:_Uzsienio-biuro-paklausimas addData:DocumentState="Correct" addData:ImagePath="C:UsersPublicData.pdf" xmlns:Paklausimas="test3">
<_Document_Section_1>
<_dokumento-rusis>Uzsienio-biuro-paklausimas</_dokumento-rusis>

[code].....

View 6 Replies View Related

JDeveloper, Java & XML :: Extract Name And ID From File

Jul 23, 2012

Trying to extract name and ID from the below XML File using XMLTable but it is returning no data

XML File is - please check attachment

Query I am using:

select p."uid",p."uname" from warehouses w,
xmltable('/ArrayOfAnyType/anyType'
passing w.warehouse_spec
columns "uname" varchar2(100) PATH '/name',"uid" varchar2(100) path '/id'
)p where warehouse_id = 6;

View 24 Replies View Related

JDeveloper, Java & XML :: Duplicate Records After XML Extract

Mar 5, 2011

I've got a table with XML code (CLOB column) and I need to read and write every of parameter(blue marked below) and value (red marked below) record from that.

For example:

create table tmp_mape
(msisdn varchar2(100),
xml_params CLOB )
insert into tmp_mape values
('423903200200',
'<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>410</value></parameter><parameter name="isDropped"><value>true</value></parameter></parameters>' );
[code]........

I tried to read this every values of xml code by select below:

select
extractValue(value(x),'/parameter/@name', 'xmlns="http://datalan.sk/webreporting/params/v1_0"') b,
extractValue(value(x),'/parameter/value', 'xmlns="http://datalan.sk/webreporting/params/v1_0"') a,
a.*
[code]......

This select returns a duplicate records because of more the one parameter and value records from XML column.

View 30 Replies View Related

JDeveloper, Java & XML :: Extract Value Of InternalID Into A New View

Jun 19, 2012

I have a xml clob field in a table and I want to extract the value of the <SampleInternalId> into a new view and have each value for the <SampleInternalId> in its own row. I am using the following sql statement to extract the data

SELECT m.messagecontent.extract('//SampleData/Samples/Sample/SampleInternalId/text()') .getStringval() as "InternalID",
m.messagecontent.extract('//SampleData/SampleGroupId/text()') .getStringval() as "GroupID",
'SMPLGRID:' ||
m.messagecontent.extract('//SampleData/SampleGroupId/text()') .getStringval() as "Printdata",
m.messageidexternal,
m.messageidmes
FROM pasx.ifarchivedmessage m
WHERE m.messagetype = 'SLDT';

The issue I am having is the statement above is putting all the values for <SampleInternalId> into one field, e.g 232297232298232299. Is it possible to have 3 rows the Internal ID split out.

CLOB Field

<?xml version="1.0" encoding="UTF-8"?>
<SampleData>
<MesRequestId>20120209122106507</MesRequestId>
<Site>Test</Site>
<SampleGroupId>Test-BULK-123579-BOBK8</SampleGroupId>

[Code]....

View 5 Replies View Related

JDeveloper, Java & XML :: Data To HTML Table With Tag

Jul 10, 2012

I've generated XML from oracle:

select dbms_xmlquery.getxml('select * from tbl_nm') from dual
<?xml version="1.0" encoding="ISO-8859-1"?>
<ROWSET>
<ROW num="1">
<NAME>admin</NAME>
<PASSWORD>a</PASSWORD>

[code]....

i tried to get this xml data in html table but the table was not displayed. When I changed the <ROW> tag into any other tag say <CD> the table was displayed.

View 10 Replies View Related

JDeveloper, Java & XML :: Parsing Containing Array Of Data?

Jan 12, 2011

I have to parse an XML and store the data in a table.

My XML :-
WITH data AS (
SELECT XMLTYPE(
'<?xml version=''1.0'' encoding=''UTF-8''?>

[Code].....

The output is :-

CONVERSATIONID COMMAND VALUE
a949-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V1

There will be cases when I will get multiple values for the columns conversationid, command, value.

<?xml version=''1.0'' encoding=''UTF-8''?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>

[Code].....

In that case the output should be as :-

CONVERSATIONID COMMAND VALUE
a949-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V1

iiii-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V2

Not sure how to go about XML parsing when I have multiple values.

View 4 Replies View Related

JDeveloper, Java & XML :: Send Data From Client To Server?

Jan 6, 2011

I want to send data from my window based standalone application to my web based application.

View 1 Replies View Related

JDeveloper, Java & XML :: Query A Column Containing XML Formatted Data

Jun 26, 2011

how to use dbms_xmlgen to take the results of a query and convert it into a XML formatted data. But if a column (clob) contains data in an XML format, is there a way to query that column and easily parse the data? I know that I could write loops that do string searches for each of the XML tags and nested tags.

View 9 Replies View Related

JDeveloper, Java & XML :: Looping And Querying Data From XML Clob

Oct 14, 2013

Our company database is Oracle based and we use SQL Developer to pull out needed data.Using a snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by an example snippet of the xml clob that I am pulling from. The reason for the "query within a query" is because the base query could return multiple entries and I only want the one with the most recent date.

select * from
(Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date,
to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
from ws_transactions
Where Wtr_Service_Tag In ('20458749610')
And Wtr_Req_Xml Like ('%CSM%')
Order By Wtr_Receive_Date Desc)
where rownum = 1;
[code]....

This query is only able to pull the first value in the list.How can I edit this query to pull all of the list items when there are more than 1 (preferably concatenated into one column in the query results)? I have another field, in a separate table, that I can pull from to get the number of list items.

This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.

View 28 Replies View Related

JDeveloper, Java & XML :: Select Data From XML Type Column

Jan 24, 2013

I have a table Table_xml_tmp which has a column MESSAGECONTENTS as XMLType data which contains data as follows


"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xclimSchemaMain xmlns="http://clones.telcordia.com">
<response>
<clHeader>
<msgName>queryPlace</msgName>
[code]......

I need to get all rows where node <operationStatus> value as SUCCESS. I am running following query but it is returning null value insted of data of columns

SELECT a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()
FROM Table_xml_tmp a
WHERE a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()= 'SUCCESS'

View 2 Replies View Related

JDeveloper, Java & XML :: Convert Column Data To XML Format

Jul 9, 2012

CREATE TABLE EMP(NAME VARCHAR2(10 BYTE))

INSERT INTO EMP VALUES ('C');
INSERT INTO EMP VALUES ('A');
INSERT INTO EMP VALUES ('T');

SELECT xmlelement("NAME",NAME) FROM EMP;

I am trying to convert column data to xml format, but I get this error message:

Quote:The query fails because all columns types are currently not supported. I am using:

Quote:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
SQLTools 1.5.0 Beta build 9 as EDITOR

View 7 Replies View Related

JDeveloper, Java & XML :: Two Separate Database - Transfer Data

Feb 15, 2011

I have two separate databases, I want to send XML file contains a query from one to the other , that read query and return results to a first via XML file.

View 10 Replies View Related

JDeveloper, Java & XML :: Process XML Data Passed In Parameter

Oct 7, 2011

I have a requirement to extract data from an XML formatted string that is passed as an IN parameter to a database stored procedure. I do not have a table to store the XML, nor an XML schema, nor a namespace, just the XML string. While new to XML, I do understand the basics. Using XMLTable I am able to parse out the simplest of XML data. My problem is this; attempting to extract data from nested XML nodes results in the error

'ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence'.

Testing a solution example I found on this site by Barbara Boehmer, I still get the ORA-19279 error. I suspect it may be because I have version 10g while she has version 11g. Her solutions is from #msg_514154. Here is that code that gives me the error:

SCOTT@orcl_11gR2> SELECT x."deptno"
2 FROM (SELECT '<departments><deptno>1</deptno><deptno>2</deptno></departments>' col FROM dual) xml_data,
3 XMLTABLE
4 ('/departments/deptno'
5 PASSING XMLTYPE (xml_data.col)
6 COLUMNS
7 "deptno" number path '/deptno') x
8 /

deptno
----------
1
2

2 rows selected.

As I said, this example generates the ORA-19279 error. Need processing XML data from an IN parameter?

View 5 Replies View Related

JDeveloper, Java & XML :: Parsing With Special Character As Part Of Data

Sep 10, 2013

I'm using following select to parse XML as table ( Oracle 11.2.0.3.0 EE Linux and Windows)

SELECT a.id, a.name, a.description
FROM ( XMLTABLE('//ROW'
PASSING (SELECT xmltype('<ROWSET>
<ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
[code]........

However, when DESCRIPTION contains "<" as part of it - I'm receiving an error:

SELECT a.id, a.name, a.description
FROM ( XMLTABLE('//ROW'
PASSING (SELECT xmltype('<ROWSET>
<ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
[code]........

Quote:ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00231: Invalid character 61 ("=") found in Name or Nmtoken

I'm receiving a XML from some external source and can not change it.

View 8 Replies View Related

JDeveloper, Java & XML :: Reading Data From Database Table That Contains Control Characters

May 1, 2011

I have the following bit of code that reads data from the an Oracle table (Note: This is running on Jdk 1.4.2)

ResultSet message = messageStatement.executeQuery(getMsgSql);
String messageData = message.getString("MESSAGE_DATA");

The data in the MESSAGE_DATA column contains text but also control characters that separate data elements in the message (i.e (char)31, (char)29) and (char)28) . What i am finding is that for some reason message.getString() is sometimes truncating the message. I can read the majority of messages but some of them are truncated. Am i supposed to be reading the data using a different method? If so how?

I have tried to use sqlplus to look at the data in the database and it is all there it is just truncated by the message.getString() method. I saw this when i tried to output the result i.e. System.out.println(message.getString()).

I ran an sql query using the length() function in Oracle and length("MESSAGE_DATA") returns 2032 whereas in java message.getString(1).length() returns 2000.

View 5 Replies View Related

JDeveloper, Java & XML :: Calling Function With (array Of) Complex Data Type

Oct 20, 2011

I'm trying to call a Java function from within a PL/SQL aggregation function with a (array of) complex data type.

a) basic definitions (work and are ok)

CREATE OR REPLACE TYPE eventtype AS OBJECT
(
relevance NUMBER,
bid1 NUMBER,tid1 NUMBER,prob1 NUMBER,
bid2 NUMBER,tid2 NUMBER,prob2 NUMBER,
bid3 NUMBER,tid3 NUMBER,prob3 NUMBER,
bid4 NUMBER,tid4 NUMBER,prob4 NUMBER,
bid5 NUMBER,tid5 NUMBER,prob5 NUMBER,
bid6 NUMBER,tid6 NUMBER,prob6 NUMBER
);
CREATE OR REPLACE TYPE eventtuple IS TABLE OF eventtype;
/

b) The following call works (but is not exactly that what I want):

public class pcompDP {
public static NUMBER probComputation(oracle.sql.STRUCT pin) throws java.sql.SQLException {
...
}
}
create or replace FUNCTION pcompDPFunc(cin IN eventtype) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC AS LANGUAGE JAVA NAME 'pcompDP.probComputation(oracle.sql.STRUCT) return BigDecimal';
/
SELECT pcompDPFunc(eventtype(5, 6, 0.3, 10, 20, 0.45, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)) from dual;

c) The following call doesn't work (but should have the intended semantics):

public class pcompDP {
public static NUMBER probComputation(oracle.sql.STRUCT[] pin) throws java.sql.SQLException {
...
}
}
create or replace FUNCTION pcompDPFunc(cin IN eventtype) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC AS LANGUAGE JAVA NAME 'pcompDP.probComputation(oracle.sql.STRUCT[]) return BigDecimal';
/
SELECT pcompDPFunc(eventtuple(eventtype(2, 3, 0.234, 1, 1, 0.123, 0, 0, 0, 0, 0, 0, 0, 0, 0), eventtype(5, 6, 0.3, 10, 20, 0.45, 0, 0, 0, 0, 0, 0, 0, 0, 0))) from dual;

d) What's the error? Do I have to use JPublisher or is it possible without it? I guess an array should be possible without it.

The type of the parameter seems to be wrong. Question is whether in PL/SQL, Java or SQL.

View 1 Replies View Related

JDeveloper, Java & XML :: Using Java.lang.Math In Oracle / ORA-31011 / Parsing Failed

Apr 26, 2013

I am trying to execute an xslt (for XML transformation) using xmltype.transform function.

I can't paste XSLT code here, but below is similar what I am doing.

[URL]

I have used a reference of random() method of java.lang.Math in xslt file, and it's giving me below error.

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00607: Invalid reference: 'random'.

I doubt, it's because of "Math" class, which is not present in Oracle, how to load standard java classes.

View 1 Replies View Related

JDeveloper, Java & XML :: Java Stored Procedure Invoking Windows Run Time Process

May 22, 2012

I am trying to invoke program from my local Windows machine (exactly java executor, which is in my environment on c:javajdkinjava) using database package with Java stored procedure.

Everything is fired by SQLPLUS script, this script invokes package with Java stored procedure (jar loaded into Oracle) and that procedure is trying to invoke runtime process (syntax:

c:javajdkinjava -cp

and here ara my classes and main jar) using my local java executor. I added all permissions using dbms_java.grant_permisions (execution forced me to give permissions java.io.FilePermission to <<ALL_FILES>> for execute), moreover I added to my database user JAVASYSPRIV and JAVAUSERPRIV role. Unfortunately, beside this I am still getting error:

C:javajdkinjava not found (Note that lookup with PATH isn't done due to the oracle executable being setuid.)

View 8 Replies View Related

JDeveloper, Java & XML :: Java.sql.SQLException - Execute Batch - Exception Equal To 1?

Jul 14, 2011

I was getting java.sql.SQLException: executeBatch, Exception = 1when i tried to use executeBatch().could not understand why i am getting it.

View 1 Replies View Related

JDeveloper, Java & XML :: Calling Java Stored Procedure From Oracle

Jun 20, 2011

I am writing a java stored procedure in my package. In the java procedure I want to issue a query to be fired on the user table from which I am calling the java procedure.

Some thing like following:

I am logged in to USER1 and my package is inside USER1. From this package I am calling the Java Stored Procedure. In java procedure I want to make a jdbc connection to USER2. The details of USER2(password, hostname,portno) are stored in a table called 'connection_details' and this table is in the USER1.

My problem is how can I issue the

SELECT details FROM CONNECTION_DETAILS for USER2

so that i can get the details..?

Do i need to first make jdbc connection to USER1 and then execute the above query for which i need similar details of USER1. How can i obtain the details of USER1 here.

I am thinking if I am already in USER1 then do I need to make the jdbc connection to USER1 and then execute the query?

View 1 Replies View Related

JDeveloper, Java & XML :: Create Or Replace And Compile Java - Getting Ora 29531?

Dec 21, 2011

I've this java source create or replace and compile java source named "Decodificador" as
public class Decodificador {
public static String decodifica(String codigo)
return codigo;

and this function

create or replace function F_Decodificador(codigo varchar2) RETURN VARCHAR2 IS
LANGUAGE java NAME 'Decodificador.decodifica(String) return String';

when I execute the function the result is:

ORA-29531: no method decodifica in class Decodificador

View 8 Replies View Related

JDeveloper, Java & XML :: Best Driver For Java To Oracle Database Connection

Jul 30, 2012

Which is the best driver for java to Oracle database connection ?

View 1 Replies View Related

JDeveloper, Java & XML :: Call DB Function From Java Hibernate

Feb 10, 2011

I need to call DB function through Java Hibernate.

My Sample XML File looks like.
---------------------------------------------------------------
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<sql-query name="getchequeno" callable="true">
?:=call cz_nab_mw_fn_gen_ol_chq_no()
</sql-query>
</hibernate-mapping>
---------------------------------------------------------------
I am getting below error while reading the file

"java.lang.RuntimeException: org.hibernate.HibernateException: Errors in named queries: getchequeno"

View 1 Replies View Related

JDeveloper, Java & XML :: How To Pass Any Array From Java To Oracle

Feb 4, 2005

Here is a problem. I have to pass an array e.g ('Account','Marketing',Computer' ) from java to Oracle 9i.

Would I be able to do in Oracle, if yes then how ?

View 11 Replies View Related

JDeveloper, Java & XML :: Native Java In Oracle Versus PL/SQL?

Aug 26, 2011

we can you java code natively with Oracle. But I didn't get how to decide when to use native java in oracle and when to use pl/sql.

View 5 Replies View Related

JDeveloper, Java & XML :: Using PL/SQL Function To Execute Java Class?

May 20, 2013

i`m trying to use pl/sql function to execute java class. I created 2 tables with around 100 values and java class with simple functionality. I need to create function to loop through my table1 and get with each iteration one value from table and pass that value as parameter to java class.

table1 is something like this:

ID NAME
1 name1
2 name2
3 name3
. .
. .
100 name100

table2 is empty with same columns as table1 ( table2 is for data obtained from java returns ) I created in sql+ java class.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "myClass" AS
import java.io.*;
import java.util.*
public class myClass {
public static String fun(String strName)

[code]....

Then i wanna make a pl/sql function for:

1)For i = 1 Obtain first value from table1 (column name) and pass it throught java class then return value and save it in table2 with the same id as id from table1

2) change i=1 to i=2 and do that same

3) end if i=101

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved