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
    	
    	
        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
  
    
	
    	
    	
        Jun 24, 2013
        how to add an xml element in oracle 10gr1 without using Oracle XDB?
	View 1 Replies
    View Related
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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
  
    
	
    	
    	
        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