XML DB :: Un-nest XML With Single Child Element

May 20, 2013

I'm trying to write a generic function for un-nesting child nodes where nesting is deemed unnecessary. Typically when the element only has one child element.

For example, given the following source....
<ROOT>
  <ITEM>
    <DESCRIPTION>TEST1</DESCRIPTION>
  </ITEM>
  <ITEM>
    <DESCRIPTION>TEST2</DESCRIPTION>
  </ITEM>
</ROOT>I actually want.....
<ROOT>
  <DESCRIPTION>TEST1</DESCRIPTION>
  <DESCRIPTION>TEST2</DESCRIPTION>
</ROOT>

because we think ITEM isn't really required before we deliver XML data.I've been trying to achieve this with a function, where I pass in the XPath to the node I want flattening, something like

function UnNest(pXMLData XMLType, pXPath varchar2) return XMLType...Called like the following...
declare
begin
  vXMLData := UnNest(SomeXMLData, '/ROOT/ITEM')
end;I tried using XQuery Update (11gR2), as follows,
[code]....

My destination platform is 11gR2 64 bit but going forward I may need a solution for 10gR2 too. Perhaps there is another way without using XQuery Update?

View 2 Replies


ADVERTISEMENT

CLOB In Xml Element

Oct 18, 2011

I have to generate a big xml. Each part of the xml needs data from different tables. So I have created two clob to hold the portion of the big xml. How can i have the concatenated clob values as an argument for xml element.

View 1 Replies View Related

SQL & PL/SQL :: How To Add XML Element In Oracle 10gr1 Without Using XDB

Jun 24, 2013

how to add an xml element in oracle 10gr1 without using Oracle XDB?

View 1 Replies View Related

Reports & Discoverer :: Use Of Xml Element In 6i?

Nov 6, 2012

i want to display values of same column with comma seperation like this

SELECT RTRIM(XMLAGG(XMLELEMENT(e,DESCR_INDI || ',')).EXTRACT('//text()'),',') INTO DESCR FROM
STUDENT_SKILL_PER WHERE GROUP_NAME=:GROUP_NAME
AND SKILL_NAME=:SKILL_NAME AND SUB_SKILL_NAME=:SUB_SKILL_NAME
AND TERM=:TERM AND STUDENT_CODE=:STUDENT_CODE AND YEAR_CD=:YEAR_CD;

but the error shown to me that xmlelement must be declared...but when i deploy same query at sql editor it gives me correct result..how i can use it on oracle report 6i... i am using oracle 9i as database.

View 1 Replies View Related

SQL & PL/SQL :: 70% / 30% From High And Low Priority Element

Oct 27, 2011

I have a priority column(possible values are 1 or 0) in a table where

i need to get 70% of high(1) and 30% of low (0) and max i can fetch for select is 50 records.

Eg1: Total if i have 60 in which 20 high and 40 low then 70% of 20 = 14 and remaining should be taken from Low i.e. 36 from low. so total will be 50 transactions.

Eg2: Total if i have 60 in which 40 high and 20 low then 70% of 40 28 + remaining should be taken from Low i.e. 22 from Low.

Eg 3: If i don't have any high then total should be picked from low vise versa.

I have below query but it is having problem when there is no low priority.

SELECT ID,PRI FROM temp tbl WHERE pri = '1' AND ROWNUM < ((70/100)*50)+1
UNION ALL SELECT * FROM temp WHERE pri = '0'
AND ROWNUM < 50-(SELECT COUNT(*) FROM temp WHERE pri = '1' AND ROWNUM < ((70/100)*50)+1)

View 3 Replies View Related

Forms :: Empty Element In Item_list

Jan 13, 2013

I used a dynamic item_list. This list should contain only the result of the query but I found an empty element in the list. So, how to eliminate this element empty list?

see the picture:

[URL]........

View 4 Replies View Related

SQL & PL/SQL :: ORA-22160 / Element At Index (1) Does Not Exist

Mar 27, 2007

i am trying to update using bulk processing.am facing this ORA-22160: element at index [1] does not exist.

ORA-22160: element at index [string] does not exist

Cause: Collection element at the given index does not exist.

Action: Specify the index of an element which exists.

how to avoid this.

1 create or replace procedure dept_new
2 is
3 type dept_type is table of dept.deptno%type;
4 type tab_type is table of dept%rowtype;
5 --variable declaration
6 dept_t dept_type;
7 tab_t tab_type;

