OraFAQ Forum: JDeveloper, Java & XML » How to format output of query for coulmn type XMLTYPE

Home » Developer & Programmer » JDeveloper, Java & XML » How to format output of query for coulmn type XMLTYPE (11.2.0.3.0)

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

How to format output of query for coulmn type XMLTYPE [message #614262]

Tue, 20 May 2014 03:27

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 .

Report message to a moderator

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

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.

Report message to a moderator

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

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

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

Smile

)

Report message to a moderator

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

ledo60
Messages: 63
Registered: May 2014

Member

Thank you , but from where should i got entry like michel></michel

Report message to a moderator

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

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

Report message to a moderator

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

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

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?

Report message to a moderator

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

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

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

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

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

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 ,

Report message to a moderator

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

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.

Report message to a moderator

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

ledo60
Messages: 63
Registered: May 2014

Member

Lalit,
can u assist in details please

Report message to a moderator

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

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

Senior Member
Account Moderator

Don't you see my examples above?

Report message to a moderator

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

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.

Report message to a moderator

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

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 /

Report message to a moderator

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

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

Report message to a moderator

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

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

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

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.

Report message to a moderator

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

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.

Report message to a moderator

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

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

Report message to a moderator

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

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>'))

Report message to a moderator

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

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

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?

Report message to a moderator

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

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

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.

Report message to a moderator

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

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

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

Senior Member
Account Moderator

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

Report message to a moderator

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

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

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

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.

Report message to a moderator

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

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

Report message to a moderator

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

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

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.

Report message to a moderator

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

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

Report message to a moderator

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

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

Senior Member
Account Moderator

I give up.
Good luck with your mystery.

Report message to a moderator

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

ledo60
Messages: 63
Registered: May 2014

Member

Thank you

Report message to a moderator

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

ledo60
Messages: 63
Registered: May 2014

Member

Dear all,
Any advice please.

Report message to a moderator

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

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

Senior Member
Account Moderator

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

Report message to a moderator

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

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>*******

Report message to a moderator

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

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

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.

Report message to a moderator

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

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

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

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?

Report message to a moderator

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

Barbara Boehmer
Messages: 9103
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.

Report message to a moderator

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

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

Report message to a moderator

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

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

Pages (2): [1 2 › »]

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 00:48:33 CST 2025