OraFAQ Forum: JDeveloper, Java & XML » How to get the Single Tag out of XML

Home » Developer & Programmer » JDeveloper, Java & XML » How to get the Single Tag out of XML (Oracle 10.2.0.4.0 , Toad)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to get the Single Tag out of XML [message #515934]

Thu, 14 July 2011 04:35

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Hi All,

We have the following XML info.

<connection value="0">
<GroupDetails>
   <info groupId="100" classType="0"/>
   <info groupId="128" classType="1"/>
   <info groupId="179" classType="0"/>
   <info groupId="198" classType="0"/>
</GroupDetails> 
</connection>

We need to get the single node with class type ="1" from this XML as given below.At any point of time, there will be only one such tag with classType="1". Its position could be anywhere inside the "GroupDetails" Tag.ie, The query should get the tag irrespective of its position inside "GroupDetails".

<info groupId="128" classType="1"/>

Is there any way in straight SQL? Is there any way using EXTRACT function with 3 parameter? I tried but with failure.
Following piece of code could be helpful to get a start.

with rec AS (  
select XMLTYPE(
'<connection value="0">
<GroupDetails>
   <info groupId="100" classType="0"/>
   <info groupId="128" classType="1"/>
   <info groupId="179" classType="0"/>
   <info groupId="198" classType="0"/>
</GroupDetails> 
</connection>') x
from dual) 
select * from rec

Report message to a moderator

Re: How to get the Single Tag out of XML [message #515951 is a reply to message #515934]

Thu, 14 July 2011 05:25

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> with rec AS (  
  2  select XMLTYPE(
  3  '<connection value="0">
  4  <GroupDetails>
  5     <info groupId="100" classType="0"/>
  6     <info groupId="128" classType="1"/>
  7     <info groupId="179" classType="0"/>
  8     <info groupId="198" classType="0"/>
  9  </GroupDetails> 
 10  </connection>') val
 11  from dual) 
 12  select column_value
 13  from rec,
 14       table(xmlsequence(extract(val, '//info'))) x
 15  where extractvalue(value(x), '//info/@classType') = 1
 16  /
COLUMN_VALUE
-------------------------------------------------------------
<info groupId="128" classType="1"/>

Regards
Michel

Report message to a moderator

Re: How to get the Single Tag out of XML [message #515965 is a reply to message #515951]

Thu, 14 July 2011 05:44

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Thanks Michel.. Thanks a ton..

Basically the Issue is, we have the XML as follows.

<connection value="0">
<GroupDetails>
   <info groupId="100" classType="0"/>
   <info groupId="128" classType="1"/>
   <info groupId="179" classType="0"/>
   <info groupId="198" classType="0"/>
</GroupDetails> 
</connection>

And the actual need os to make the tag with classType="1" as the first one inside "GroupDetails" as below.At any point of time, there will be only one such tag with classType="1". Its position could be anywhere inside the "GroupDetails" Tag.

<connection value="0">
<GroupDetails>
   <info groupId="128" classType="1"/>
   <info groupId="100" classType="0"/> 
   <info groupId="179" classType="0"/>
   <info groupId="198" classType="0"/>
</GroupDetails> 
</connection>

We are planning to the following.
1. Delete the tag with classType="1" first (using DELETEXML)
2. Insert the same tag again with INSERBEFOREXML function.

Is there a better approach ?

Report message to a moderator

Re: How to get the Single Tag out of XML [message #515971 is a reply to message #515965]

Thu, 14 July 2011 06:07

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

No...
Moreover Why you are worrying about position ?Michel`s query work for your requirement.

16:30:37 ind> with rec AS (  
16:30:40   2    select XMLTYPE(
16:30:40   3    '<connection value="0">
16:30:40   4    <GroupDetails>
16:30:40   5       <info groupId="100" classType="0"/>
16:30:40   6       <info groupId="128" classType="1"/>
16:30:40   7       <info groupId="179" classType="0"/>
16:30:40   8       <info groupId="198" classType="0"/>
16:30:40   9    </GroupDetails> 
16:30:40  10    </connection>') val
16:30:40  11    from dual) 
16:30:40  12    select column_value
16:30:40  13    from rec,
16:30:40  14         table(xmlsequence(extract(val, '//info'))) x
16:30:40  15    where extractvalue(value(x), '//info/@classType') = 1
16:30:40  16    /

COLUMN_VALUE
----------------------------------------------------------------------------------------------------
<info groupId="128" classType="1"/>

1 row selected.

Elapsed: 00:00:01.07
16:30:42 ind> ed
Wrote file afiedt.buf

  1  with rec AS (
  2    select XMLTYPE(
  3    '<connection value="0">
  4    <GroupDetails>
  5       <info groupId="128" classType="1"/>
  6       <info groupId="100" classType="0"/>
  7       <info groupId="179" classType="0"/>
  8       <info groupId="198" classType="0"/>
  9    </GroupDetails>
 10    </connection>') val
 11    from dual)
 12    select column_value
 13    from rec,
 14         table(xmlsequence(extract(val, '//info'))) x
 15*   where extractvalue(value(x), '//info/@classType') = 1
16:31:02 ind> /

COLUMN_VALUE
----------------------------------------------------------------------------------------------------
<info groupId="128" classType="1"/>

1 row selected.

Elapsed: 00:00:00.17
16:31:04 ind>

sriram

Report message to a moderator

Re: How to get the Single Tag out of XML [message #515986 is a reply to message #515971]

Thu, 14 July 2011 06:36

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

yes. Michel's query is working perfectly..

What I was trying to say is, The initial post was a part of An Actual requirement. For Iniital post , Michel's query works perfectly.

But my Actual requirement was to move the tag with classType="1" to the First position using SQL inside an XML. So we were trying to delete the tag with classType="1" and insert again with INSERTXMLBEFORE Function. What I was asking. Instead of these two sql, are there any alternatives ?

Report message to a moderator

Re: How to get the Single Tag out of XML [message #516052 is a reply to message #515986]

Thu, 14 July 2011 15:07

Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl_11gR2> create table rec
  2    (x  xmltype)
  3  /

Table created.

SCOTT@orcl_11gR2> insert into rec values (xmltype (
  2  '<connection value="0">
  3  	<GroupDetails>
  4  	  <info groupId="100" classType="0"/>
  5  	  <info groupId="128" classType="1"/>
  6  	  <info groupId="179" classType="0"/>
  7  	  <info groupId="198" classType="0"/>
  8  	</GroupDetails>
  9   </connection>')
 10  )
 11  /

1 row created.

SCOTT@orcl_11gR2> select * from rec
  2  /

X
--------------------------------------------------------------------------------
<connection value="0">
  <GroupDetails>
    <info groupId="100" classType="0"/>
    <info groupId="128" classType="1"/>
    <info groupId="179" classType="0"/>
    <info groupId="198" classType="0"/>
  </GroupDetails>
</connection>


1 row selected.

SCOTT@orcl_11gR2> update rec r
  2  set    r.x =
  3  	      insertXMLBefore
  4  		(deleteXML (r.x, '//info[@classType="1"]'),
  5  		 '//info[1]',
  6  		 XMLQuery
  7  		   ('$rx//info[@classType="1"]'
  8  		     passing r.x as "rx"
  9  		     returning content))
 10  /

1 row updated.

SCOTT@orcl_11gR2> select * from rec
  2  /

X
--------------------------------------------------------------------------------
<connection value="0">
  <GroupDetails>
    <info groupId="128" classType="1"/>
    <info groupId="100" classType="0"/>
    <info groupId="179" classType="0"/>
    <info groupId="198" classType="0"/>
  </GroupDetails>
</connection>


1 row selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: How to get the Single Tag out of XML [message #516812 is a reply to message #516052]

Thu, 21 July 2011 01:14

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Elegantly excellent Barbara.. You are rocking .. Btw, I was using the following query. You suggestion is much better than the current one Smile

11:41:35 SQL> with rec AS (
select XMLTYPE(
11:41:36   2  11:41:36   3  '<connection value="0">
11:41:36   4  <GroupDetails>
11:41:36   5     <info groupId="100" classType="0"/>
11:41:36   6     <info groupId="128" classType="1"/>
11:41:36   7     <info groupId="179" classType="0"/>
11:41:36   8     <info groupId="198" classType="0"/>
11:41:36   9  </GroupDetails>
11:41:36  10  </connection>') x
11:41:36  11  from dual)
11:41:36  12  select  INSERTXMLBEFORE(DELETEXML(x,'connection/GroupDetails/info[@classType="1"]'),
11:41:36  13          'connection/GroupDetails/info[1]',value(y))
11:41:36  14  from rec ,
11:41:36  15      table(xmlsequence(extract(x, '/connection/GroupDetails/info'))) y
11:41:36  16      where extractvalue(value(y), '//info/@classType') = 1 ;

INSERTXMLBEFORE(DELETEXML(X,'CONNECTION/GROUPDETAILS/INFO[@CLASSTYPE="1"]'),'CON
--------------------------------------------------------------------------------
<connection value="0">
  <GroupDetails>
    <info groupId="128" classType="1"/>
    <info groupId="100" classType="0"/>
    <info groupId="179" classType="0"/>
    <info groupId="198" classType="0"/>
  </GroupDetails>
</connection>


11:41:37 SQL>

Report message to a moderator

Re: How to get the Single Tag out of XML [message #516856 is a reply to message #516812]

Thu, 21 July 2011 03:37

_jum
Messages: 577
Registered: February 2008

Senior Member

This can be simplified to:

WITH rec AS
 (SELECT xmltype ('<connection value="0">
  <GroupDetails>
    <info groupId="100" classType="0"/>
    <info groupId="128" classType="1"/>
    <info groupId="179" classType="0"/>
    <info groupId="198" classType="0"/>
  </GroupDetails>
 </connection>') x FROM DUAL)
   SELECT
     INSERTXMLBEFORE(DELETEXML(x,'connection/GroupDetails/info[@classType="1"]'),
       'connection/GroupDetails/info[1]', EXTRACT (x,'//info[@classType=1]')) nx
   FROM rec;

NX                                                                              
--------------------------------------------
<connection value="0">                                                          
  <GroupDetails>                                                                
    <info groupId="128" classType="1"/>                                         
    <info groupId="100" classType="0"/>                                         
    <info groupId="179" classType="0"/>                                         
    <info groupId="198" classType="0"/>                                         
  </GroupDetails>                                                               
</connection>

Report message to a moderator

Re: How to get the Single Tag out of XML [message #516862 is a reply to message #516856]

Thu, 21 July 2011 03:52

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the feedback, it will be helpful.
Allow me to reformat it:

WITH rec AS
 (SELECT xmltype ('<connection value="0">
  <GroupDetails>
    <info groupId="100" classType="0"/>
    <info groupId="128" classType="1"/>
    <info groupId="179" classType="0"/>
    <info groupId="198" classType="0"/>
  </GroupDetails>
 </connection>') x FROM DUAL)
SELECT insertXMLBefore (
         deleteXML (x, 'connection/GroupDetails/info[@classType="1"]'),
         'connection/GroupDetails/info[1]', 
         Extract (x, '//info[@classType=1]')
       ) nx
FROM rec;

Regards
Michel

Report message to a moderator

Re: How to get the Single Tag out of XML [message #517037 is a reply to message #516862]

Fri, 22 July 2011 00:16

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Thanks again for the replies. Lemme shorten the query once again Smile

WITH rec AS
 (SELECT xmltype ('<connection value="0">
  <GroupDetails>
    <info groupId="100" classType="0"/>
    <info groupId="128" classType="1"/>
    <info groupId="179" classType="0"/>
    <info groupId="198" classType="0"/>
  </GroupDetails>
 </connection>') x FROM DUAL)
SELECT insertXMLBefore (
         deleteXML (x, '//info[@classType="1"]'),
         '//info[1]', 
         Extract (x, '//info[@classType=1]')
       ) nx
FROM rec;

Report message to a moderator

Re: How to get the Single Tag out of XML [message #517040 is a reply to message #517037]

Fri, 22 July 2011 00:45

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

And this is for the reference of prospect readers.

Suppose, the XML format is as follows.

<connection value="0">
    <GroupDetails>
        <info><groupId>100</groupId>
              <classType>0</classType>
        </info>
        <info><groupId>128</groupId>
            <classType>1</classType>
        </info>  
        <info><groupId>179</groupId>
              <classType>0</classType>
        </info>
        <info><groupId>198</groupId>
              <classType>0</classType>
        </info>
    </GroupDetails>
</connection>

And it needs to be formatted in such a way that subtags with classtype="1" to appear in the first position as below.

<connection value="0">
    <GroupDetails>
        <info><groupId>128</groupId>
            <classType>1</classType>
        </info>
        <info><groupId>100</groupId>
              <classType>0</classType>
        </info>
        <info><groupId>179</groupId>
              <classType>0</classType>
        </info>
        <info><groupId>198</groupId>
              <classType>0</classType>
        </info>
    </GroupDetails>
</connection>

Then, the SQL query should be modified as below.

WITH rec AS
 (SELECT xmltype ('<connection value="0">
  <GroupDetails>
    <info> <groupId>100</groupId>
           <classType>0</classType>
    </info>
    <info> <groupId>128</groupId>
           <classType>1</classType>
    </info>
    <info> <groupId>179</groupId>
           <classType>0</classType>
    </info>
    <info> <groupId>198</groupId>
           <classType>0</classType>
     </info> 
  </GroupDetails>
 </connection>') x FROM DUAL)
SELECT  insertXMLBefore (
         deleteXML (x, '//info[classType="1"]'),
         '//info[1]', 
         Extract (x, '//info[classType=1]')
       )
FROM rec;

Report message to a moderator

Previous Topic:	XML Dom Could not convert from encoding UTF-8 to UCS2
Next Topic:	regarding grants in oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 01:16:46 CST 2025