Home » Developer & Programmer » JDeveloper, Java & XML » Sql query for the unexpected scenario
Sql query for the unexpected scenario [message #628012] Wed, 19 November 2014 01:31 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Sql query for the unexpected scenario [message #628016 is a reply to message #628014] Wed, 19 November 2014 02:01 Go to previous messageGo to next message
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
Re: Sql query for the unexpected scenario [message #628017 is a reply to message #628014] Wed, 19 November 2014 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Sql query for the unexpected scenario [message #628018 is a reply to message #628017] Wed, 19 November 2014 02:07 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Sql query for the unexpected scenario [message #628021 is a reply to message #628019] Wed, 19 November 2014 02:42 Go to previous messageGo to next message
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
Re: Sql query for the unexpected scenario [message #628026 is a reply to message #628021] Wed, 19 November 2014 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Sql query for the unexpected scenario [message #628069 is a reply to message #628021] Wed, 19 November 2014 14:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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.

Re: Sql query for the unexpected scenario [message #628098 is a reply to message #628069] Thu, 20 November 2014 03:15 Go to previous messageGo to next message
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.
Re: Sql query for the unexpected scenario [message #628103 is a reply to message #628098] Thu, 20 November 2014 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
asbh123
Messages: 26
Registered: November 2014
Location: mumbai
Junior Member
11.2.0.3.0
Re: Sql query for the unexpected scenario [message #628125 is a reply to message #628107] Thu, 20 November 2014 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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


Re: Sql query for the unexpected scenario [message #628176 is a reply to message #628098] Thu, 20 November 2014 16:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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.

Re: Sql query for the unexpected scenario [message #628191 is a reply to message #628176] Fri, 21 November 2014 01:37 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
Registered: March 2007
Location: Nanterre, France, http://...
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.
Re: Sql query for the unexpected scenario [message #628194 is a reply to message #628193] Fri, 21 November 2014 01:50 Go to previous messageGo to next message
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.
Re: Sql query for the unexpected scenario [message #628195 is a reply to message #628194] Fri, 21 November 2014 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Sql query for the unexpected scenario [message #628196 is a reply to message #628191] Fri, 21 November 2014 01:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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.


Re: Sql query for the unexpected scenario [message #630896 is a reply to message #628196] Tue, 06 January 2015 03:07 Go to previous message
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
 /
Previous Topic: How to Read Xml Data into Oracle Database
Next Topic: Extracting Data from Xml (merged)
Goto Forum:
  


Current Time: Tue May 18 03:29:09 CDT 2021