[code]....

my dept and dept1 tables are both of same structure.

View 7 Replies View Related

PL/SQL :: Referencing Element In Nested Collection

Jun 27, 2013

I have a table called dept table with nested collection as below. Nested Table Collection:

create or replace
type courselist as table of varchar2(64) Table Using Nested Table Collection: 
CREATE TABLE "FCSDWH_STG"."DEPT"
   ( "NAME" VARCHAR2(20 BYTE),
  "DIRECTOR" VARCHAR2(20 BYTE),
  "OFFICE" VARCHAR2(20 BYTE),
  "COURSES" "FCSDWH_STG"."COURSELIST"
   )  Content Of Table:NameDirectorofficecoursesAccountingJames CharlesUNOFCSDWH_STG.COURSELIST('natural science','chemistry','Computer Science','Computer Science') 

I am trying to select and print an element from nested table collection using below plsql block.

MY_COURSE VARCHAR2(64 CHAR);
BEGIN
SELECT courses INTO MY_COURSE FROM TABLE(SELECT COURSES FROM DEPT) where courses='chemistry';
DBMS_OUTPUT.PUT_LINE(MY_COURSE);
END; Error Message:
PL/SQL: ORA-00904: "COURSES": invalid identifier

View 3 Replies View Related

JDeveloper, Java & XML :: Procedure For Reading Xml Element Value

May 25, 2011

In my pl/sql procedure I'm calling a webservice and it is returning me a varchar2 which contains xml tags.I want to pull values for each element. any one can provide me procedure/function to pull data from it.Below is the output from webservice.

<ResponseEnvelope xmlns:xsd="URL....xmlns="URL....> <ResponseHeader xmlns="URL......
[code]....

ResponseId, MessageVersion etc are element name. the value between element names are to be retrived.

View 13 Replies View Related

SQL & PL/SQL :: Check If Element Present In Defined Column Of Cursor?

Apr 29, 2010

i want to produce a function who return XmlType element if she checks that a parameter (that i passed to here is contained in a row in a defined table)

for example

create table sec_form(
form_code VARCHAR2(8)
, sec_code VARCHAR2(5)
) ;
insert into sec_form values ('CSA','section1');
insert into sec_form values ('CSA','section2');
insert into sec_form values ('CSA','section3');

then i got my function

create or replace function checkIfSectionEnabled (elem XmlType,name_section varchar2)
return XmlType
is v XmlType;
CURSOR myCursor IS select * from sec_form where form_code = 'CSA';
begin
open myCursor;
loop

[code]....

now i'm gonna call the function (elem XmlType,name_section varchar2) and pass here "section1" for example ; and she must return me my xmltype element if she find "section1" finds section1 present;

when i try t compile i got:

Error(18,13): PL/SQL: Statement ignored
Error(18,26): PLS-00225: subprogram or cursor 'MYCURSOR' reference is out of scope

View 3 Replies View Related

JDeveloper, Java & XML :: Updating Element Text In Document?

Nov 15, 2012

I have table called shipper with document_xml as CLOB column

The sample content of document_xml is below

