Home » SQL & PL/SQL » SQL & PL/SQL » Sql to get the value from a string (ORACLE 10.2.0.3)
Sql to get the value from a string [message #438656] Mon, 11 January 2010 07:54 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
create table t300 as
select
1 a, '<ObjectXML><test>
		<ID>1</ID>
		<Startdt>2009-12-01 10:58:52</Startdt>
		<enddt>2009-12-01 10:59:49</enddt>
		<maxcount>63</macount>
		<mincount>1</mincount>
		<totalCount>65</totalnCount>
</test><Msg></Msg></ObjectXML>' b from dual
union all
select 2 , '<ObjectXML><test>
		<ID>2</ID>
		<Startdt>2009-12-01 10:59:52</Startdt>
		<enddt>2009-12-01 10:61:49</enddt>
		<maxcount>62</macount>
		<mincount>1</mincount>
		<totalCount>68</totalnCount>
</test><Msg></Msg></ObjectXML>' from dual;

How I would get the Total count value =68 from the table?
Is there any method other than INSTR,SUBSTR?
the value will be numeric. The max total count value can be 5000
Re: Sql to get the value from a string [message #438659 is a reply to message #438656] Mon, 11 January 2010 08:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Extract or ExtractValue
Re: Sql to get the value from a string [message #438660 is a reply to message #438659] Mon, 11 January 2010 08:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
ahh - I'm too quick for my own good.
EXTRACT would work if your data was stored in an XMLTYPE column - but in your test case, it's stored in a varchar2, and it's nothing like valid XML.

Granted, much of that could be typos, so if your data is meant to be XML tehn EXTRACT is the way you want to go.
Re: Sql to get the value from a string [message #438661 is a reply to message #438659] Mon, 11 January 2010 08:18 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Can we do it to extract the value from the column?

What is Warehouse in '/Warehouse/Docks' is it a node?


the column is a varchar2(4000)

[Updated on: Mon, 11 January 2010 08:19]

Report message to a moderator

Re: Sql to get the value from a string [message #438662 is a reply to message #438661] Mon, 11 January 2010 08:46 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
First of all, You have to correct Your XML (assuming it should be XML), so that it is WELL FORMED (typos).
I did it for You, used EXTRACTVALUE and get:
WITH xmlnt AS
(
SELECT 1 a, XMLTYPE('
    <ObjectXML>
      <test>
        <ID>1</ID>
        <Startdt>2009-12-01 10:58:52</Startdt>
        <enddt>2009-12-01 10:59:49</enddt>
        <maxcount>63</maxcount>
        <mincount>1</mincount>
        <totalCount>65</totalCount>
     </test>
     <Msg></Msg>
    </ObjectXML>') b FROM dual
UNION ALL
SELECT 2 , XMLTYPE('
    <ObjectXML>
      <test>
        <ID>2</ID>
        <Startdt>2009-12-01 10:59:52</Startdt>
        <enddt>2009-12-01 10:61:49</enddt>
        <maxcount>62</maxcount>
        <mincount>1</mincount>
        <totalCount>68</totalCount>
      </test>
      <Msg></Msg>
    </ObjectXML>') FROM dual)
SELECT a, extractvalue(b,'//totalCount') tcnt FROM xmlnt;

A	TCNT
---------------
1	65
2	68

[Updated on: Mon, 11 January 2010 08:55]

Report message to a moderator

Re: Sql to get the value from a string [message #438666 is a reply to message #438662] Mon, 11 January 2010 09:32 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
is there any function that we can use to convert the varchar2 to xmltype and then use extractvalue function?

In pl/sql we can derfine a variable sys.xmltype I think
Re: Sql to get the value from a string [message #438667 is a reply to message #438666] Mon, 11 January 2010 09:40 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>is there any function that we can use to convert the varchar2 to xmltype and then use extractvalue function?
What is gained by converting string to XML & then using EXTRACTVALUE, as opposed to just obtaining results directly from original string?
Re: Sql to get the value from a string [message #438668 is a reply to message #438666] Mon, 11 January 2010 09:53 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
You can use the constructor XMLTYPE as in my example:

SELECT XMLTYPE('
  <ObjectXML>
    <test>
      <ID>1</ID>
      <Startdt>2009-12-01 10:58:52</Startdt>
      <enddt>2009-12-01 10:59:49</enddt>
      <maxcount>63</maxcount>
      <mincount>1</mincount>
      <totalCount>65</totalCount>
    </test>
    <Msg></Msg>
  </ObjectXML>') FROM dual
Re: Sql to get the value from a string [message #438669 is a reply to message #438666] Mon, 11 January 2010 09:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
prachij593 wrote on Mon, 11 January 2010 16:32
is there any function that we can use to convert the varchar2 to xmltype and then use extractvalue function?

In pl/sql we can derfine a variable sys.xmltype I think

You respond to _jum's message, where this functionality is very clearly displayed. You should really put more effort in reading and trying to understand the answers provided.
Re: Sql to get the value from a string [message #438809 is a reply to message #438669] Tue, 12 January 2010 06:41 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I want to copy the data from VARCHAR2 to XMLTYPE column. The column of varchar2 has the data in xml format.

I tried this way
INSERT INTO TEST2(XML_DATA) 
SELECT XMLTYPE(A) FROM TEST1;

But when I do select * from TEST2 its showing as <cursor>

A is a varchar2 in TEST1
XML_DATA is XMLTYPE


Thanks you!

[Updated on: Tue, 12 January 2010 06:46]

Report message to a moderator

Re: Sql to get the value from a string [message #438810 is a reply to message #438809] Tue, 12 January 2010 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And did you try the XMLTYPE function?

Regards
Michel
Re: Sql to get the value from a string [message #438811 is a reply to message #438810] Tue, 12 January 2010 06:47 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Yes,

INSERT INTO TEST2(XML_DATA) 
SELECT XMLTYPE(A) FROM TEST1;
Re: Sql to get the value from a string [message #438813 is a reply to message #438811] Tue, 12 January 2010 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, I saw you updated your previous post when I was writting mine.
Thanks to have reposted it with code tags.

Regards
Michel

[Updated on: Tue, 12 January 2010 06:50]

Report message to a moderator

Re: Sql to get the value from a string [message #438816 is a reply to message #438813] Tue, 12 January 2010 06:53 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Is there a way?
Re: Sql to get the value from a string [message #438817 is a reply to message #438816] Tue, 12 January 2010 07:06 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes, there is a way. It is already posted three times.
Re: Sql to get the value from a string [message #438819 is a reply to message #438816] Tue, 12 January 2010 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
prachij593 wrote on Tue, 12 January 2010 13:53
Is there a way?

A way to what?
To do what you posted? Just try it.

Regards
Michel

Re: Sql to get the value from a string [message #438820 is a reply to message #438817] Tue, 12 January 2010 07:10 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
But I need to copy the content from source to destination table
using INSERT

The destination table have the column of XMLTYPE
while source table has column of varchar2 type
Re: Sql to get the value from a string [message #438821 is a reply to message #438820] Tue, 12 January 2010 07:13 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then this

INSERT INTO TEST2(XML_DATA) 
SELECT XMLTYPE(A) FROM TEST1;


will work.

If it doesn't then post the complete SQL*Plus session with any errors you get.
Re: Sql to get the value from a string [message #438822 is a reply to message #438821] Tue, 12 January 2010 07:20 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
yes, it worked
Actually I was trying to view the data using select *
and it was displaying as <cursor>.

I forgot that I need to use function to see the content in destination table.

Thank you!
Re: Sql to get the value from a string [message #438823 is a reply to message #438820] Tue, 12 January 2010 07:21 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
In Your first post the XML - String was in COLUMN b not a Cool
-----
Too late !

[Updated on: Tue, 12 January 2010 07:22]

Report message to a moderator

Re: Sql to get the value from a string [message #438837 is a reply to message #438822] Tue, 12 January 2010 09:05 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
yes, you need to use getstringval()to display the content


SELECT e.poDoc.getstringval() AS newpo
   FROM po_xml_tab e;

NEWPO
--------------------------------------------------------------------
<?xml version="1.0"?>
<PO pono="2">
   <PNAME>Po_2</PNAME>
   <CUSTNAME>John</CUSTNAME>
   <SHIPADDR>
      <STREET>2 Avocet Drive</STREET>
      <CITY>Redwood Shores</CITY>
      <STATE>CA</STATE>
   </SHIPADDR>
</PO>


Previous Topic: upload a text file in oracle table
Next Topic: Select Random Rows
Goto Forum:
  


Current Time: Sun Dec 04 04:24:22 CST 2016

Total time taken to generate the page: 0.10760 seconds