Home » Developer & Programmer » JDeveloper, Java & XML » need query to extract values from xml data (merged) (oracle 10g)
need query to extract values from xml data (merged) [message #459675] Mon, 07 June 2010 22:36 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
Please tell me a query to extract the node name (after the nodes of ROWSET/ROW), The values of OWNER_NAME, NAME from a oracle table having xml as data.
Please find the below data is the record of my oracle table.
Also i have the many nodes of Owner name and Name. But i want to extract the the owner name and name of OWNER_NAME and NAME nodes. If OWNER_NAME node not exists in the xml data i want to display as 'SYS'.
<ROWSET>
  <ROW>
    <TABLE_T>
      <VERS_MAJOR>1</VERS_MAJOR>
      <VERS_MINOR>1 </VERS_MINOR>
      <OBJ_NUM>69437</OBJ_NUM>
      <SCHEMA_OBJ>
        <OBJ_NUM>69437</OBJ_NUM>
        <DATAOBJ_NUM>69437</DATAOBJ_NUM>
        <OWNER_NUM>159</OWNER_NUM>
        <OWNER_NAME>ICO</OWNER_NAME>
        <NAME>IAR_ICR_T</NAME>
        <NAMESPACE>1</NAMESPACE>
        <TYPE_NUM>2</TYPE_NUM>
        <TYPE_NAME>TABLE</TYPE_NAME>
        <CTIME>2009-11-18 17:18:40</CTIME>
        <MTIME>2010-02-17 00:46:28</MTIME>
        <STIME>2009-11-18 17:18:40</STIME>
        <STATUS>1</STATUS>
        <FLAGS>0</FLAGS>
        <SPARE1>6</SPARE1>
        <SPARE2>1</SPARE2>
      </SCHEMA_OBJ>
       <COL_LIST>
        <COL_LIST_ITEM>
          <OBJ_NUM>69437</OBJ_NUM>
          <COL_NUM>1</COL_NUM>
          <INTCOL_NUM>1</INTCOL_NUM>
          <SEGCOL_NUM>1</SEGCOL_NUM>
          <PROPERTY>0</PROPERTY>
          <NAME>CUST_TECH_ID</NAME>
          <TYPE_NUM>2</TYPE_NUM>
          <LENGTH>22</LENGTH>
          <SCALE>0</SCALE>
          <NOT_NULL>1</NOT_NULL>
          <CHARSETID>0</CHARSETID>
          <CHARSETFORM>0</CHARSETFORM>
          <CON>
            <OWNER_NUM>159</OWNER_NUM>
            <NAME>SYS_C0058265</NAME>
            <CON_NUM>58265</CON_NUM>
            <OBJ_NUM>69437</OBJ_NUM>
            <NUMCOLS>1</NUMCOLS>
            <CONTYPE>7</CONTYPE>
            <ENABLED>1</ENABLED>
            <INTCOLS>1</INTCOLS>
            <MTIME>18-NOV-09</MTIME>
            <FLAGS>12</FLAGS>
          </CON>
          <SPARE1>0</SPARE1>
          <SPARE2>0</SPARE2>
          <SPARE3>0</SPARE3>
        </COL_LIST_ITEM>
    <COL_LIST>
  </ROW>
</ROWSET>


I have copied the part of my xml data as its size is very large to paste.
My oracle table name is T_Metadata and column name having xml data is MD.
Regards,
Madhavi.

Re: need query to extract values from xml data [message #459679 is a reply to message #459675] Mon, 07 June 2010 23:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
After fixing your data which is not a valid XML string:
SQL> col owner format a10
SQL> col table_name format a20
SQL> with data as (
  2    select xmltype('<ROWSET>
  3    <ROW>
  4      <TABLE_T>
  5        <VERS_MAJOR>1</VERS_MAJOR>
  6        <VERS_MINOR>1 </VERS_MINOR>
  7        <OBJ_NUM>69437</OBJ_NUM>
  8        <SCHEMA_OBJ>
  9          <OBJ_NUM>69437</OBJ_NUM>
 10          <DATAOBJ_NUM>69437</DATAOBJ_NUM>
 11          <OWNER_NUM>159</OWNER_NUM>
 12          <OWNER_NAME>ICO</OWNER_NAME>
 13          <NAME>IAR_ICR_T</NAME>
 14          <NAMESPACE>1</NAMESPACE>
 15          <TYPE_NUM>2</TYPE_NUM>
 16          <TYPE_NAME>TABLE</TYPE_NAME>
 17          <CTIME>2009-11-18 17:18:40</CTIME>
 18          <MTIME>2010-02-17 00:46:28</MTIME>
 19          <STIME>2009-11-18 17:18:40</STIME>
 20          <STATUS>1</STATUS>
 21          <FLAGS>0</FLAGS>
 22          <SPARE1>6</SPARE1>
 23          <SPARE2>1</SPARE2>
 24        </SCHEMA_OBJ>
 25         <COL_LIST>
 26          <COL_LIST_ITEM>
 27            <OBJ_NUM>69437</OBJ_NUM>
 28            <COL_NUM>1</COL_NUM>
 29            <INTCOL_NUM>1</INTCOL_NUM>
 30            <SEGCOL_NUM>1</SEGCOL_NUM>
 31            <PROPERTY>0</PROPERTY>
 32            <NAME>CUST_TECH_ID</NAME>
 33            <TYPE_NUM>2</TYPE_NUM>
 34            <LENGTH>22</LENGTH>
 35            <SCALE>0</SCALE>
 36            <NOT_NULL>1</NOT_NULL>
 37            <CHARSETID>0</CHARSETID>
 38            <CHARSETFORM>0</CHARSETFORM>
 39            <CON>
 40              <OWNER_NUM>159</OWNER_NUM>
 41              <NAME>SYS_C0058265</NAME>
 42              <CON_NUM>58265</CON_NUM>
 43              <OBJ_NUM>69437</OBJ_NUM>
 44              <NUMCOLS>1</NUMCOLS>
 45              <CONTYPE>7</CONTYPE>
 46              <ENABLED>1</ENABLED>
 47              <INTCOLS>1</INTCOLS>
 48              <MTIME>18-NOV-09</MTIME>
 49              <FLAGS>12</FLAGS>
 50            </CON>
 51            <SPARE1>0</SPARE1>
 52            <SPARE2>0</SPARE2>
 53            <SPARE3>0</SPARE3>
 54          </COL_LIST_ITEM>
 55        </COL_LIST>
 56      </TABLE_T>
 57    </ROW>
 58  </ROWSET>') xml_data from dual
 59    )
 60  select nvl(extractvalue(value(x), '/SCHEMA_OBJ/OWNER_NAME'),'SYS') owner,
 61         extractvalue(value(x), '/SCHEMA_OBJ/NAME') table_name
 62  from data, table(xmlsequence(extract(xml_data, '/ROWSET/ROW/TABLE_T/SCHEMA_OBJ'))) x
 63  /
OWNER      TABLE_NAME
---------- --------------------
ICO        IAR_ICR_T

Regards
Michel
Re: need query to extract values from xml data [message #459692 is a reply to message #459679] Tue, 08 June 2010 00:46 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Michel,
Thank you for response.

I have many records in the oracle table (xml data in records). The 3rd node is not constant ((1st node is ROWSET and 2nd node is ROW for all recors, where as the 3rd node (TABLE_T, in the above case) is diffrent. Also, for some records the SCHEMA_OBJ exists, for some records it is BASE_OBJ and for some records there is no node like that.

can we use OR condition like SCHEMA_OBJ OR BASE_OBJ in the query path?

Is there any query to get the values dynamically without doing hard code.

Regards,
Madhavi.

[Updated on: Tue, 08 June 2010 00:48]

Report message to a moderator

Re: need query to extract values from xml data [message #459698 is a reply to message #459692] Tue, 08 June 2010 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to provide a test case that is representative of the cases you can have along with the result you want for each case.
Remove all what is not relevant to the question (for instance the COL_LIST part and so on).

Regards
Michel

[Updated on: Tue, 08 June 2010 01:01]

Report message to a moderator

Re: need query to extract values from xml data [message #459705 is a reply to message #459698] Tue, 08 June 2010 01:38 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
Please find the test case.

CREATE TABLE T_DB_MD_XML (Objtype VARCHAR2(50), MD XMLTYPE);

INSERT INTO T_DB_MD_XML VALUES ('TABLESPACE_T', XMLTYPE.CREATEXML('<ROWSET>
  <ROW>
    <TABLESPACE_T>
      <NAME>S_ACT_TEG_CED_D</NAME>
      <OWNER_NUM>0</OWNER_NUM>
      </TABLESPACE_T>
  </ROW>
</ROWSET>') );

INSERT INTO T_DB_MD_XML VALUES ('PROFILE_T', XMLTYPE.CREATEXML('<ROWSET>
  <ROW>
    <PROFILE_T>
       <PROFILE_ID>3</PROFILE_ID>
      <PROFILE_NAME>MACH_APP_PROFILE</PROFILE_NAME>
      </PROFILE_T>
  </ROW>
</ROWSET>') );

INSERT INTO T_DB_MD_XML VALUES ('TABLE_T', XMLTYPE.CREATEXML('<ROWSET>
  <ROW>
    <TABLE_T>
      <OBJ_NUM>51253</OBJ_NUM>
      <SCHEMA_OBJ>
        <OWNER_NUM>54</OWNER_NUM>
        <OWNER_NAME>SCOTT</OWNER_NAME>
        <NAME>BONUS</NAME>
        <NAMESPACE>1</NAMESPACE>
      </SCHEMA_OBJ>
      <COL_LIST>
        <COL_LIST_ITEM>
          <PROPERTY>0</PROPERTY>
          <NAME>ENAME</NAME> 
        </COL_LIST_ITEM>
       </COL_LIST>
    </TABLE_T>
  </ROW>
</ROWSET>') );

INSERT INTO T_DB_MD_XML VALUES ('SYSGRANT_T', XMLTYPE.CREATEXML('<ROWSET>
  <ROW>
    <SYSGRANT_T>
      <GRANTEE>IMP_FULL_DATABASE</GRANTEE>
      <PRIVNAME>DROP ANY MATERIALIZED VIEW</PRIVNAME>
      </SYSGRANT_T>
  </ROW>
</ROWSET>') );

INSERT INTO T_DB_MD_XML VALUES ('COMMENT_T', XMLTYPE.CREATEXML('<ROWSET>
  <ROW>
    <COMMENT_T>
      <OBJ_NUM>69447</OBJ_NUM>
      <BASE_OBJ>
        <OWNER_NAME>ICO</OWNER_NAME>
        <NAME>ICN_POI_T</NAME>
      </BASE_OBJ>
    </COMMENT_T>
  </ROW>
</ROWSET>') );

COMMIT;


Finally i'm expecting the Object name and owner from each ecord.
Please find, there are multiple number of NAME nodes (In case of TABLE_T). I want the first node value if multiples exist.

Regards,
Madhavi.
Re: need query to extract values from xml data [message #459716 is a reply to message #459705] Tue, 08 June 2010 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please find, there are multiple number of NAME nodes (In case of TABLE_T). I want the first node value if multiples exist.

In your example, there is only one TABLE_T.
In the end, it is not clear what you want.

Please post data for a complete set of cases you can have.
Please post the result you want with these data.

Regards
Michel
Re: need query to extract values from xml data [message #459728 is a reply to message #459716] Tue, 08 June 2010 02:05 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Michel,
What i meant to say is.. There is one record with OBJTYPE of 'TABLE_T' only. The related xml has multiple NAME nodes.
I used just '//NAME' in the query where we need to use '/SCHEMA_OBJ/NAME'. So i got ORA-19025: EXTRACTVALUE returns value of only one node error. But for some objects there is no node like SCHEMA_OBJ.

Finally my problem is getting the objname, object owner from each record having diffrent node names.

Regards,
Madhavi.
Re: need query to extract values from xml data [message #459732 is a reply to message #459728] Tue, 08 June 2010 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I said is:
- post an example of ALL cases you can have
- post the result you want for these data (as a table result)
- if you have error, copy and paste your SQL*Plus session as I did.

Currently I don't understand what you want EXACTLY.

Regards
Michel
Re: need query to extract values from xml data [message #459734 is a reply to message #459732] Tue, 08 June 2010 02:53 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
To returns value of only one node you can use a query like:
WITH xml_data AS (  
SELECT XMLTYPE ('
<ROWSET>
  <ROW>
    <TABLESPACE_T>
      <NAME>S_ACT_TEG_CED_D</NAME>
      <OWNER_NUM>0</OWNER_NUM>
      </TABLESPACE_T>
  </ROW>
  <ROW>
    <PROFILE_T>
       <PROFILE_ID>3</PROFILE_ID>
      <PROFILE_NAME>MACH_APP_PROFILE</PROFILE_NAME>
      </PROFILE_T>
  </ROW>
  <ROW>
    <TABLE_T>
      <OBJ_NUM>51253</OBJ_NUM>
      <SCHEMA_OBJ>
        <OWNER_NUM>54</OWNER_NUM>
        <OWNER_NAME>SCOTT</OWNER_NAME>
        <NAME>BONUS</NAME>
        <NAMESPACE>1</NAMESPACE>
      </SCHEMA_OBJ>
      <COL_LIST>
        <COL_LIST_ITEM>
          <PROPERTY>0</PROPERTY>
          <NAME>ENAME</NAME> 
        </COL_LIST_ITEM>
       </COL_LIST>
    </TABLE_T>
  </ROW>
  <ROW>
    <SYSGRANT_T>
      <GRANTEE>IMP_FULL_DATABASE</GRANTEE>
      <PRIVNAME>DROP ANY MATERIALIZED VIEW</PRIVNAME>
      </SYSGRANT_T>
  </ROW>
  <ROW>
    <COMMENT_T>
      <OBJ_NUM>69447</OBJ_NUM>
      <BASE_OBJ>
        <OWNER_NAME>ICO</OWNER_NAME>
        <NAME>ICN_POI_T</NAME>
      </BASE_OBJ>
    </COMMENT_T>
  </ROW>
</ROWSET>') xcoll FROM dual)
SELECT 
  extractvalue(column_value,'//*') xname
  FROM xml_data, TABLE(XMLSequence(extract(xcoll,'//NAME'))); 

XNAME       
---------------
S_ACT_TEG_CED_D
BONUS               
ENAME
ICN_POI_T

You find many examples in the Oracle documentation!
Re: need query to extract values from xml data [message #459736 is a reply to message #459732] Tue, 08 June 2010 02:59 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
I'm expecting a sql query to get below output.

OBJ_OWNER	OBJ_NAME

SYS	      S_ACT_TEG_CED_D
SYS	      MACH_APP_PROFILE
SCOTT	      BONUS
SYS	      IMP_FULL_DATABASE
ICO	      ICN_POI_T


Is there any query to get output as above?
We can achieve the above with hard code. Is there any other methods?

I used diffrent select statements to get above output.

select nvl(extractvalue(value(x), '/SCHEMA_OBJ/OWNER_NAME'),'SYS') owner,
   extractvalue(value(x), '//PROFILE_NAME') table_name
from T_DB_MD_XML e , table(xmlsequence(extract(e.md, '/ROWSET/ROW'))) x
where objtype in ('PROFILE_T');


select nvl(extractvalue(value(x), '/SCHEMA_OBJ/OWNER_NAME'),'SYS') owner,
   extractvalue(value(x), '//NAME') table_name
from T_DB_MD_XML e , table(xmlsequence(extract(e.md, '/ROWSET/ROW'))) x
where objtype in ('TABLESPACE_T');

select nvl(extractvalue(value(x), '/SCHEMA_OBJ/OWNER_NAME'),'SYS') owner,
   extractvalue(value(x), '//GRANTEE') table_name
from T_DB_MD_XML e , table(xmlsequence(extract(e.md, '/ROWSET/ROW'))) x
where objtype in ('SYSGRANT_T');

select nvl(extractvalue(value(x), '/TABLE_T/SCHEMA_OBJ/OWNER_NAME'),'SYS') owner,
   extractvalue(value(x), '/TABLE_T/SCHEMA_OBJ/NAME') table_name
from T_DB_MD_XML e , table(xmlsequence(extract(e.md, '/ROWSET/ROW/TABLE_T'))) x
where objtype in ('TABLE_T');

select nvl(extractvalue(value(x), '/COMMENT_T/BASE_OBJ/OWNER_NAME'),'SYS') owner,
   extractvalue(value(x), '/COMMENT_T/BASE_OBJ/NAME') table_name
from T_DB_MD_XML e , table(xmlsequence(extract(e.md, '/ROWSET/ROW//COMMENT_T'))) x
where objtype in ('COMMENT_T');


Is it possible with one select statement?
Regards,
Madhavi.

[Updated on: Tue, 08 June 2010 03:04]

Report message to a moderator

Re: need query to extract values from xml data [message #459804 is a reply to message #459736] Tue, 08 June 2010 06:51 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Don't know a solution may be with XQuery?
ORA-31011: XML parsing failed, LPX-00601: Invalid token in: '//' [message #459822 is a reply to message #459675] Tue, 08 June 2010 07:32 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
I have a small error with my procedure.
I want to pass the value to the extract function with a variable. I got the below error.
create or replace procedure p_xml_test 
as
V_Owner2 varchar2(50);
V_Obj_Name2 varchar2(50);
V_TOTAL XMLTYPE;
V_NEXT_PART XMLTYPE;
V_OWNER_PATH VARCHAR2(50);
V_NAME_PATH VARCHAR2(50);
v_xml xmltype;
begin

    FOR I IN (SELECT * FROM MY_Db_MD_XML where objtype in ('TABLESPACE_T')) LOOP
       v_xml := i.md;
    IF I.OBJTYPE IN ('TABLESPACE','USER','ROLE') THEN V_NAME_PATH := 'NAME';
        ELSIF I.OBJTYPE = 'PROFILE' THEN V_NAME_PATH := 'PROFILE_NAME';
    END IF;
    
    SELECT OWNER, Obj_Name INTO V_Owner2, V_Obj_Name2 
FROM (select nvl(extractvalue(value(x), '/SCHEMA_OBJ/OWNER_NAME'),'SYS') owner,
  extractvalue(value(x), '//'||V_NAME_PATH) Obj_name
                from table(xmlsequence(extract(v_xml, '/ROWSET/ROW'))) x ) ;
    DBMS_OUTPUT.PUT_LINE(V_Owner2);
    DBMS_OUTPUT.PUT_LINE(V_Obj_Name2);
    INSERT INTO xml_test VALUES (V_OBJ_NAME2, V_TOTAL);
      COMMIT;
    END LOOP;
END;


error is ..
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '//'
ORA-06512: at "DDI_TEST.P_XML_TEST", line 20
ORA-06512: at line 1



How to pass value through an variable?

When i use path name directly without using variable, there is no error.
please help regarding this.
Regards,
Madhavi.

[Updated on: Tue, 08 June 2010 07:45] by Moderator

Report message to a moderator

Re: ORA-31011: XML parsing failed, LPX-00601: Invalid token in: '//' [message #459827 is a reply to message #459822] Tue, 08 June 2010 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AFAIK, You can't put a variable in the xpath.

Use SQL*Plus to make your test and copy them here, we don't which one is line 20.
In addition, as this related to your previous topic, even if you succeed to get a valid syntax, you will get an error: "ORA-19025: EXTRACTVALUE returns value of only one node".

The main problem in your previous topic (I will merge this one to it) is not the unknown node name, it is the above error and the fact that you want to sometimes retrieves data from 2 parent different parent nodes for owner and object name.

Regards
Michel
Re: ORA-31011: XML parsing failed, LPX-00601: Invalid token in: '//' [message #459833 is a reply to message #459827] Tue, 08 June 2010 08:05 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Michel,
Thank you response.

I have tried a small program like using variable in xpath. Its working fine.
  1  DECLARE
  2  V_OWNER VARCHAR2(50);
  3  V_NAME VARCHAR2(50);
  4  V_PATH VARCHAR2(50);
  5  BEGIN
  6  V_PATH := 'NAME';
  7   SELECT OWNER, Obj_Name INTO V_OWNER, V_NAME FROM
  8    (select nvl(extractvalue(value(x), '/SCHEMA_OBJ/OWNER_NAME'),'SYS') owner,
  9    extractvalue(value ( x ), '//'||V_PATH) Obj_name
 10     from MY_DB_MD_XML, table(xmlsequence(extract(MD, '/ROWSET/ROW'))) x ) WHERE ROWNUM = 1;
 11                 DBMS_OUTPUT.PUT_LINE(V_Owner);
 12                 DBMS_OUTPUT.PUT_LINE(V_Name);
 13*     END;
SQL> /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
SYS
S_ACT_TEG_CED_D


What i'm getting is for each diff path, we need to use SELECT statement. Is it correct?
Thanks for your great support.
Regards,
Madhavi.
Re: ORA-31011: XML parsing failed, LPX-00601: Invalid token in: '//' [message #459839 is a reply to message #459833] Tue, 08 June 2010 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, the probel is not there, it is in those cases where there are several nodes below "//ROW/something" and you get OWNER_NAME from one node and NAME from another one as in the TABLE_T row.
The variable part can easily be solved with:
SQL> SELECT nvl(extractvalue(value(x),'//*/OWNER_NAME'),'SYS') obj_owner
  2  FROM T_DB_MD_XML, 
  3       TABLE(XMLSequence(extract(md,'//ROW'))) x
  4  /
OBJ_OWNER
-------------------------------
SYS
SYS
SCOTT
SYS
ICO

Regards
Michel
Re: ORA-31011: XML parsing failed, LPX-00601: Invalid token in: '//' [message #459844 is a reply to message #459833] Tue, 08 June 2010 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here something close:
SQL> WITH 
  2    data AS (
  3      SELECT rn rn1, row_number() over (partition by rn order by null) rn2,
  4             value(x) md
  5      FROM (SELECT rownum rn, MD FROM T_DB_MD_XML) t, 
  6           TABLE(XMLSequence(extract(MD,'//ROW/*/*'))) x
  7      WHERE regexp_like (value(x), '<([[:alpha:]]+_)*NAME>')
  8    ),
  9    normalize AS (
 10      SELECT rn1, rn2, 
 11             xmltype(replace(regexp_replace(
 12                               replace(md, 'OWNER_', '######'), 
 13                               '[[:alpha:]]*_NAME>', 'NAME>'),
 14                             '######', 'OWNER_')) md
 15      FROM data
 16    )
 17  SELECT rn1, rn2,
 18         nvl(extractvalue(md,'//OWNER_NAME'),'SYS') obj_owner,
 19         extractvalue(md,'//NAME') obj_name
 20  FROM normalize
 21  /
       RN1        RN2 OBJ_OWNER            OBJ_NAME
---------- ---------- -------------------- --------------------
         1          1 SYS                  S_ACT_TEG_CED_D
         2          1 SYS                  MACH_APP_PROFILE
         3          1 SCOTT                BONUS
         3          2 SYS                  ENAME
         5          1 ICO                  ICN_POI_T

I let you finish to get only the first RN2 for each RN1.
I don't know how you can get "SYS IMP_FULL_DATABASE" as IMP_FULL_DATABASE is not a "NAME" but a "GRANTEE" in your data; are you sure you want it?

Regards
Michel

[Updated on: Tue, 08 June 2010 08:48]

Report message to a moderator

Re: need query to extract values from xml data (merged) [message #459983 is a reply to message #459675] Wed, 09 June 2010 05:05 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Michel,
I'm very much thankful for your response.

Actually i need that. I used hard code for that.

Regards,
Madhavi.
Re: need query to extract values from xml data (merged) [message #459985 is a reply to message #459983] Wed, 09 June 2010 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can add it in the WHERE clause at line 7 and in the "normalize" query to replace GRANTEE by NAME and make it fit the "standard".

Regards
Michel
Re: need query to extract values from xml data (merged) [message #460001 is a reply to message #459675] Wed, 09 June 2010 05:56 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Michel,

Actually the xml data is exists in pl/sql variable of xmltype. Can i use the same query to get name/grantee and owner_name from pl/sql variable?

The XML data is coming at runtime.

CREATE OR REPLACE PROCEDURE DDI.EXPORT_SCHEMA_DB1_PER1                             
--RETURN XMLTYPE
authid current_user 
AS
    V_Scm_Id NUMBER;
    V_Scm VARCHAR2(50);
    Handle NUMBER;
    V_First_Part XMLTYPE;
    V_Next_Part XMLTYPE;
    V_Output_Total XMLTYPE;
    TYPE BASE_OBJECT_TYPE IS TABLE OF VARCHAR2 (100)
    INDEX BY BINARY_INTEGER;
    Base_Objtype BASE_OBJECT_TYPE;
    V_Owner2 varchar2(50);
    V_Obj_Name2 varchar2(50);
    BEGIN
  FOR I IN (SELECT OBJ_TP_ID, OBJ_TP_NM FROM DDI_OBJ_TP_T WHERE SCM_EXP = 'Y' AND Obj_Tp_Nm = 'USER' ORDER BY OBJ_TP_ID  ) LOOP
 Handle := DBMS_METADATA.OPEN('DATABASE_EXPORT');
 DBMS_METADATA.SET_FILTER(Handle,'INCLUDE_PATH_EXPR','IN'''||I.OBJ_TP_NM||'''');
 DBMS_METADATA.SET_FILTER(Handle,'NAME_EXPR','IN''SYS''','SCHEMA');
 
 DBMS_METADATA.SET_FILTER(Handle, 'NAME_EXPR','IN(SELECT obj_nm FROM DDI_OBJ_DEF_T WHERE OBJ_TP_ID = I.OBJ_TP_ID AND SCM_EXP = ''Y'' )','SCHEMA') ;
        LOOP
            DBMS_OUTPUT.PUT_LINE('Start the loop');
            V_First_Part := DBMS_METADATA.FETCH_XML(Handle);
            EXIT WHEN V_FIRST_PART IS NULL;
            
            IF I.OBJ_TP_NM IN ('TABLESPACE','USER','ROLE') THEN 
                SELECT OWNER, Obj_Name INTO V_Owner2, V_Obj_Name2 FROM (select nvl(extractvalue(value(x), '/SCHEMA_OBJ/OWNER_NAME'),'SYS') owner,
                    extractvalue(value(x), '//NAME' )  Obj_name
                    from table(xmlsequence(extract(V_First_Part, '/ROWSET/ROW'))) x ) ;
             ELSIF I.OBJ_TP_NM IN ('PROFILE_T') THEN 
                SELECT OWNER, Obj_Name INTO V_Owner2, V_Obj_Name2 FROM (select nvl(extractvalue(value(x), '/SCHEMA_OBJ/OWNER_NAME'),'SYS') owner,
                extractvalue(value(x), '//PROFILE_NAME' )  Obj_name
                from table(xmlsequence(extract(V_First_Part, '/ROWSET/ROW'))) x ) ;
            ELSIF I.OBJ_TP_NM IN ('TABLE_T','VIEW_T','SEQUENCE_T','SYNONYM_T','PROCEDURE_T','FUNCTION_T','PACKAGE_T','PACKAGE_SPEC_T','PACKAGE_BODY_T','OPERATOR',
               'DBLINK_T','DIRECTORY_T','CONTEXT_T','LIBRARY_T','TYPE_T','TYPE_SPEC_T','TYPE_BODY_T','INDEX_T','TRIGGER_T','INDEXTYPE_T','CLUSTER_T') THEN 
                SELECT OWNER, Obj_Name INTO V_Owner2, V_Obj_Name2 FROM (select nvl(extractvalue(value(x), '/SCHEMA_OBJ/OWNER_NAME'),'SYS') owner,
                extractvalue(value(x), '/SCHEMA_OBJ/NAME') Obj_name
                from table(xmlsequence(extract(V_First_Part, '/ROWSET/ROW//SCHEMA_OBJ'))) x ) WHERE ROWNUM = 1;
            ELSIF I.OBJ_TP_NM IN ('CONSTRAINT_T') THEN 
                SELECT OWNER, Obj_Name INTO V_Owner2, V_Obj_Name2 FROM (select nvl(extractvalue(value(x), '/CONSTRAINT_T/OWNER_NAME'),'SYS') owner,
                extractvalue(value(x), '/CONSTRAINT_T/NAME[1]') Obj_name
                from table(xmlsequence(extract(V_First_Part, '/ROWSET/ROW/CONSTRAINT_T'))) x ) WHERE ROWNUM = 1;
            ELSIF I.OBJ_TP_NM IN ('COMMENT_T','OBJGRANT_T') THEN 
                SELECT OWNER, Obj_Name INTO V_Owner2, V_Obj_Name2 FROM (select nvl(extractvalue(value(x), '/BASE_OBJ/OWNER_NAME'),'SYS') owner,
                extractvalue(value(x), '//NAME') Obj_name
                from table(xmlsequence(extract(V_First_Part, '/ROWSET/ROW'))) x ) ;  
            END IF;
        
           DBMS_OUTPUT.PUT_LINE(v_Owner2);
           DBMS_OUTPUT.PUT_LINE(v_Obj_Name2);
         END LOOP;
       
    DBMS_METADATA.CLOSE(Handle); 
    
    END LOOP;  
    
END;



Regards,
Madhavi.
Re: need query to extract values from xml data (merged) [message #460011 is a reply to message #460001] Wed, 09 June 2010 06:24 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes functions are available in both SQL and PL/SQL.

Regards
Michel
Previous Topic: help with clob concatenation
Next Topic: How can i delete a node from a pl/sql variable of xmltype
Goto Forum:
  


Current Time: Thu Mar 28 13:41:57 CDT 2024