Home » Developer & Programmer » JDeveloper, Java & XML » How to format output of query for coulmn type XMLTYPE (11.2.0.3.0)
How to format output of query for coulmn type XMLTYPE [message #614262] Tue, 20 May 2014 03:27 Go to next message
ledo60
Messages: 63
Registered: May 2014
Member
Hi,

How to format output of query for coulmn type XMLTYPE

Am trying this
select dbms_lob.substr (coulmn name,2000,1) from table name;

but got the below error.

ERROR at line 1:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'SUBSTR'





Any advice please .
Re: How to format output of query for coulmn type XMLTYPE [message #614263 is a reply to message #614262] Tue, 20 May 2014 03:46 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
You can directly use SUBSTR. No need for dbms_lob.
Re: How to format output of query for coulmn type XMLTYPE [message #614277 is a reply to message #614262] Tue, 20 May 2014 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If your data is of XMLTYPEon it, it is meaningless to use SUBSTR on it (like to use SUBSTR on a number). You use XML functions to access part of the XML data.

However, if you really to do such thing, you can do it getting the string associated to the XML data:
SQL> select dbms_lob.substr(xmltype('<michel></michel>'),6,2) from dual;
select dbms_lob.substr(xmltype('<michel></michel>'),6,2) from dual
       *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'SUBSTR'


SQL> select dbms_lob.substr(xmltype('<michel></michel>').getclobval(),6,2) from dual;
DBMS_LOB.SUBSTR(XMLTYPE('<MICHEL></MICHEL>').GETCLOBVAL(),6,2)
--------------------------------------------------------------------------------------
michel


You can also directly used SUBSTR which will implicitly convert your XMLTYPE into a CLOB:
SQL> select substr(xmltype('<michel></michel>'),2,6) from dual;
SUBSTR
------
michel

(But it is better to know what you're doing. Smile )

Re: How to format output of query for coulmn type XMLTYPE [message #614279 is a reply to message #614277] Tue, 20 May 2014 04:47 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
Thank you , but from where should i got entry like michel></michel
Re: How to format output of query for coulmn type XMLTYPE [message #614280 is a reply to message #614279] Tue, 20 May 2014 04:50 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
Lalit Kumar
when used it retrive the same data in xml and huge voulmn,
my aim to have a readable data in text formal
Re: How to format output of query for coulmn type XMLTYPE [message #614281 is a reply to message #614279] Tue, 20 May 2014 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sorry I don't understand the question.
Do you mean "how to you extract the node "michel"? Or how do you get the value inside the node "michel"? Or something else?

Re: How to format output of query for coulmn type XMLTYPE [message #614283 is a reply to message #614280] Tue, 20 May 2014 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If the purpose is to have a readable format to display, you can use something like:
SQL> with
  2    data as (
  3      select xmltype('<parameters>
  4  <parameter name="result"><value>success</value></parameter>
  5  <parameter name="showBirthday"><value>false</value></parameter>
  6  <parameter name="_wrCommand"><value>clearCacheBefore</value></parameter>
  7  </parameters>') val
  8      from  dual
  9    )
 10  select value(x).extract('/parameters') res
 11  from data, table(xmlsequence(extract(val, '/parameters'))) x
 12  /
RES
--------------------------------------------------------------------------------
<parameters><parameter name="result"><value>success</value></parameter><paramete
r name="showBirthday"><value>false</value></parameter><parameter name="_wrComman
d"><value>clearCacheBefore</value></parameter></parameters>

SQL> with
  2    data as (
  3      select xmltype('<parameters>
  4  <parameter name="result"><value>success</value></parameter>
  5  <parameter name="showBirthday"><value>false</value></parameter>
  6  <parameter name="_wrCommand"><value>clearCacheBefore</value></parameter>
  7  </parameters>') val
  8      from  dual
  9    )
 10  select xmlserialize(document extract(value(x),'/parameters') indent size = 2) res
 11  from data, table(xmlsequence(extract(val, '/parameters'))) x
 12  /
RES
--------------------------------------------------------------------------------
<parameters>
  <parameter name="result">
    <value>success</value>
  </parameter>
  <parameter name="showBirthday">
    <value>false</value>
  </parameter>
  <parameter name="_wrCommand">
    <value>clearCacheBefore</value>
  </parameter>
</parameters>

[Updated on: Tue, 20 May 2014 04:59]

Report message to a moderator

Re: How to format output of query for coulmn type XMLTYPE [message #614284 is a reply to message #614283] Tue, 20 May 2014 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or simpler:
SQL> with
  2    data as (
  3      select xmltype('<parameters><parameter name="result"><value>success</value>
  4  </parameter><parameter name="showBirthday"><value>false</value>
  5  </parameter><parameter name="_wrCommand"><value>clearCacheBefore</value>
  6  </parameter></parameters>') val
  7      from  dual
  8    )
  9  select xmlserialize(document val indent size = 2) res
 10  from data
 11  /
RES
--------------------------------------------------------------------------------
<parameters>
  <parameter name="result">
    <value>success</value>
  </parameter>
  <parameter name="showBirthday">
    <value>false</value>
  </parameter>
  <parameter name="_wrCommand">
    <value>clearCacheBefore</value>
  </parameter>
</parameters>

[Updated on: Tue, 20 May 2014 05:06]

Report message to a moderator

Re: How to format output of query for coulmn type XMLTYPE [message #614289 is a reply to message #614284] Tue, 20 May 2014 05:33 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
Yes , i need the output cllear and readable out of xml in text to be imported in another system ..
Thanks alot for your help ,
Re: How to format output of query for coulmn type XMLTYPE [message #614290 is a reply to message #614277] Tue, 20 May 2014 05:37 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Tue, 20 May 2014 15:09

You can also directly used SUBSTR which will implicitly convert your XMLTYPE into a CLOB


Using xmlserialize as clob would convert the content to a clob. Then substr could be directly used without implicit clob conversion.
Re: How to format output of query for coulmn type XMLTYPE [message #614291 is a reply to message #614290] Tue, 20 May 2014 06:03 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
Lalit,
can u assist in details please
Re: How to format output of query for coulmn type XMLTYPE [message #614292 is a reply to message #614291] Tue, 20 May 2014 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Don't you see my examples above?

Re: How to format output of query for coulmn type XMLTYPE [message #614293 is a reply to message #614291] Tue, 20 May 2014 06:15 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
First you need to explain why you want to use substr? If you just want to format your xml output, then xmlserialize would suffice, as Michel has already demonstrated.
Re: How to format output of query for coulmn type XMLTYPE [message #614294 is a reply to message #614293] Tue, 20 May 2014 06:29 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
No need to substr, my target is format your xml output .
yes i saw Michel post but how can i deal with and write my code?
SQL> with
2 data as (
3 select xmltype('<parameters><parameter name="result"><value>success</value>
4 </parameter><parameter name="showBirthday"><value>false</value>
5 </parameter><parameter name="_wrCommand"><value>clearCacheBefore</value>
6 </parameter></parameters>') val
7 from dual
8 )
9 select xmlserialize(document val indent size = 2) res
10 from data
11 /
Re: How to format output of query for coulmn type XMLTYPE [message #614295 is a reply to message #614294] Tue, 20 May 2014 06:41 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
ledo60 wrote on Tue, 20 May 2014 16:59
how can i deal with and write my code?


All you need to do is replace "val" with the column name and select it from required table_name.
select xmlserialize(document column_name indent size = 2) res from table_name
Re: How to format output of query for coulmn type XMLTYPE [message #614304 is a reply to message #614295] Tue, 20 May 2014 08:04 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
Thank you alot ,what is syntax here?

select xmlserialize(document column_name indent size = 2) res from table_name
as i got this error
ERROR at line 1:
ORA-00907: missing right parenthesis

[Updated on: Tue, 20 May 2014 08:04]

Report message to a moderator

Re: How to format output of query for coulmn type XMLTYPE [message #614306 is a reply to message #614304] Tue, 20 May 2014 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Always copy and paste what you do and get, we can't see it
2/ Change column_name to YOUR column name and table_name to YOUR table name.
3/ Pick up the documentation at read about xmlserialize, we can't do it for you.

Re: How to format output of query for coulmn type XMLTYPE [message #614307 is a reply to message #614304] Tue, 20 May 2014 08:53 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
ledo60 wrote on Tue, 20 May 2014 18:34
what is syntax here?

select xmlserialize(document column_name indent size = 2) res from table_name
as i got this error
ERROR at line 1:
ORA-00907: missing right parenthesis


And it was already told to you,

Lalit Kumar B wrote on Tue, 20 May 2014 17:11

All you need to do is replace "val" with the column name and select it from required table_name.


Column_name and table_name are not the keywords of the syntax. If you still fail to do so, then follow the steps Michel suggested in above comment.
Re: How to format output of query for coulmn type XMLTYPE [message #614348 is a reply to message #614307] Wed, 21 May 2014 02:13 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
Hi ,Lalit
there is a misunderstanding here ,of course am not speaking about Column_name and table_name , i mean indent size = 2) res
and the error raised for me ORA-00907: missing right parenthesis
Thank you
Re: How to format output of query for coulmn type XMLTYPE [message #614350 is a reply to message #614348] Wed, 21 May 2014 02:18 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
Hi Michel Cadot and Lalit Kumar B
In addition i can see this example in the internet
SELECT XMLSERIALIZE(CONTENT XMLTYPE('<Owner>Grandco</Owner>'))
FROM DUAL;


HOW can i get these values ('<Owner>Grandco</Owner>'))

Re: How to format output of query for coulmn type XMLTYPE [message #614351 is a reply to message #614350] Wed, 21 May 2014 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't understand which value you want.
SQL> SELECT XMLSERIALIZE(CONTENT XMLTYPE('<Owner>Grandco</Owner>'))
  2  FROM DUAL;
XMLSERIALIZE(CONTENTXMLTYPE('<OWNER>GRANDCO</OWNER>'))
--------------------------------------------------------------------
<Owner>Grandco</Owner>

Is this not that?
Re: How to format output of query for coulmn type XMLTYPE [message #614352 is a reply to message #614348] Wed, 21 May 2014 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
the error raised for me ORA-00907: missing right parenthesis


Use SQL*Plus and copy and paste what you do and get as I did it.
Before, Please read How to use [code] tags and make your code easier to read.

Re: How to format output of query for coulmn type XMLTYPE [message #614358 is a reply to message #614352] Wed, 21 May 2014 02:51 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
Ok , i will clear ,
when i put my coulmn name and table name i got the below error
select xmlserialize(document my coulmn name indent size = 2) res from my table_name
 
 ERROR at line 1:
 ORA-00907: missing right parenthesis


so what is the problem here

[Updated on: Wed, 21 May 2014 02:53]

Report message to a moderator

Re: How to format output of query for coulmn type XMLTYPE [message #614360 is a reply to message #614358] Wed, 21 May 2014 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"my coulmn name" and "my table_name" are invalid syntax.

Re: How to format output of query for coulmn type XMLTYPE [message #614368 is a reply to message #614360] Wed, 21 May 2014 03:49 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
Dear Am replacing with the name of my obejects of course.
this is not the problem it's on the ORA-00907: missing right parenthesis

[Updated on: Wed, 21 May 2014 03:50]

Report message to a moderator

Re: How to format output of query for coulmn type XMLTYPE [message #614370 is a reply to message #614368] Wed, 21 May 2014 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So you want we debug a code we don't see. Do you think it is wise?
Once again, copy and paste what you do and get.
For the moment you are just wasting our time.

Re: How to format output of query for coulmn type XMLTYPE [message #614381 is a reply to message #614370] Wed, 21 May 2014 04:30 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
Thank you , it's working now but the output like this not clear text

RES
--------------------------------------------------------------------------------
<row id="******" xml:space="preserve"><c1>*****</c1><c2>*******



Note:am hashing the data in addition the last coulmn in above results is truncated
Re: How to format output of query for coulmn type XMLTYPE [message #614383 is a reply to message #614381] Wed, 21 May 2014 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You still doesn't show what command you use and no more the actual result.
It is obvious you didn't use what you showed you.
You still waste our time.

Quote:
in addition the last coulmn in above results is truncated


Investigate "set long" command.


Re: How to format output of query for coulmn type XMLTYPE [message #614388 is a reply to message #614383] Wed, 21 May 2014 04:44 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
Dear I alread said , it's working which refere to my above post regarding

select xmlserialize(document  coulmn_name  indent size = 2) res from  table_name
Re: How to format output of query for coulmn type XMLTYPE [message #614390 is a reply to message #614388] Wed, 21 May 2014 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I give up.
Good luck with your mystery.

Re: How to format output of query for coulmn type XMLTYPE [message #614394 is a reply to message #614390] Wed, 21 May 2014 05:16 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
Thank you
Re: How to format output of query for coulmn type XMLTYPE [message #614476 is a reply to message #614394] Thu, 22 May 2014 04:15 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
Dear all,
Any advice please.
Re: How to format output of query for coulmn type XMLTYPE [message #614477 is a reply to message #614476] Thu, 22 May 2014 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you don't post what we request you will not have help.

Re: How to format output of query for coulmn type XMLTYPE [message #614483 is a reply to message #614477] Thu, 22 May 2014 05:31 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
Dear Michel,
Thanks for your feedback , note that i posted all avilable data and it's very clear am wondering why you insist in having a clear names ? it's very clear please double check and all friends can advice for my problem .

i will conclude again
select xmlserialize(document  coulmn_name  indent size = 2) res from  table_name
RES
--------------------------------------------------------------------------------
<row id="******" xml:space="preserve"><c1>*****</c1><c2>*******



Re: How to format output of query for coulmn type XMLTYPE [message #614492 is a reply to message #614483] Thu, 22 May 2014 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It works for me.
I (and every one) can't know why it does not for you at you didn't post something we can reproduce.
So we can't help.

Re: How to format output of query for coulmn type XMLTYPE [message #614494 is a reply to message #614492] Thu, 22 May 2014 07:09 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
What you mean by "It works for me"
you mean you got a clear text in results whithout xml code?please advice
on the otherhand i have no Mysterious point all what i did is hashing the sensetive data and leave the code which you need to trace.

[Updated on: Thu, 22 May 2014 07:11]

Report message to a moderator

Re: How to format output of query for coulmn type XMLTYPE [message #614503 is a reply to message #614494] Thu, 22 May 2014 09:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
What you mean by "It works for me"


See my examples above.
Did you even try it to see if it works for you?

Re: How to format output of query for coulmn type XMLTYPE [message #614525 is a reply to message #614494] Thu, 22 May 2014 15:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
-- Suppose you have a table and data like this:
SCOTT@orcl12c> CREATE TABLE table_name
  2    (column_name  XMLTYPE)
  3  /

Table created.

SCOTT@orcl12c> INSERT INTO table_name (column_name) VALUES (XMLTYPE (
  2  '<?xml version="1.0"?>
  3   <rowset>
  4  	<row id="10" xml:space="preserve">
  5  	  <c1>ACCOUNTING</c1>
  6  	  <c2>NEW YORK</c2></row>
  7  	<row id="20" xml:space="preserve">
  8  	  <c1>RESEARCH</c1>
  9  	  <c2>DALLAS</c2></row>
 10  	<row id="30" xml:space="preserve">
 11  	  <c1>SALES</c1>
 12  	  <c2>CHICAGO</c2></row>
 13  	<row id="40" xml:space="preserve">
 14  	  <c1>OPERATIONS</c1>
 15  	  <c2>BOSTON</c2>
 16  	</row>
 17   </rowset>'
 18  ))
 19  /

1 row created.

SCOTT@orcl12c> SELECT XMLSERIALIZE (DOCUMENT column_name INDENT SIZE = 2) res FROM table_name
  2  /

RES
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<rowset>
  <row id="10" xml:space="preserve">
     <c1>ACCOUNTING</c1>
     <c2>NEW YORK</c2></row>
  <row id="20" xml:space="preserve">
     <c1>RESEARCH</c1>
     <c2>DALLAS</c2></row>
  <row id="30" xml:space="preserve">
     <c1>SALES</c1>
     <c2>CHICAGO</c2></row>
  <row id="40" xml:space="preserve">
     <c1>OPERATIONS</c1>
     <c2>BOSTON</c2>
   </row>
</rowset>


1 row selected.


-- To display the xml data in tabular format:
SCOTT@orcl12c> SELECT x.id, x.c1, x.c2
  2  FROM   table_name t,
  3  	    XMLTABLE
  4  	      ('//row'
  5  	       PASSING t.column_name
  6  	       COLUMNS
  7  		 id  NUMBER	   PATH '@id',
  8  		 c1  VARCHAR2(15)  PATH 'c1',
  9  		 c2  VARCHAR2(15)  PATH 'c2') x
 10  /

        ID C1              C2
---------- --------------- ---------------
        10 ACCOUNTING      NEW YORK
        20 RESEARCH        DALLAS
        30 SALES           CHICAGO
        40 OPERATIONS      BOSTON

4 rows selected.

Re: How to format output of query for coulmn type XMLTYPE [message #614644 is a reply to message #614525] Sun, 25 May 2014 02:17 Go to previous messageGo to next message
ledo60
Messages: 63
Registered: May 2014
Member
Dear Barbara Boehmer :
Perfect , but am stopping after selecting 15 row and having this error ERROR please advice:
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence
- got multi-item sequence
Re: How to format output of query for coulmn type XMLTYPE [message #614647 is a reply to message #614644] Sun, 25 May 2014 03:35 Go to previous messageGo to previous message
ledo60
Messages: 63
Registered: May 2014
Member
I am tryin to trace the issue so i tried to select c2 only and it's raised the error while id and c1 selected normaly


SELECT x.c2
FROM table_name t,
XMLTABLE
('//row'
PASSING t.coulmn_name
COLUMNS
c2 VARCHAR2(100) PATH 'c2') x
/

Please advice

[Updated on: Sun, 25 May 2014 03:35]

Report message to a moderator

Previous Topic: xml data in columns
Next Topic: Grants for loadjava
Goto Forum:
  


Current Time: Fri Mar 29 10:28:49 CDT 2024