54 questions
0
votes
1
answer
41
views
Get the resultset of any SQL cursor as XML with column names as attribute without using XSLT in Oracle
I'm trying to create a routine that takes an arbitrary cursor as input and generates the resultset as XML in this form:
<ROWSET>
<ROW>
<COLUMN name="ID">1</COLUMN&...
- 2,562
0
votes
1
answer
32
views
Can the storage segment of an XMLTYPE column be named at will during creation?
Conversely as for LOB columns, I cannot find how to give the name I wish to the storage segment created for an XMLTYPE column within the CREATE TABLE script. Is that possible on Oracle 19c and can ...
- 2,562
0
votes
1
answer
208
views
Oracle XMLQUERY: How to find an XML node to remove based on sub-node properties?
Can you help me make the below code more robust and avoid hardcoding, please?
I have the following piece of code:
SET SERVEROUTPUT ON SIZE UNLIMITED;
exec DBMS_OUTPUT.ENABLE (NULL);
set lines 1000
...
- 143
0
votes
2
answers
181
views
XMLPATCH in PL/SQL?
I need to remove certain part of the XML (table definition from DBMS_METADATA). Found XMLPATCH and made it work for very small, example XML below. But I'm facing two problems: one big and one small.
...
- 143
1
vote
1
answer
5k
views
ORA-31061: XDB error: special char to escaped char conversion failed
I'm completely new to Oracle's XDB, trying to breakfix a code in production environment which i am completely new to.
SELECT DBMS_LOB.SubStr(RTRIM (XMLAGG (XMLELEMENT (E,XMLATTRIBUTES (ntx.nttx_str ...
- 53
1
vote
1
answer
435
views
Oracle XMLtable giving cross joined data
I have written a Oracle XML sql and it is giving output like below. I have given the whole code below from creating table to sql below for your quick help.
output: (WRONG)
ID, NAME
1 name1
1 ...
- 217
2
votes
3
answers
1k
views
Oracle - validate date format (yyyy-mm-ddThh24:mi:ssZ) in XML against XSD
Oracle version:
The result of this query select * from v$version; is:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2....
- 9,093
0
votes
1
answer
786
views
Extract Node Values within a CLOB from XMLType in PL/SQL and not a Table
Assuming I have the following XML data assigned to a local variable in PL/SQL of type CLOB, i.e:
l_clob := ‘<xml>
<ticket_order>
<table_no>1<table_no/>
<waiter>...
- 35.8k
0
votes
1
answer
133
views
Extract single repeating XML node through whole XML result
Given the following XML sample below, how can I extract all <table_no> values from top to bottom using Oracle XML extraction.
Based on below, I would expect to see the following individual rows ...
- 35.8k
1
vote
1
answer
321
views
Remove all annotations added into schema by Oracle dbms_xmlschema.registerschema
I register a schema into Oracle XMLDB via the dbms_xmlschema.registerschema() package sub program.
I then select the registered schema from user_xml_schemas.
The schema returns fine, but Oracle has ...
- 2,213
0
votes
1
answer
237
views
How to return Table as a row using Oracle XML DB Webservices
I am trying to expose an Oracle Table as a web service using XML DB Service.
I want to return multiple rows of table based on where condition passed in the Request of the web service.
Following -: ...
- 381
0
votes
0
answers
271
views
Oracle XMLDB retrieve invalid XML
I have invalid XML files(some tag is missing) in XML DB which are uploaded using FTP. When I try to retrieve these files using RESOURCE_VIEW, i get below error.
SELECT rv.res.getClobVal() FROM ...
- 3
2
votes
0
answers
908
views
Oracle SQL Function XMLISVALID returns allways 0
knows someone why this statement delivers me the result 0 and not 1
SELECT XMLISVALID( XMLType('<Termin Nr="498">
<Beginn>10.06.03</Beginn>
...
- 81
0
votes
2
answers
3k
views
Oracle XMLQuery- Display comma separated list with two XML nodes
I have a query such as this-
WITH xtbl AS (SELECT xmltype ('<root>
<parent>
<item>
<item_detail>AAA</...
- 8,896
4
votes
3
answers
3k
views
Oracle XMLTable- fetching column from parent node
I have the following XML structure:
<root>
<parent>
<parent_id>1</parent_id>
<parent_value>10000</parent_value>
<child>
...
- 8,896
2
votes
1
answer
2k
views
LEFT OUTER JOIN with XMLTABLE doesn't work?
Here is the sample query-
WITH empdata AS (SELECT xmltype ('<office>
<emp>
<empno>1</empno>
...
- 8,896
0
votes
1
answer
53
views
Linux version that is compatible with Berkeley XMLDB 2.5.16
Is Berkeley DBXML 2.5.16 compatible with latest version of Red hat Linux?
- 31
0
votes
2
answers
2k
views
Parse XML with CDATA attribute using Oracle XML Features
I have the following XML response after calling a WebService that has CDATA attribute.
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/...
- 1,028
0
votes
1
answer
54
views
Size of the Berkley XMLDB container remains unchanged even after deleting document
I am using Berkeley dbxml 2.5.16. First I insert a document Test.xml to myDb.dbxml container.
dbxml> putDocument KEY_1 Test.xml`
Then I checked the file size of the container,
bash-3.2$ ls -ll ${...
- 31
0
votes
1
answer
1k
views
oracle xmlexists with variable path
I have a table tsk with 2 columns : a task id in number, and a task scope in XMLTYPE which can be like :
<scope>
<siteCode>2</siteCode>
<supplierCode>1111</...
- 1
1
vote
1
answer
418
views
Oracle XML DB with XMLEXISTS don't work with xmlns on root XML
I have a table with XMLTYPE column and the XMLEXIST works fine.
But when the root element have a xmlns atribute the XMLEXISTS don't find the record what i'm looking for.
Without the xmlns attribute ...
0
votes
1
answer
487
views
Querying incrementing XML element names in Oracle
I am trying to query a XMLtype field in a table which has element names that increment by 1 on each occurrence. There could be 1 to many levels of these elements and I was wondering if there is a way ...
- 2,974
2
votes
3
answers
4k
views
Extract specific xml node from duplicate ones in oracle
Given is the value of "my_xml" column in "XYZ" table
<?xml version="1.0" encoding="UTF-8"?>
<India>
<city>
<string>ADI</string>
<string>Ahmedabad</...
- 4,334
0
votes
1
answer
327
views
insert bulk xml data into single table
I was trying to do an INSERT INTO .. SELECT from xmltable. The procedure completes successfully(quickly) without this insert, but when this code is included to insert 9999 rows it takes its time to ...
0
votes
1
answer
2k
views
xmlexists with namespace in Oracle
I am having issues figuring out how to integrate namespaces to a where clause having xmlexists. The query is as below:
SELECT sing_d1.sing_info_text1, sing_d1.sing_info_text2
FROM XMLTABLE(
...
0
votes
1
answer
631
views
What is the fastest way to query in batches a large xml stored in xmltype column in oracle database table?
I have Oracle xmldb with a table containing an xmltype column that has multiple records of large xmls. The size of each xml would be around 100MB with a portion of the xml having repeating elements.
...
- 858
1
vote
0
answers
143
views
How to disable browser's pop-up window with inscription "Attention required" when i enter to APEX local server?
I need your help. I please you. Recently i installed the Oracle APEX 3.2.1 using next instructions from:
https://www.youtube.com/watch?v=Nj8gXyA3Z4w
. And after that , i have already changed a ...
0
votes
1
answer
962
views
How to Specify Tablespace for XML Type Index creation in Oracle 11g
I am trying to create an XML Index on XML Type column in one of my table.
We are using 2 tablespace here, one for Tables and the second for Indexes.
I would like to create the XMLIndex in the index ...
- 81
2
votes
1
answer
2k
views
Oracle DB, validate xml during insert
I'm trying to validate a document XML during it's inserting into a table in ORACLE database. I've define a XML Schema and a XMLTYPE with this schema in my table but the db allow me to insert a wrong ...
- 733
2
votes
1
answer
94
views
Inserting new tag in XML in xmlDB
Is it possible to insert new tag in the XML which is there in the XML-database?
For example; below is my example which is exists in the database:
<PurchaseOrder
xmlns:xsi="http://www.w3.org/...
- 340
0
votes
1
answer
693
views
Adding an attribute to all nodes matching an XPATH expression using Oracle XML DB
I cannot come to a solution to this task: my goal is to pass a cursor to a PL/SQL procedure and get the results as an XMLType. The function dbms_xmlgen.getxmltype() makes this task straightforward
&...
- 2,562
0
votes
0
answers
497
views
Oracle 12g XmlType store as Binary XML (select issue)
I am trying to select XML from DB Oracle12g. XML is stored in the table (XMLType store as binary XML) and I am not able to create Select query with where-condition. My problem is that query doesn't ...
1
vote
1
answer
5k
views
How to aggregate multiple XMLELEMENT using XMLAGG
I want to aggregate multiple XMLELEMENT for table following columns and return an xml file.
Here is the table data:
I want return xml with following format:
<employee>
<id>FMCSC00015</...
- 1,469
0
votes
1
answer
689
views
PLSQL type file generator from xsd
Is there any API, command or tools available, which generates plsql type file in .sql format( so that i can keep in version control ) which may undergo changes for multiple releases, for the given xsd ...
- 13
0
votes
1
answer
320
views
Issue using XMLElement in oracle- xml
I am creating a table of xml type Oracle(I am new to this). I inserted a few records in the table. When I try to retrieve records from the table using the select statement I get the following error.
...
- 47
1
vote
0
answers
2k
views
Oracle Case Statement Along with XMLElement is giving different output on each execution
This is the oracle query I am trying to execute which is giving different output on each execution. For the first execution it is giving the correct xml and all subsequent request it is returning null....
- 11
2
votes
2
answers
1k
views
Comparing dates in Oracle XQuery (XMLQuery/XMLTable)
While trying to implement some checks in Oracle database with use of XQuery I need to compare if two dates are the same, but can't do that on obvious manner because casting to xs:date don't really ...
- 6,765
0
votes
1
answer
1k
views
Unable to use XMLTYPE in Oracle PL/SQL web service
I have been trying to provide my Oracle PL/SQL package as a web service in Oracle 11g (11.2.0.2.0). While everything else is going well, I seem to have a problem in using any procedure/function that ...
- 133
2
votes
1
answer
3k
views
ORA-00937: not a single group function (XMLAGG)
I used the XMLAGG aggregate function in a single row and I keep getting the error code ORA-00937. Why?
The structure for the report is:
<Forms>
<Year>2015</Year>
<Month&...
- 23
0
votes
1
answer
4k
views
ExtractValue/xmlsequence
I have the following code which works fine for smaller data it extracts, but when it runs into a record that is larger sized it errors. How would i go about getting around the ORA-01706:user function ...
1
vote
1
answer
188
views
select subselect parse results oracle
Temp Extract :
CREATE GLOBAL TEMPORARY TABLE "TRANSACTION"."TEMP_EXTRACT"
(
"EXTRACT_ID" VARCHAR2(256 BYTE),
"APP_DATA_ID" VARCHAR2(256 BYTE),
"IT_DEBIT_BANKID" VARCHAR2(11 BYTE)...
0
votes
1
answer
49
views
Not able to write where condition on complex type with array of tags
Iam new to the XML database, facing issue while writing where condition on id of transportEquipment.
My xml is :
below xml taken from the database. this xml has the multiple id in transportequipment ...
1
vote
2
answers
6k
views
got multi-item sequence
My intention is to "select the data where container number is equals to input data" (kind of search functionality). I am facing an issue when I am trying to retrieve the data, with a problem at where ...
- 340
1
vote
2
answers
2k
views
Using the XQUERY node function to extract values into rows
My question is identical to this one but instead of SQL Server I am using Oracle 12.1.
I would like to extract all the name elements from the following xml document:
<ci5>
<employee>
...
- 8,656
2
votes
2
answers
1k
views
Reading XML element occurrences using PL SQL
I have this XML.
<a>
<b>b1</b>
<c>c1</c>
<b>b2</b>
<c>c2</c2>
</a>
I want to be able to extract the values of elements 'b' and '...
- 505
0
votes
0
answers
173
views
extract clob value from table and store that in xml domdocument oracle
FOR get_rows IN DF_ROWS LOOP
row_elmt := xmldom.createElement(doc, 'ROW');
row_node := xmldom.appendChild(root_node, xmldom.makeNode(row_elmt));
item_elmt := xmldom.createElement(doc, '...
- 647
2
votes
1
answer
160
views
Parse XML in oracle when same dom structure is repeated multiple time's
I know how to parse an xml in oracle which goes something like this
select extractvalue(column_value, 'CIRS/CIR/@applicantId') applicantId
into applicantId
from CIBIL_BINARY_INPUT ,table(...
- 405
0
votes
0
answers
46
views
Oracle XMLDB: how should i refactor this query?
I have this query. its just running endlessly without any error. is there anything i'm doing wrong?
SELECT RECID, extract(XMLRECORD,'/row') as xmlrecord
FROM ACCOUNT
WHERE extractvalue(...
- 1,620
4
votes
2
answers
8k
views
Oracle SQL extractvalue from multiple elements
I have spent days looking for a simple solution to the following problem and I need some help please. I have a Oracle table with two columns, recid (Account Number) as the primary key and xmlrecord ...
- 61
0
votes
1
answer
259
views
How can I extract relational data from an Oracle database into a structured XML file?
I have a simple table structure in an Oracle Database (11gR2). Using generic terms, I have "groups" than contain one or more "items"; i.e. a one-to-many relationship. So the "item" table has a "...
- 323