<placemark id = "5467">
<name> 415-CI-78492</name>
<description><![CDATA[<//!!!!//15249599]]></description>
<ICON><href>http://localhost:63077/Icons/Markers/pushpin.png</href></Icon>

and there are other attributes continued

Objective: I want to replace "href" element in each of the record with "[URL] ......" value.

After I update the row the data should look as follows

<placemark id = "5467">
<name> 415-CI-78492</name>
<description><![CDATA[<//!!!!//15249599]]></description>
<ICON><href>http://localhost:63077/Icons/Markers/ship.png</href></Icon>.

I have to do this for bunch of records in a table(table_name = shipper).

Is there an oracle function to replace/update the href element in a CLOB column?

View 4 Replies View Related

XML DB :: Insert Element Into Data Stored In CLOB Column?

Dec 14, 2012

My ORACLE DB version is:

('Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production');
('PL/SQL Release 11.2.0.2.0 - Production');
('CORE 11.2.0.2.0 Production');
('TNS for Linux: Version 11.2.0.2.0 - Production');
('NLSRTL Version 11.2.0.2.0 - Production');

I have this XML data stored in a CLOB column:

<Activity>
<Changes>     
</Changes>
<Inserts>     
</Inserts>
<Definition>     

[code]....

I need creating an update that will insert another <Activity>SomeGUID</Activity> into the <Spawned> parent.

View 3 Replies View Related

Application Express :: Button With Pre-element Text Is Given A Container Div?

Aug 8, 2013

Just installed 4.2.2.00.09 and imported a workspace and an application. Worked fine.I noted a page display difference when compared to the exported application (4.1.0.00.32). I have (so far) found this:If I have a button with Pre Element Text set to <div class="dummy"> and Post Element Text set to </div>, Apex generates an extra div around it all.If my button was named P1_SUBMIT I end up with 

<div id="P1_SUBMIT_CONTAINER">
<div class="dummy"> <input type="button" value="Search" id="P1_SUBMIT" /></div>
</div> 

Is it possible for me to turn this extra div off?

View 0 Replies View Related

JDeveloper, Java & XML :: Convert XML Message To CSV And Assign It To (anyType) Element

Mar 28, 2012

I have a requirement that is to convert XML message to CSV format and assign this to a "anyType" element. How would I convert to CSV?

View 1 Replies View Related

XML DB :: Add Attribute Modification_date For Root Element Process For Field Xml_data?

Mar 6, 2013

I'm studying the guide Oracle XML DB Best practices and it is ok to add, modify or delete nodes. Also to change the value of an attribute, but I'm looking for adding an attribute.What is exactly the statement, for example to add the attribute modification_date for the root element process for a field xml_data ?

View 1 Replies View Related

Application Express :: How To Get Unique Element IDs In Manual Tabular Form

Oct 24, 2012

I'm trying to get a unique sequential element id for every field in a manual tabular form. Here is the code to generate the form:

SELECT apex_item.hidden(1, lpad(rownum,4,0)) row_num,
apex_item.hidden(2, skillset_demand_id) sdi,    
apex_item.text(3, domain, 20, 20, null, 'f03_#row_num#') Domain,
apex_item.text(4, target, 20, 20) Specialization,
apex_item.text(5, skill, 20, 20) Skill
FROM RI_SKILLSET_DEMAND
WHERE WORK_ID = :P511_WORK_IDAccording to the APEX API manual I should be able to set the attribute id with p_item_id*, which in the example of a text item, is the sixth parameter. In line 3 of the code above, I set the p_item_id to 'f03_#row_num#' after having created row_num in line 1.

The HTML I get is:
<input id="f03_#row_num#" type="text" value="xxxxxxxxx" maxlength="20" size="20" name="f03">
...
<input id="f03_#row_num#" type="text" value="xxxxxxxxx" maxlength="20" size="20" name="f03">What I want is:
<input id="f03_0001" type="text" value="xxxxxxxxx" maxlength="20" size="20" name="f03">
...
<input id="f03_0002" type="text" value="xxxxxxxxx" maxlength="20" size="20" name="f03">
...
etc.

I've tried a lot of things but I can't seem to get parameter substitution inserted into the id attribute so I can get each element uniquely with javascript later.

View 1 Replies View Related

JDeveloper, Java & XML :: ORA-30951 / Element Or Attribute At Xpath Exceeds Maximum Length

Apr 29, 2013

I am getting the error when trying to run this test block, this is code is to simulate the production error we are getting...

DECLARE
g_xdoc XMLTYPE;
g_trans_type CHAR (20) := NULL;
BEGIN
SELECT xmltype(c) -- column is a clob column
INTO g_xdoc
FROM test_gil

[code]....

View 12 Replies View Related

Parent Child Relationship

May 13, 2011

Just wanted to pose a question Can a (Unique and Not nullable)Alternate key of the parent table be part of child Table in an Identifying relationship i.e part of primary key of a child table????

Is there a rule that a primary keys of a parent table can be used to build a identifying relationship with its child?

View 1 Replies View Related

SQL & PL/SQL :: Getting Parent And Leaf Child Along With ID

Jun 14, 2012

I've the following data and i need the parent and leaf most child,including their id's

Create table par_chld (id,p_id,c_id) as
(select 1,900,501 from dual union all
select 2,900,502 from dual union all
select 3,900,503 from dual union all
select 4,100,900 from dual union all
select 5,200,900 from dual union all
select 6,300,400 from dual union all
select 7,101,500 from dual union all
select 8,102,500 from dual union all
select 9,103,500 from dual union all
select 10,201,600 from dual union all
select 11,201,601 from dual union all
select 12,201,602 from dual )

In the above data
p_id =100 has c_id 900 , and this 900 acts as a parent which has child 501 , 502 ,503

And the output should be in the following format , Where i don't need the middle level data. I need parent and its leaf child along with the id's of parent and child

Expected output

id_p p_id id_c c_id

4 100 1 501
4 100 2 502
4 100 3 503
5 200 1 501
5 200 2 502
5 200 3 503
6 300 6 400
7 101 7 500
8 102 8 500
9 103 9 500
10 201 10 600
11 201 11 601
12 201 12 602

I tried the following query where i got the p_id,id_c,c_id columns , but unable to get the column id_p

select connect_by_root p_id p_id ,id id_c,c_id
from par_chld
where connect_by_isleaf =1
start with p_id not in (select c_id
from par_chld
where c_id is not null)
connect by nocycle p_id =prior c_id

getting the id_p column as expected above.

View 2 Replies View Related

SQL & PL/SQL :: How To Get All The Data From Child Tables

Jun 2, 2011

I have a requirement to get the data in "SQL" directly and should not use plsql/ can't redirect the output to a .sql file. I have a master table Main_tab which does have all "child table" names.

I want to build a query such that it will give all the data from child tables.

create table main_tab ( tab_name varchar(10));

insert into main_tab values ('one');
insert into main_tab values ('two');
insert into main_tab values ('three');
create table one ( a char);
insert into one values ('a')
create table two ( b char);
insert into two values ('b')
create table three ( c char);
insert into three values ('c');

Now I have got the query to combine all the table data using

select decode (rownum,1,'','union all ') || 'select * from ' || tab_name example from main_tab

but I am expecting the output to get as
a
b
c
which is the data from tables one,two and three respectively.

I don't want to save the above select data in to .sql and then execute it , instead I want to run it on "fly" in SQL itself. Is there such a possibility to do so?

View 12 Replies View Related

SQL & PL/SQL :: Batch ID From Child Status

Mar 27, 2013

Im looking for a query which returns the batch for which all the child should either be in 'A_STATUS','B_STATUS' or 'C_STATUS'. In this query im expecting a query which returns batch 2,3 and 4.

create table batch (batchid number);
insert into batch values(1);
insert into batch values(2);
insert into batch values(3);
insert into batch values(4);

[Code]...

View 9 Replies View Related

SQL & PL/SQL :: Parent-Child Hierarchy?

Dec 20, 2012

way to achieve the below logic.

SQL to pick up parent child relationship within same table with a certain logic.

Example:
mod_product_numberProduct_Hierarchy
H555888 PH05678
H888987 H555888
H8889 H555888

[code]...

Example: I expect the rows with H8889,H9955 & P6666 & P5555 to be sub-category values value for product hierarchy H555888.

If there are rows with H8888987 as Product_hierarchy, we will pull up those rows too for product hierarchy H555888. The extra condition is we drill down only on 7 character mod_prod_number not on 5 character mod_prod_number. We pull out all sub category mod_prod_number for all distinct Product hierarchy.

View 11 Replies View Related

SQL & PL/SQL :: Parent Child Relation

Sep 17, 2013

I have one requirment like below

Table1 have records like below

parent childflag
null.....A......Y
A........B......Y
B........C
C........D
D........E......Y

I want it should be like

parent child flag
null......A.....Y
A.........B.....Y
B.........E.....Y

I want to replace 'D' (parent of 'E') with any of the upper level which has Flag 'Y' (first level parent which satisfy falg 'Y')

View 7 Replies View Related

PL/SQL :: How To Replace 2 Single Quotes To Single Quote

Nov 21, 2012

I have an script.sql that receives as a parameter an string.

example:

@C:/myscript.sql "o'connor"

user_account_value varchar2(120) := '&1';

EXECUTE IMMEDIATE "Select * from Table where column = :1 "  USING user_account_value I am not sure how to deal with string that contains single quotes.

If the parameter were passed as : "o''connor" this will work
If the parameter is pass as: "o'connor" this will not work.

so my question is what options do I have to deal with dynamic queries and single quotes.

I tried replacing replace(myParameter,'''',''''''); but not working well.

View 11 Replies View Related

SQL & PL/SQL :: Creating Parent Child Relationship

Aug 8, 2011

I have a table called FAMILY which holda the data like , for father_gen_1 --->father_gen_2 --> father_gen_3 -- son.

father_gen_1 father_gen_2 father_gen_3 son
ABC XYZ GEF MNC
ABC lmn kth

I want to get the output which is a three column heirarical table and holds the parent child relationship Like following:-

id name parent_id
1 ABC NULL
2 XYZ 1
3 GEF 2
4 MNC 3
5 LMN 1
6 KTH 5

View 7 Replies View Related

SQL & PL/SQL :: Way Of Getting Only Parent And Child Record From Table

Jul 27, 2010

i have a table with data as follows:

select genres.* from genres

data is as follows

INTPRODUCTIDVCHGENRENAME INTGENREPAGEIDINTPARENTIDINTGENREID
14430015Biography 157 0100
14430015Classics & Poetry 173 0116
14430015Literature & Anthologies 175 173118

now when i give

select level,genres.* from genres connect by prior INTGENREPAGEID= INTPARENTID

i get

LEVELINTPRODUCTIDVCHGENRENAME INTGENREPAGEIDINTPARENTIDINTGENREID
114430015Biography 157 0100
114430015Classics & Poetry 173 0116
214430015Literature & Anthologies 175 173118
114430015Literature & Anthologies 175 173118

i need to find the parent and child from the table in this case the parent is Classics & Poetry and child is Literature & Anthologies..the way of getting only the parent and child record from this table.

View 5 Replies View Related

SQL & PL/SQL :: Parent And Child Table - Delete Row

May 18, 2010

I have a parent table and child table. I want a row to be deleted from the parent table which is referenced by a child row. Is there a way to achieve this. I dont have permission to re create the table or alter the table using delete cascade option. Is there a way to do it in sql.

SQL> create table t1(a number primary key, b number);
SQL> create table t2(c number, d number references t1(a));
SQL> insert into t1 values(1,2);
SQL> insert into t1 values(2,3);
SQL> insert into t1 values(3,4);
SQL> insert into t2 values(10,3);
SQL> insert into t2 values(20,2);
SQL> delete from t1 where a=2;
delete from t1 where a=2
*

ERROR at line 1:
ORA-02292: integrity constraint (CISBATCH.SYS_C00763501) violated - child
record found

View 11 Replies View Related

SQL & PL/SQL :: Extract Correct Value For Child Records

Mar 22, 2011

Test case: drop table test;

create table test (id number, last_name varchar2(15), first_name varchar2(15), empno varchar2(15))

select * from test;

insert into test values (143,'frank','kadel,watson','j2098,k09876');
insert into test values (143,'steve','kadel,watson','j0987i,kuy765');
[code]....

The requirement is as follows: I need to split the rows by first_name and assign the respective empno in the results child rows if there is any.

For example:- Where id = 143, the resultset should be like this.

ID LAST_NAME FIRST_NAME EMPNO
---------- --------------- --------------- ---------------
143 frank kadel j2098
143 frank watson k09876
143 steve kadel j0987i
143 steve watson kuy765

sofar, i am able to come with the query to split the records by last name but unable to find the way to extract the respective empno and assign to the splited records correctly.

SQL> select id, last_name, EXTRACTVALUE(x.COLUMN_VALUE, 'e') first_name, empno
2 from test,
3 TABLE(XMLSEQUENCE(XMLTYPE('<e><e>' || REPLACE(first_name, ',', '</e><e>') || '</e></e>')
4 .EXTRACT('e/e'))) x
5 ;
[code]...

View 8 Replies View Related

SQL & PL/SQL :: Update Parent Child Table?

May 26, 2011

i want to update primary key of 1 master and 2 child table is it possible if i do it simultaniously if not

View 9 Replies View Related

SQL & PL/SQL :: Delete - Parent / Child Relationship

May 22, 2012

I have a DB that has one parent table with many child tables. I would like to delete all records(child - parent) based upon a particular "net_id". I have three versions that "seem" to work BUT I'd like to use "version2" because I want to go to the parent one time(for the parent delete I just made it simple for me) only and be able to delete the child record(s) from the child table(s). I want to achieve this w/o using triggers...

My question: Is version2 an "ok" way to delete child records or should I try another method(version1 or 3)??

create table PARENT
(
event_id NUMBER,
event_title VARCHAR2(50),
net_id VARCHAR2(16)
);

--truncate table parent;
insert into parent
(event_id, event_title, net_id)
values
(1, 'title1', 'CURLY');
[code]...

View 10 Replies View Related







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