OraFAQ Forum: JDeveloper, Java & XML » Sql query for the unexpected scenario

Home » Developer & Programmer » JDeveloper, Java & XML » Sql query for the unexpected scenario

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

Sql query for the unexpected scenario [message #628012]

Wed, 19 November 2014 01:31

asbh123
Messages: 26
Registered: November 2014
Location: mumbai

Junior Member

hi all,

I have one table in that only one column having data in xmlformat and remaing normal oracle datatypes.for example column name is xml_data.I need sql to get values from that xml data output data highlighted for reference in below xml data.

Sample xmldata for one of the record is like below
<?xml version="1.0" encoding="utf-8"?>
<ns0:finalvalue xmlns :ns0="http://amagdgdg.com">
<ns0:valuableitems>
<ns0:valuesfortheitems>dsddd</ns0:valuesfortheitems>
<ns0:valuesid>d21</ns0:valuesid>
<ns0:items>
<ns0:itemid>1a>/ns0:itemid>
<ns0:itemdfg>2a>/ns0:itemdfg>
<ns0:itecart>11a>/ns0:itecart>
</ns0:itesms>
<ns0:items>
<ns0:itemid>56a>/ns0:itemid>
<ns0:itemdfg>2145a>/ns0:itemdfg>
<ns0:itecart>1361a>/ns0:itecart>
</ns0:itesms>
<ns0:items>
<ns0:itemid>1a>/iwwsb0:itemid>
<ns0:itemdfg>2a>/wens0:itemdfg>
<ns0:itecart>11a>/dns0:itecart>
</ns0:itesms>
</ns0:valuableitems>
<ns0:valuableitems>
<ns0:valuesfortheitems>qqqd</ns0:valuesfortheitems>
<ns0:valuesid>dqw21</ns0:valuesid>
<ns0:items>
<ns0:itemid>1wwa>/ns0:itemid>
<ns0:itemdfg>2qa>/ns0:itemdfg>
<ns0:itecart>1ww1a>/ns0:itecart>
</ns0:itesms>
<ns0:items>
<ns0:itemid>56wwa>/ns0:itemid>
<ns0:itemdfg>21dd45a>/ns0:itemdfg>
<ns0:itecart>13a61a>/ns0:itecart>
</ns0:itesms>
<ns0:items>
<ns0:itemid>1addqa>/iwwsb0:itemid>
<ns0:itemdfg>2wa>/wens0:itemdfg>
<ns0:itecart>1aa1a>/dns0:itecart>
</ns0:itesms>
</ns0:valuableitems>
</ns0:finalvalue>

Requiredoutput:

xml_data

dsddd
qqqd

[Edit MC: remove empty lines]

[Updated on: Wed, 19 November 2014 01:40] by Moderator

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628014 is a reply to message #628012]

Wed, 19 November 2014 01:55

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

Senior Member
Account Moderator

There are many errors in your XML string, please take care of what you post.

SQL> col value format a20
SQL> with 
  2    data as ( select 
  3  '<?xml version="1.0" encoding="utf-8"?>
  4  <ns0:finalvalue xmlns:ns0="http://amagdgdg.com">
  5  <ns0:valuableitems>
  6    <ns0:valuesfortheitems>dsddd</ns0:valuesfortheitems>
  7    <ns0:valuesid>d21</ns0:valuesid>
  8    <ns0:items>
  9       <ns0:itemid>1a</ns0:itemid>
 10       <ns0:itemdfg>2a</ns0:itemdfg>
 11       <ns0:itecart>11a</ns0:itecart>
 12    </ns0:items>
 13    <ns0:items>
 14      <ns0:itemid>56a</ns0:itemid>
 15      <ns0:itemdfg>2145a</ns0:itemdfg>
 16      <ns0:itecart>1361a</ns0:itecart>
 17    </ns0:items>
 18    <ns0:items>
 19      <ns0:itemid>1a</ns0:itemid>
 20      <ns0:itemdfg>2a</ns0:itemdfg>
 21      <ns0:itecart>11a</ns0:itecart>
 22    </ns0:items>
 23  </ns0:valuableitems>
 24  <ns0:valuableitems>
 25    <ns0:valuesfortheitems>qqqd</ns0:valuesfortheitems>
 26    <ns0:valuesid>dqw21</ns0:valuesid>
 27    <ns0:items>
 28      <ns0:itemid>1wwa</ns0:itemid>
 29      <ns0:itemdfg>2qa</ns0:itemdfg>
 30      <ns0:itecart>1ww1a</ns0:itecart>
 31    </ns0:items>
 32    <ns0:items>
 33      <ns0:itemid>56wwa</ns0:itemid>
 34      <ns0:itemdfg>21dd45a</ns0:itemdfg>
 35      <ns0:itecart>13a61a</ns0:itecart>
 36    </ns0:items>
 37    <ns0:items>
 38      <ns0:itemid>1addqa</ns0:itemid>
 39      <ns0:itemdfg>2wa</ns0:itemdfg>
 40      <ns0:itecart>1aa1a</ns0:itecart>
 41    </ns0:items>
 42  </ns0:valuableitems>
 43  </ns0:finalvalue>' val 
 44      from dual
 45    )
 46  select extractvalue(value(x), '//ns0:valuesfortheitems','xmlns:ns0="http://amagdgdg.com"') value
 47  from data, 
 48       table(xmlsequence(extract(xmltype(val), '//ns0:valuesfortheitems','xmlns:ns0="http://amagdgdg.com"'))) x
 49  /
VALUE
--------------------
dsddd
qqqd

2 rows selected.

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628016 is a reply to message #628014]

Wed, 19 November 2014 02:01

asbh123
Messages: 26
Registered: November 2014
Location: mumbai

Junior Member

Hi Michel Cadot,

I'm confused with the above code.i have placed the xml data for one record in a table column named as xml_data.every time ineed to use the every record xml data in this querry or I can simple use the below script.bcz I have nearly 15500records in table.

select extractvalue(value(x), '//ns0:valuesfortheitems','xmlns:ns0="http://amagdgdg.com"') value
   from data, 
       table(xmlsequence(extract(xmltype(val), '//ns0:valuesfortheitems','xmlns:ns0="http://amagdgdg.com"'))) x

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628017 is a reply to message #628014]

Wed, 19 November 2014 02:03

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

Senior Member
Account Moderator

If your table is named DATA and your column VAL and is of VARCHAR2/CLOB datatype, yes.

Note you will have all values but you will don't know from which records they come from as nothing identifies the record.

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628018 is a reply to message #628017]

Wed, 19 November 2014 02:07

asbh123
Messages: 26
Registered: November 2014
Location: mumbai

Junior Member

my table column datatype defined as xmldatatype for the column xml_data(this column having xmldata) and I have another column in this table called seqid it is integer datatype.

[Updated on: Wed, 19 November 2014 02:08]

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628019 is a reply to message #628018]

Wed, 19 November 2014 02:19

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

Senior Member
Account Moderator

So you don't to convert it using XMLTYPE as in my query and you have to change the names of the column and table, and to add seqid in the SELECT to identify the row.

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628021 is a reply to message #628019]

Wed, 19 November 2014 02:42

asbh123
Messages: 26
Registered: November 2014
Location: mumbai

Junior Member

Hi,
My table like below,

seqid xml_date
1 xmldata posted above is present in this column
2 similaraly some xmldata present in this column
3 similaraly some xmldata present in this column

sample Output for seqid 1
seqid xml_date

1 dsddd
1 qqqd

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628026 is a reply to message #628021]

Wed, 19 November 2014 03:11

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

Senior Member
Account Moderator

No problem, now do it, I gave you all the elements.

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628069 is a reply to message #628021]

Wed, 19 November 2014 14:10

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

Senior Member

You can also use XMLTABLE. In the example below, I used the row of data corrected by Michel Cadot and another row of similar data.

-- test data:

SCOTT@orcl12c> COLUMN xml_data FORMAT A60
SCOTT@orcl12c> SELECT * FROM your_table_name
  2  /

     SEQID XML_DATA
---------- ------------------------------------------------------------
         1 <?xml version="1.0" encoding="WINDOWS-1252"?>
           <ns0:finalvalue xmlns:ns0="http://amagdgdg.com">
             <ns0:valuableitems>
               <ns0:valuesfortheitems>dsddd</ns0:valuesfortheitems>
               <ns0:valuesid>d21</ns0:valuesid>
               <ns0:items>
                 <ns0:itemid>1a</ns0:itemid>
                 <ns0:itemdfg>2a</ns0:itemdfg>
                 <ns0:itecart>11a</ns0:itecart>
               </ns0:items>
               <ns0:items>
                 <ns0:itemid>56a</ns0:itemid>
                 <ns0:itemdfg>2145a</ns0:itemdfg>
                 <ns0:itecart>1361a</ns0:itecart>
               </ns0:items>
               <ns0:items>
                 <ns0:itemid>1a</ns0:itemid>
                 <ns0:itemdfg>2a</ns0:itemdfg>
                 <ns0:itecart>11a</ns0:itecart>
               </ns0:items>
             </ns0:valuableitems>
             <ns0:valuableitems>
               <ns0:valuesfortheitems>qqqd</ns0:valuesfortheitems>
               <ns0:valuesid>dqw21</ns0:valuesid>
               <ns0:items>
                 <ns0:itemid>1wwa</ns0:itemid>
                 <ns0:itemdfg>2qa</ns0:itemdfg>
                 <ns0:itecart>1ww1a</ns0:itecart>
               </ns0:items>
               <ns0:items>
                 <ns0:itemid>56wwa</ns0:itemid>
                 <ns0:itemdfg>21dd45a</ns0:itemdfg>
                 <ns0:itecart>13a61a</ns0:itecart>
               </ns0:items>
               <ns0:items>
                 <ns0:itemid>1addqa</ns0:itemid>
                 <ns0:itemdfg>2wa</ns0:itemdfg>
                 <ns0:itecart>1aa1a</ns0:itecart>
               </ns0:items>
             </ns0:valuableitems>
           </ns0:finalvalue>

         2 <?xml version="1.0" encoding="WINDOWS-1252"?>
           <ns0:finalvalue xmlns:ns0="http://amagdgdg.com">
             <ns0:valuableitems>
               <ns0:valuesfortheitems>DSDDD</ns0:valuesfortheitems>
             </ns0:valuableitems>
             <ns0:valuableitems>
               <ns0:valuesfortheitems>QQQD</ns0:valuesfortheitems>
             </ns0:valuableitems>
           </ns0:finalvalue>


2 rows selected.

-- query:

SCOTT@orcl12c> SELECT t.seqid,
  2  	    x.xml_data
  3  FROM   your_table_name t,
  4  	    XMLTABLE
  5  	      (XMLNAMESPACES ('http://amagdgdg.com' AS "ns0"),
  6  	       '//ns0:valuesfortheitems'
  7  	       PASSING t.xml_data
  8  	       COLUMNS
  9  		 xml_data  PATH  '//ns0:valuesfortheitems') x
 10  /

     SEQID XML_DATA
---------- ------------------------------------------------------------
         1 dsddd
         1 qqqd
         2 DSDDD
         2 QQQD

4 rows selected.

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628098 is a reply to message #628069]

Thu, 20 November 2014 03:15

asbh123
Messages: 26
Registered: November 2014
Location: mumbai

Junior Member

Hi,

When i try to execute above querry in second line at x.xml_data giving error as invalid identifier.when i replace x.xml_data line with t.xml_data it is giving result but data in xml format in columns.

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628103 is a reply to message #628098]

Thu, 20 November 2014 03:26

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

Senior Member
Account Moderator

Show us what you see, it is FAR better than telling. Don't you see we showed you?
Also post the result of

select * from v$version;

(or tell us your DB version with 4 decimals like 11.2.0.3)

[Updated on: Thu, 20 November 2014 03:27]

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628107 is a reply to message #628103]

Thu, 20 November 2014 03:49

asbh123
Messages: 26
Registered: November 2014
Location: mumbai

Junior Member

11.2.0.3.0

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628125 is a reply to message #628107]

Thu, 20 November 2014 05:03

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

Senior Member
Account Moderator

Quote:

Show us what you see, it is FAR better than telling. Don't you see we showed you?

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628176 is a reply to message #628098]

Thu, 20 November 2014 16:03

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

Senior Member

What I posted, I tested in both 11g and 12c. You must have run something slightly different, which is why you need to copy and paste a run of your query, complete with the results. If you left out something as simple as the x at the end of the last line, then that would produce the error. Please see the example below that first reproduces the error without the x, then runs correctly with the x.

-- version:

SCOTT@orcl> SELECT banner FROM v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

5 rows selected.

-- reproduction of error by removing x from last line of query:

SCOTT@orcl> SELECT t.seqid,
  2  	    x.xml_data
  3  FROM   your_table_name t,
  4  	    XMLTABLE
  5  	      (XMLNAMESPACES ('http://amagdgdg.com' AS "ns0"),
  6  	       '//ns0:valuesfortheitems'
  7  	       PASSING t.xml_data
  8  	       COLUMNS
  9  		 xml_data  PATH  '//ns0:valuesfortheitems')
 10  /
       x.xml_data
       *
ERROR at line 2:
ORA-00904: "X"."XML_DATA": invalid identifier

-- correct query with x at the end of the last line:

SCOTT@orcl> SELECT t.seqid,
  2  	    x.xml_data
  3  FROM   your_table_name t,
  4  	    XMLTABLE
  5  	      (XMLNAMESPACES ('http://amagdgdg.com' AS "ns0"),
  6  	       '//ns0:valuesfortheitems'
  7  	       PASSING t.xml_data
  8  	       COLUMNS
  9  		 xml_data  PATH  '//ns0:valuesfortheitems') x
 10  /

     SEQID XML_DATA
---------- ------------------------------------------------------------
         1 dsddd
         1 qqqd
         2 DSDDD
         2 QQQD

4 rows selected.

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628191 is a reply to message #628176]

Fri, 21 November 2014 01:37

asbh123
Messages: 26
Registered: November 2014
Location: mumbai

Junior Member

Hi,
Thanks for the code.but i dont know why the same code is givinng error for me.I applied above code for my table by changing to my table columnnames.my sid columns is similar to seqid column and valid_xml is similar to xml_data column in above script.

SELECT t.sid,
  2  	    x.valid_xml
  3  FROM   section t,
  4  	    XMLTABLE
  5  	      (XMLNAMESPACES ('http://amagdgdg.com' AS "ns0"),
  6  	       '//ns0:valuesfortheitems'
  7  	       PASSING t.valid_xml
  8  	       COLUMNS
  9  		 xml_data  PATH  '//ns0:valuesfortheitems') x
 10  /

Erroe showing again at line 2 ora-00923 from keyword not found where expected.

what is the use of XMLTABLE in the above script?

[Updated on: Fri, 21 November 2014 01:37]

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628193 is a reply to message #628191]

Fri, 21 November 2014 01:40

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

Senior Member
Account Moderator

Copy and paste the COMPLETE scenario AND post the database version with 4 decimals (for instance like Barbara did).
Use SQL*Plus.

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628194 is a reply to message #628193]

Fri, 21 November 2014 01:50

asbh123
Messages: 26
Registered: November 2014
Location: mumbai

Junior Member

databaseverion is 11.2.0.3.0.I don't have sql*plus.toad im using now.

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628195 is a reply to message #628194]

Fri, 21 November 2014 01:53

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

Senior Member
Account Moderator

This an error, install it and forget TOAD to execute (and post in forum) SQL.
As we don't know what you actually do the only thing we can say is that if you do and we did then it works.

Report message to a moderator

Re: Sql query for the unexpected scenario [message #628196 is a reply to message #628191]

Fri, 21 November 2014 01:59

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

Senior Member

If your table name is section and your column names are sid and valid_xml, then your query should be something like below. XMLTABLE converts the xml data into tabular format using the columns specified. You can find detailed information and examples in the online documentation.

SCOTT@orcl> SELECT t.sid,
  2  	    x.xml_data
  3  FROM   section t,
  4  	    XMLTABLE
  5  	      (XMLNAMESPACES ('http://amagdgdg.com' AS "ns0"),
  6  	       '//ns0:valuesfortheitems'
  7  	       PASSING t.valid_xml
  8  	       COLUMNS
  9  		 xml_data  PATH  '//ns0:valuesfortheitems') x
 10  /

       SID XML_DATA
---------- ------------------------------------------------------------
         1 dsddd
         1 qqqd
         2 DSDDD
         2 QQQD

4 rows selected.

Report message to a moderator

Re: Sql query for the unexpected scenario [message #630896 is a reply to message #628196]

Tue, 06 January 2015 03:07

asbh123
Messages: 26
Registered: November 2014
Location: mumbai

Junior Member

Hi Everyone,

Thanks for your Suggestions and scripts.Barbara i used your script works fine.

SELECT t.sid,
x.xml_data
FROM   section t,
XMLTABLE
(XMLNAMESPACES ('http://amagdgdg.com' AS "ns0"),
'//ns0:valuesfortheitems'
PASSING t.valid_xml
 COLUMNS
xml_data  PATH  '//ns0:valuesfortheitems') x
 /

Report message to a moderator

Previous Topic:	How to Read Xml Data into Oracle Database
Next Topic:	Extracting Data from Xml (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 00:47:08 CST 2025