Thanks very much
Sangita Saxena, April 16, 2003 - 11:23 am UTC
Thanks Tom - once again for a great solution .
Thanks
sangita saxena, April 16, 2003 - 11:40 am UTC
Hi tom
Thanks for the solution which i have already implemented .
But your statement "long term, use the right type for the data" . Isn't clob used for xml storage ? I thought clob and xmltype either could be used ?
April 16, 2003 - 12:00 pm UTC
You can store a number in a varchar2.
but it would be wrong. You spend lots of time converting strings into numbers over and over -- and the data isn't validated.
You can store XML in a clob.
but it would be wrong. You spend lots of time converting strings into XML documents over and over -- and the data isn't validated
Just substitute NUMBER for XML and VARCHAR for CLOB
Or
DATE for XML and NUMBER for CLOB
Or
<any specific type> for XML and <any more generic type> for CLOB
same argument. use the type that most closely models your data. XML -> xmltype. Unknown text -> clob.
getting an error
Anuja Batra, April 16, 2003 - 1:14 pm UTC
Tom
I tried the example and am getting an error
SQL > select
xmltype(e.x).extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
from t e;
xmltype(e.x).extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
*
ERROR at line 2:
ORA-00904: invalid column name
I also tried creating a table using xmltype and got an error
sql> create table yy(x xmltype);
create table yy(x xmltype)
*
ERROR at line 1:
ORA-00902: invalid datatype
However i could create the table using sys.xmltype
12:34:02 a246156@rmu2.world> create table yy(x sys.xmltype);
Table created.
So i replaced xmltype in your query with sys.xmltype and it still doesnt work .
sql > select
sys.xmltype(e.x).extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
from t e ;
sys.xmltype(e.x).extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
*
ERROR at line 2:
ORA-22876: this user-defined type is not allowed or it cannot be used in this context
Please advise
Thank You
Anuja
April 25, 2003 - 2:06 pm UTC
You're getting the error when you don't specify SYS.xmltype because you're on Oracle9i Release 1. In Release 2, you no longer have to specify SYS. For your query, try removing the (e.x) from the query... Not sure how/why that's there...
select sys.xmltype.extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
from t e;
Hope that helps!
How to insert into XMLType then
Nilendu, April 16, 2003 - 1:42 pm UTC
Tom,
I guess XMLType is a new 9i Rel. 2 feature.
We have been using CLOB for holding XML data too. We use DBMS_XMLSave.INSERTXML which inserts XML in the form of CLOB into the table.
But if we have XMLType column, how we insert the XML there?
I went to see spec for DBMS_XMLSave - but could not find INSERTXML has a XMLType argument.
Thanks,
Nilendu
9.0.1 feature
joe, April 16, 2003 - 3:32 pm UTC
Is XML a good idea to begin with?
Mikito Harakiri, April 16, 2003 - 8:18 pm UTC
How about
table REQUEST_DETAIL (
GROUP_TYPE integer,
GROUP_NAME varchar2,
...
)
?
XML has been promoted by document content folks who have no clue about data management. It's reincarnated EDI.
April 17, 2003 - 10:08 am UTC
XML is an excellent idea -- but like anything when taken to illogical extremes, can be made to look silly.
I've seen systems where people don't use subroutines (ok, in 21st century speak "methods"), they generate XML -- and soap it to eachother.
Why do something fast and efficient when you can do it "cool"...
Here I have to assume there is some logical reason they are storing XML in the database instead of using XMLSave to parse and load the XML as structured data.
Sonika, July 28, 2003 - 6:22 am UTC
Hi Tom,
understood that EXTRACT is used for searching in XML but
as in the above example, EXTRACT requires the full XPATH. I was trying to use wildcards and attributes in the XPATH.
I changed the XML to have a flag attribute.
<REQUEST_DETAIL>
<GROUP_TYPE flag="1">PR</GROUP_TYPE>
<GROUP_NAME flag="0">DATA ENGINEERING ASSET MANAG</GROUP_NAME>
<BUS_UNIT_ACRN flag="1">SCS-FCAT</BUS_UNIT_ACRN>
</REQUEST_DETAIL>
when I write the SQL : select e.request_detail('/REQUEST_DETAIL/*[@flag="1"]/text()').getStringVal()
from t_request e
This is the output which I got:
PRSCS-FCAT
The output gets clubbed as a single column. Is there any way that the output comes out as two different columns?
Thanks
Sonika
July 28, 2003 - 7:39 am UTC
getstringval by its very definition returns "a thing", not "some number of things"
think about it -- each and every row could/would return a different number of columns -- that won't work.
You would have to call getstringval for each attribute you wanted as a column
Thanks
Sonika, July 29, 2003 - 4:34 am UTC
Thanks Tom
..and 8.1.7
Steve Kiteley, November 18, 2003 - 9:13 am UTC
What is the best way to emulate this functionality (extracting a particular XML tag value from a clob) using Oracle 8.1.7?
December 08, 2003 - 10:11 am UTC
I asked Sean Dillon, our local XML technologist, to take a look at this here's what he had to say:
--
Hi Steve,
In Oracle8.1.7, your only option is the XML Developer's Kits on OTN (</code>
http://otn.oracle.com/tech/xml/ <code>. In the XDKs, we have PL/SQL, C, Java and JavaBeans support for DOM & SAX parsers. That would be the best way to do what you're trying to do. The XDKs are backward compatible to 8.1.6, so you should have no problems downloading the latest kits into your 8.1.7 database and using the parsers.
Hope that helps.
XML Type not decoding xml properly?
Darren, October 21, 2004 - 11:06 am UTC
Tom,
why is xmltype not decoding ampersands?
SQL> create table t
2 (
3 cl clob
4 )
5 /
Table created.
SQL> insert into t values ('<root>test & test</root>')
2 /
1 row created.
SQL> select xmltype(t.cl).extract('/root/text()').getStringVal()
2 from t
3 /
XMLTYPE(T.CL).EXTRACT('/ROOT/TEXT()').GETSTRINGVAL()
----------------------------------------------------------------------------
test & test
SQL>
shouldnt that be "test & test" ?
indeeed it doesnt like invalid XML..
SQL> insert into t values ('<root>test &test</root>');
1 row created.
SQL> select xmltype(t.cl).extract('/root/text()').getStringVal()
2 from t
3 /
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00241: entity reference is not well formed
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 1
no rows selected
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production
October 21, 2004 - 2:52 pm UTC
we are not an XML document viewer, that would be the job of that, we just extract text and give it to you.
i don't believe this to be improper behaviour personally -- but if you do, you can contact support and see if this is "correct" (seems correct and ok to me)
Ampersand in xml
Sasa, October 22, 2004 - 12:16 pm UTC
Hi Darren,
This is issue I posted several times and solution is to instead of extract function use extractvalue.
I will give you an example:
SELECT
xmlelement(
"TEST",
xmlforest(
id, testamp
)
).getstringval()
AS xmlpayload
FROM (SELECT 1 AS id, '1ST&2ND' AS testamp FROM dual) ;
and you have a such output:
<TEST><ID>1</ID><TESTAMP>1ST&2ND</TESTAMP></TEST>
Using extractvalue (find in documentation , mentioned that is almost same with extract) you got next:
SELECT
extractvalue(value(d),'/TEST/TESTAMP/text()') as TESTAMP
FROM
TABLE(XMLSequence(xmltype('<TEST><ID>1</ID><TESTAMP>1ST&2ND</TESTAMP></TEST>').extract('/TEST'))) d;
TESTAMP
-------
1ST&2ND
Hope that helps.
Regards,
Sasa
October 22, 2004 - 6:12 pm UTC
thanks!
Help regarding GROUP BY on CLOB datatype
Asim, August 17, 2008 - 9:55 pm UTC
Hi Tom,
I am looking for your help on this.
I am trying the following -
select b.app_name, a.qh_sql, count(*)
from nz_query_history a, nz_app_db b
where a.host_name='a.b.com'
and to_char(a.qh_tend,'yyyy-mm-dd') = '2008-08-04'
and a.host_name=b.host_name
and a.qh_database = b.db_name
group by b.app_name , a.qh_sql
order by count(*) desc;
Getting the error message - inconsistent datatype - expected got clob.
After googling sometime, some people suggested to use DBMS.SUBSTR function but looks like I can not use it for more than 4000 chars but my SQL query can be as long as 32767 characters.
Any suggestion on how do I handle this?
Thanks,
Asim
August 20, 2008 - 9:25 am UTC
you cannot group by on a lob
you'll need to find some other approach. for example, instead of storing just the sql query, maybe you store also the sql_id and group by sql_id (a hash of the sql statement)
or, you write your own "hash" function or use dbms_crypto to hash the long string into a short 'hash' to group by.
GROUP by on CLOB
Asim, August 22, 2008 - 5:33 pm UTC
Hi Tom,
Thanks for your reply.
This SQL statement is from datawarehouse appliance called Netezza so it does not have any SQL_ID. We are actually pulling the query history out of Netezza into Oracle and do some query audit.
Could you please give me an example how to use DBMS_CRYPTO? I have never used it before. All I have is a CLOB column having 32768 characters which needs to be grouped so that I could provide one statistics as most frequent queries?
Thanks,
Asim
August 26, 2008 - 7:41 pm UTC
dbms_crypto is documented. otn.oracle.com has all documentation. Calling it to generate a hash is really pretty straightforward.
use the hashing functions to create a "hopefully unique" signature.
manasa, February 16, 2011 - 3:22 am UTC
Hi Tom,
i have a similer question.
i am unig
pd.Content.value('(/BADirect/BADirectCSE/INARequestStatus)[1]', 'varchar(20)') this is working fine in SQL
i converted it to
select xmltype(pd.content).extract('/BADirect/BADirectCSE/INARequestStatus/text()').getstringval()
from gcsrept.ProductDetail pd
to make it compatable with oracle. but it is throwing an error
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00240: element-start tag is not well formed
ORA-06512: at "SYS.XMLTYPE", line 254
ORA-06512: at line 1
please help me in resolving this.
February 16, 2011 - 8:40 am UTC
no idea what you are doing.
there is no example here whatsoever to work with.
xml
A reader, February 16, 2011 - 8:56 pm UTC
Clob Search
Deepak, May 28, 2013 - 2:40 pm UTC
Hi Tom,
We have a table column of CLOB type.
The values in this column are like...
ID_COL CLOB_COL
====== ========
10 A,B,C,D,E,...,X,Y,Z
20 A1,B1,C1,D1,E1,...,X1,Y1,Z1
Our query is required to return the rows matching a sub string of the CLOB value.
e.g.,
if we query for the sub string "D1" in the CLOB_COL it should return the row with ID_COL value 20.
The problem with this query is that it does a full table scan of the table as it searches the CLOB column.
We have thought of flattening the table by creating another table like the following.
ID_COL CLOB_SUBSTR_VAL
====== ===============
10 A
10 B
10 C
...
...
20 A1
20 B1
Need your help in understanding if there would be a better approach than this to avoid full table scan?
May 29, 2013 - 6:59 pm UTC
create a text index on it. use contains to search it.
now that said, it does look like someone is storing a delimited list in a clob - which means your existing table is wrong and your proposed fix is the actual and ONLY table you want....
similar problem
Pritish, June 18, 2013 - 2:21 pm UTC
set long 1000000000
set longchunksize 10000000
SET LINESIZE 30000
SET TRIMOUT ON
SET NUMWIDTH 40
SET HEADING OFF
--SET COLSEP '|'
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF6';
SELECT trigger_log_id || '|' || TO_CLOB(REPLACE(REPLACE(EXTRACT(response, '/ResponseFromAPI'), CHR(10), ''), CHR(13), '')) AS response
FROM klog_gc.t_trigger_log
WHERE update_date >= (
SELECT NVL(MAX(last_extract_date), TO_DATE('19000101', 'YYYYMMDD'))
FROM klog_batch.t_file_log
WHERE header = 'STG_TRIGGER_RESPONSE'
AND file_type_code = 'CDWETL'
);
June 18, 2013 - 4:04 pm UTC
similar problem to what exactly?
I sometimes wish I could rate "questions", this would get less than a half star.
to_clob only works in create table as select and insert as select, I presume that is the issue here - you cannot to_clob in a select like that.
similar problem
Pritish, June 18, 2013 - 2:23 pm UTC
Any solution for ora- 19011 error?
June 18, 2013 - 4:05 pm UTC
no creates
no inserts
no look
we'd sort of need a way to see what you see and reproduce the issue.
A reader, January 09, 2018 - 9:49 pm UTC
i used this
select xmltype(A.TXN_PAYLOAD).extract('/Transaction/UnitID/text()').getstringVal() from TALEND_ODS.POS_POSLOGU_PAYLOAD A.
and got Null values
January 10, 2018 - 12:52 am UTC
I got an error.
SQL> select xmltype(A.TXN_PAYLOAD).extract('/Transaction/UnitID/text()').getstringVal() from TALEND_ODS.POS_POSLOGU_PAYLOAD A;
select xmltype(A.TXN_PAYLOAD).extract('/Transaction/UnitID/text()').getstringVal() from TALEND_ODS.POS_POSLOGU_PAYLOAD A
*
ERROR at line 1:
ORA-00942: table or view does not exist
which would make sense given that I don't have that table.
How to filter data in the XML?
MUKUNTHAN L, May 06, 2019 - 1:27 pm UTC
Hi Tom,
Thanks much for your support here. I have an extended scenario here. Let's say, my xml data is like this:
<?xml version="1.0" encoding="UTF-8"?>
<REQUEST_DETAIL>
<GROUP_ID>1</GROUP_ID>
<GROUP_TYPE>PR</GROUP_TYPE>
<GROUP_NAME>DATA ENGINEERING ASSET MANAGEMENT</GROUP_NAME>
<BUS_UNIT_ACRN>SCS-FCAT</BUS_UNIT_ACRN>
<PROJ_MGR_ID />
<PROJ_MGR_NAME>Roland Roy</PROJ_MGR_NAME>
<GROUP_ID>2</GROUP_ID>
<GROUP_TYPE>PZ</GROUP_TYPE>
<GROUP_NAME>DATA ENGINEERING ASSET MANAGEMENT</GROUP_NAME>
<BUS_UNIT_ACRN>FCAT-XYZ</BUS_UNIT_ACRN>
<PROJ_MGR_ID />
<PROJ_MGR_NAME>Roland Roy</PROJ_MGR_NAME>
</REQUEST_DETAIL>
Here, I have repeated the same XML data twice - for two rows of data. I have included a <GROUP_ID> to distinguish the rows. And for the 2nd row, I have updated the <GROUP_TYPE> value with PZ and <BUS_UNIT_ACRN> value with FCAT-XYZ.
Now, I need to fetch the <BUS_UNIT_ACRN> value wherever the <GROUP_TYPE> value is 'PZ'. Can I do a where condition to this extent? Please advise. Thanks.
May 10, 2019 - 8:39 am UTC
Both the GROUP_ID elements have the same parent. This makes it tricky to identify the corresponding BUS_UNIT_ACRN for a given GROUP_ID.
Nest each set of elements in their own GROUP (or whatever) element. You can then use XMLTable to extract the attributes as traditional rows and columns. And query them like a normal table:
with rws as (
select xmltype ( '<?xml version="1.0" encoding="UTF-8"?>
<REQUEST_DETAIL>
<GROUP>
<GROUP_ID>1</GROUP_ID>
<GROUP_TYPE>PR</GROUP_TYPE>
<GROUP_NAME>DATA ENGINEERING ASSET MANAGEMENT</GROUP_NAME>
<BUS_UNIT_ACRN>SCS-FCAT</BUS_UNIT_ACRN>
<PROJ_MGR_ID />
<PROJ_MGR_NAME>Roland Roy</PROJ_MGR_NAME>
</GROUP>
<GROUP>
<GROUP_ID>2</GROUP_ID>
<GROUP_TYPE>PZ</GROUP_TYPE>
<GROUP_NAME>DATA ENGINEERING ASSET MANAGEMENT</GROUP_NAME>
<BUS_UNIT_ACRN>FCAT-XYZ</BUS_UNIT_ACRN>
<PROJ_MGR_ID />
<PROJ_MGR_NAME>Roland Roy</PROJ_MGR_NAME>
</GROUP>
</REQUEST_DETAIL>') doc
from dual
)
select x.*
from rws r, xmltable (
'/REQUEST_DETAIL/GROUP' passing r.doc
columns
GROUP_TYPE path 'GROUP_TYPE',
BUS_UNIT_ACRN path 'BUS_UNIT_ACRN'
) x
where group_type = 'PZ';
GROUP_TYPE BUS_UNIT_ACRN
PZ FCAT-XYZ