Home » Developer & Programmer » JDeveloper, Java & XML » How to format output of query for coulmn type XMLTYPE (11.2.0.3.0)
|
|
| Re: How to format output of query for coulmn type XMLTYPE [message #614686 is a reply to message #614647] |
Sun, 25 May 2014 15:43   |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It all depends on your data, which we can't see, so the following is just an example.
-- If there is more than one c2 in any row:
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>
<c2>BIG APPLE</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>
<c2>WINDY CITY</c2>
</row>
<row id="40" xml:space="preserve">
<c1>OPERATIONS</c1>
<c2>BOSTON</c2>
</row>
</rowset>
1 row selected.
-- Then this will raise an error:
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 /
XMLTABLE
*
ERROR at line 3:
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence
- got multi-item sequence
-- You can eliminate the error by declaring c2 as XMLTYPE:
SCOTT@orcl12c> COLUMN c2 FORMAT A30
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 XMLTYPE PATH 'c2') x
10 /
ID C1 C2
---------- --------------- ------------------------------
10 ACCOUNTING <c2>NEW YORK</c2>
<c2>BIG APPLE</c2>
20 RESEARCH <c2>DALLAS</c2>
30 SALES <c2>CHICAGO</c2>
<c2>WINDY CITY</c2>
40 OPERATIONS <c2>BOSTON</c2>
4 rows selected.
-- You can break it down further like so:
SCOTT@orcl12c> SELECT x.id, x.c1, y.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 XMLTYPE PATH 'c2') x,
10 XMLTABLE
11 ('//c2'
12 PASSING x.c2
13 COLUMNS
14 c2 VARCHAR2(15) PATH '.') y
15 /
ID C1 C2
---------- --------------- ------------------------------
10 ACCOUNTING NEW YORK
10 ACCOUNTING BIG APPLE
20 RESEARCH DALLAS
30 SALES CHICAGO
30 SALES WINDY CITY
40 OPERATIONS BOSTON
6 rows selected.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to format output of query for coulmn type XMLTYPE [message #614752 is a reply to message #614690] |
Mon, 26 May 2014 13:46 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
ledo60 wrote on Sun, 25 May 2014 23:24
1-c2 coulmn data is truncated , i tried to increase this parameter COLUMN c2 FORMAT A50 with no success.
The "COLUMN c2 FORMAT A50" is just a SQL*Plus command that determines the width of the display column. Any excess data should be wrapped around to the next line, not truncated. It is the last line "c2 VARCHAR2(15) PATH '.') y" of the query that needs to be changed to "c2 VARCHAR2(50) PATH '.') y" or whatever size is sufficient to hold the largest value. For extremely large values, you may also need to SET LONG to a larger value to display all of it.
ledo60 wrote on Sun, 25 May 2014 23:24
2-some data appered like this O?Ii , C?Ca i checked db nls and it's AL32UTF8 and AMERICAN_AMERICA.AR8MSWIN all was n't helpfull .
Was it XML type issue .
Without seeing the data or at least knowing what language it is in and what characters are not being displayed properly, it is difficult to guess what the problem is. It is more likely a display problem than a data storage or conversion problem. You can use ascii and substr on the resulting column values to see what values are actually being returned. Also, you may find that if you spool the results to a file, then edit that file, you see different results than what is displayed in the window that you run it from.
|
|
|
|
|
|
| Re: How to format output of query for coulmn type XMLTYPE [message #614875 is a reply to message #614874] |
Wed, 28 May 2014 02:12 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is an example of how to get the ascii values of the first 3 characters in column c2. You can expand this to check however many characters you want. You may want to post this portion of your problem as a separate question about a character set issue with values not being displayed as stored and provide a link to this thread. This is not my area of expertise. It is past midnight here, so I am going to bed now. Hopefully, someone else will help you.
SCOTT@orcl12c> SELECT ASCII (SUBSTR (y.c2, 1, 1)) || ',' ||
2 ASCII (SUBSTR (y.c2, 2, 1)) || ',' ||
3 ASCII (SUBSTR (y.c2, 3, 1))
4 FROM table_name t,
5 XMLTABLE
6 ('//row'
7 PASSING t.column_name
8 COLUMNS
9 id NUMBER PATH '@id',
10 c1 VARCHAR2(15) PATH 'c1',
11 c2 XMLTYPE PATH 'c2') x,
12 XMLTABLE
13 ('//c2'
14 PASSING x.c2
15 COLUMNS
16 c2 VARCHAR2(15) PATH '.') y
17 /
ASCII(SUBSTR(Y.C2,1,1))||','||ASCII(SUBSTR(Y.C2,2,1))||','||ASCII(SUBSTR(Y.C2,3,
--------------------------------------------------------------------------------
78,69,87
66,73,71
68,65,76
67,72,73
87,73,78
66,79,83
6 rows selected.
|
|
|
|
| Re: How to format output of query for coulmn type XMLTYPE [message #615215 is a reply to message #614875] |
Sun, 01 June 2014 00:38 |
 |
ledo60
Messages: 63
Registered: May 2014
|
Member |
|
|
Please find a sample from output
ASCII(SUBSTR(Y.C2,1,1))||','||ASCII(SUBSTR(Y.C2,2,1))||','||ASCII(SUBSTR(Y.C2,3,
--------------------------------------------------------------------------------
50055,50061,50083,78,65,68
What we can gain from this?
Regarding NLS i will open a new case.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 23:46:10 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
