Home » SQL & PL/SQL » SQL & PL/SQL » display the first two node names from a variable (Oracle 10g)
display the first two node names from a variable [message #439890] Tue, 19 January 2010 21:52 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
I have xmldata in one of the column of database table. Iam selecting the xmldata from the column ang keep it one variable.
Part of my requirement is extracting some part of xmldata and then display first two node name from that part. Here i used extract function to extract some portion and keep it another variable. Now i stuck here to display the first two node names (i have more than 500 nodes in that portion). Below is my code.

declare
V_XML XMLTYPE;
XML XMLTYPE;
V_XML_IND XMLTYPE;
V_Node VARCHAR2(15);
begin
select repository into v_xml from ddi_repos_t where object_id = 7;
select extract (v_xml,'/ROWSET/ROW[1]') INTO V_XML_IND FROM DUAL;

-------------------
-------------------
DBMS_OUTPUT.PUT_LINE(V_NODE);

END;


If it is in SQL we did like this.
michaels>  with t as 
(
select xmltype('<Objects>
                  <Label ID="lblTest">Test</Label>
                  <Label>Another Test</Label>
                  <Text ID="txtTest">Text Test</Text>
                </Objects>') xml 
 from dual
)
---
---
select t2.column_value.getrootelement() nodes
  from t t, table(xmlsequence(t.xml.extract('//node()'))) t2
 
NODES                         
------------------------------
Objects                       
Label                         
Label                         
Text                          
 
 
4 rows selected.


How to use above logic in pl/sql program?
I got 'Table or view does not exists' error when i use variable name in from clause.
Please help me in this corner.
Regards,
Madhavi.
Re: display the first two node names from a variable [message #439896 is a reply to message #439890] Tue, 19 January 2010 23:53 Go to previous message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
I got the solution for above requirement by using inline view. In the next part of my requirement, i want to display the total (count) of child nodes in one level. For example I have xmldata like main node (root node name is ROWSET), 10 child nodes (name is ROW). Under each child node iam having around 500 nodes (Idont know the technical names).
Here i want to display the count of first level child nodes (In this case 10). How can we do this?

Regards,
Madhavi.
Previous Topic: SQL Replace Question
Next Topic: Bypass package function with name conflict
Goto Forum:
  


Current Time: Tue Sep 27 21:12:30 CDT 2016

Total time taken to generate the page: 0.11964 seconds