Home » Developer & Programmer » JDeveloper, Java & XML » select the text of one node (oracle 10g, xml)
select the text of one node [message #453333] Wed, 28 April 2010 05:23 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
I have XML data with many levels. I have many "NAME" nodes/tags in my xml in diffrent levels. Which function should i use to get the text of "NAME" node/tag which is coming first (not all) in my XML.
I tried with "extract" function but i get total texts (appended all texts). Please help me to filter all texts except first one.
I tried with index also (like NAME[1]) but not getting required output.
Regards,
Madhavi.
Re: select the text of one node [message #453334 is a reply to message #453333] Wed, 28 April 2010 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 66633
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you post what you have and what you tried maybe we can help.

Regards
Michel
Re: select the text of one node [message #453357 is a reply to message #453333] Wed, 28 April 2010 06:22 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
HI,
My actual requirement is get the metadata of one table and store it in one oracle table. I want to know the name and type of object also while fetching.
To store the metadata i created the following table.
CREATE TABLE MY_SCHEMAMETA4 (Objtype VARCHAR2(50), Objname VARCHAR2(1000), MD XMLTYPE);

I used the following procedure to fetch metadata of table.
 1  CREATE OR REPLACE procedure Get_schema_XML2
 2  --return xmltype
 3  authid current_user
 4  IS
 5  -- Define local variables.
 6  h1      NUMBER;         -- handle returned by OPEN for tables
 7  th1     NUMBER;         -- handle returned by ADD_TRANSFORM for tables
 8  doc     sys.XMLtype;   -- metadata is returned in sys.ku$_ddls,
 9  --DDL     xmltype;           -- creation DDL for an object
10  pi      parsed_items;   -- parse items are returned in this object
11  Obj_type varchar2(500);
12  obj_name varchar2(10000);
13  SQL_ERROR     NUMBER := SQLCODE;
14  SQL_ERRMESS   VARCHAR2 (250) := SUBSTR (SQLERRM, 1, 200);
15  BEGIN
16   h1 := DBMS_METADATA.OPEN('SCHEMA_EXPORT');
17   DBMS_METADATA.SET_FILTER(H1,'SCHEMA','EMACH');
18   DBMS_metadata.set_filter(h1,'INCLUDE_PATH_EXPR','IN''TABLE''');
19   DBMS_METADATA.SET_FILTER(H1,'NAME_EXPR','LIKE''PMN_ORG_T''','TABLE');
20   DBMS_METADATA.SET_PARSE_ITEM(h1,'NAME');
21  DBms_output.put_line(3);
22   LOOP
23     doc := dbms_metadata.fetch_XML(h1);
24     EXIT WHEN doc IS NULL;
25   SELECT   Nodes into obj_type FROM   (SELECT   t2.COLUMN_VALUE.GETROOTELEMENT () Nodes, Xml
26                                  FROM   (SELECT   EXTRACT (doc,
27                                                '/ROWSET/ROW') Xml FROM   dual) t,
28                                  TABLE (XMLSEQUENCE (t.xml.EXTRACT ('//node()'))) t2)
29                                  WHERE   ROWNUM = 1 AND Nodes NOT IN ('ROWSET', 'ROW');    
                                    -- SELECT USED TO GET OBJECT TYPE
30  DBms_output.put_line(OBJ_TYPE);
31    select DOC.extract('/ROWSET/ROW//NAME/text()').getstringval() INTO OBJ_NAME from DUAL ;    
             -- NEED TO APPLY FILTER 
32    dbms_output.put_line(' The srting value is '|| Obj_Name);
33       INSERT INTO MY_SCHEMAMETA4(Objtype, Objname, md)
34         VALUES (Obj_Type, obj_name,DOC);
35       COMMIT;
36     END LOOP;
37   DBMS_METADATA.CLOSE(h1);
38    EXCEPTION
39   WHEN OTHERS THEN
40     DBMS_OUTPUT.PUT_LINE(OBJ_NAME);
41     DBMS_OUTPUT.PUT_LINE(OBJ_type);
42* END;
43  /

Procedure created.

Then i executed the procedure without any error.
But when selecting data from table ...I get the object name like object name along with its column names also. Because in xml, so many "NAME" nodes are available.
I need to apply filters (i think) to get only object name at line number:31.
please suggest me.
Regards,
Madhavi.

[Updated on: Wed, 28 April 2010 07:18] by Moderator

Report message to a moderator

Re: select the text of one node [message #453366 is a reply to message #453357] Wed, 28 April 2010 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 66633
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the code.
What I'm interesting on is one example of your data and the result you want from it.

Regards
Michel
Re: select the text of one node [message #453376 is a reply to message #453333] Wed, 28 April 2010 06:57 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Michel,
I got the below output (i selected one record which gives wrong output).
OBJTYPE                                 OBJNAME
---------------------------------------------------------------
TRIGGER_T
    G_PMN_ORG_HISTPMN_ORG_TORG_IDORG_IDORG_TECH_IDORG_TECH_IDORG_TECH_IDID_BYID_BYMAIN_TAP_CDMAIN_TAP_
CDALT_TAP_CDALT_TAP_CDTAPE_CDTAPE_CDORG_NMORG_NM


I'm expecting the trigger name as output (G_PMN_ORG_HIST). The remaing text is text of diffrent nodes having its tag name is "NAME".
PMN_ORG_T is the table name, ORG_ID is the one column of PMN_ORG_T table.... The table name and column names having a node/tag as "NAME". So it appends all names and throw as output. Finally i want to get the text of "NAME" coming first in xml doc.
regards,
madhavi.

[Updated on: Wed, 28 April 2010 07:17] by Moderator

Report message to a moderator

Re: select the text of one node [message #453380 is a reply to message #453376] Wed, 28 April 2010 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 66633
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I got the below output (i selected one record which gives wrong output).

But what is the input?
And what should be the ourput?

Regards
Michel

[Updated on: Wed, 28 April 2010 07:17]

Report message to a moderator

Re: select the text of one node [message #453383 is a reply to message #453333] Wed, 28 April 2010 07:45 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Michel,
There is no input actually. My procedure fetches the metadata of one table (PMN_ORG_T) from one of my database using DBMS_METADATA API. I think along with table metadata it fetches the base objects metadata also (INDEX, CONSTRAINT, TRIGGER, OBJECT_GRANT). Just i want to track the object type and object name along with metadata.
The expected output is Object type, Object name and metadata of an object (Table, constraint, index, triggers).
I got correct object type and metadata (xml form). In case of object name its coming with more (total table name and column names for depedent objects like trigger in above exxample).
Regards,
Madhavi.
Re: select the text of one node [message #453385 is a reply to message #453383] Wed, 28 April 2010 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 66633
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK I misunderstood the issue.

Regards
Michel

[Updated on: Wed, 28 April 2010 07:56]

Report message to a moderator

Re: select the text of one node [message #453444 is a reply to message #453333] Thu, 29 April 2010 00:54 Go to previous message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
I'm new to xml. So i tried to get the solution for above requirement using SUBSTR and INSTR as a db developer.
Simply it works out.
I just add some more logic to program at line number 31.
The added code is...
SELECT SUBSTR(ABC, (INSTR(ABC,'<NAME>',1,1)+6),(INSTR(ABC,'</NAME>',1,1)-7)) INTO OBJ_NAME FROM (
  select DOC.extract('/ROWSET/ROW//NAME').getstringval() ABC from DUAL) ;      

Its good if any other solutions.
Regards,
Madhavi.
Previous Topic: deploy the project
Next Topic: help needed in xml
Goto Forum:
  


Current Time: Mon Oct 14 05:20:59 CDT 2019