Hierarchical XML from SQL

Years ago I wrote an article (in Dutch) on the XML functions in Oracle SQL. It can be found here.
It describes how to create an xml document as an XMLType with an Oracle SQL Query.

The query that is described is based on a pretty simple table, with no relationships. I'm creating a new course based on a datamodel we created years ago, that contains data. I wanted to abstract some of that data (more...)

Run XQuery with XqlPlus

I started today with setting up an OSB training and figuring out how to run XQuery scripts outside of OSB. I looked into the (ok a little dated) book: Oracle Database 10g XML&SQL, that I have in my library. In that book there's also a chapter about XQuery. And it shows that the Oracle XDK has a XQuery processer, but also a commandline tool like SQLPLus: XQLPlus. It is in the xquery jar, but (more...)

SQL utils using XML

You may have previously seen a short post I did on a SQL statement to identify which statements are using dynamic sampling.

If not, quick recap:

SELECT p.sql_id, t.val
FROM   v$sql_plan p
,      xmltable('for $i in /other_xml/info
                 where $i/@type eq "dynamic_sampling"
                 return $i'
                passing xmltype(p.other_xml)
                columns attr  (more...)

Working with XML files and APEX – Part 2: Selecting data from XMLType

After we successfully imported XML files into our APEX application. It's time to start to analyze them.

We still assume that this is our example XML file:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<league id="1" name="2. Bundeliga">
<name location="Dresden" stadium="Glücksgas-Stadion">SG Dynamo Dresden</name>

Working with XML files and APEX – Part 1: Upload

Working with Oracle and XML can be a pain in the ass especially at the beginning when you don't know the hidden secrets. :) That's why I want to give some major hints how to integrate XML files in APEX applications.

This time I will provide an easy way how (more...)

Suche in Tabellen ähnlicher Struktur

Wieder war es eine Frage im OTN-Forum, die mich an etwas erinnert hat, das ich fast vergessen hatte: die Möglichkeit, mit Hilfe von XML-Funktionen innerhalb einer Query auf alle Tabellen zuzugreifen, die eine Spalte mit einem bestimmten Namen haben. Wenn ich z.B. herausfinden will, wie viele Datensätze in (more...)

Current date and time in XLST under OSB

For my current assignment I needed to build quite complex transformations in OSB. Mainly because of my experiences I choose to do that in XSLT.
Eclipse has a quite nice XQuery mapper, but it lacks an XSLT mapper.
Since JDeveloper has a nice XSL Mapper tool, I incorporated a JDeveloper (more...)

Oracle Dynamic SQL: generic search – crazy case

For the Friday evening I decided to dig out my example from the most recent of my published books (Expert PL/SQL Practices, APress, 2011) - it was a very fun project to work with: first, to come with with the idea of such multi-author book, second, to write (more...)

LPX-00209: PI names starting with XML are reserved

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates. I don't know why it took me more than the necessary to understand where was the problem when I hit the following error. ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00209: PI names starting with XML are reserved At any rate, in my case the reason was

Find your way with HttpUriType and Google Maps

Recently I read Duke Ganote writing about using UTL_HTTP to get stock quote from Yahoo. (Duke must have a thing for authorities, particularly Marshalls of Legoredo ;-) Anyway, I posted a comment how to do a similar thing with HttpUriType.

And that reminded me that long time ago I reminded myself that I should blog about how we use HttpUriType to query driving distance and time from Google Maps. (I have even tried to submit abstract to KScope and UKOUG on getting data with HttpUriType, UTL_HTTP or UTL_FTP, but no go so far...)

Let's imagine Larry needs directions to (more...)

SQL Solution for the Third International NoCOUG SQL & NoSQL Challenge

SQL vs NoSQL: Third International NoCOUG SQL & NoSQL Challenge sponsored by Pythian
As published in the 102nd issue of the NoCOUG Journal


BE IT KNOWN BY THESE PRESENTS that the Wicked Witch of the West needs your help to create
a (more...)

INSERT ALL master/detail data from XML

This is something I actually made for a quiz on PL/SQL Challenge, but I think the technique could be useful for others as well :-)

The idea is you may have some master/detail data (in this case orders and orderlines) for which you get XML with such data that needs to be inserted into two relational tables. Many might be tempted to parse the XML client side or in PL/SQL, loop through the data, and then insert the orders and lines row by row (or perhaps bulk insert from arrays.)

But it can be done in a single (more...)

How to simulate DML Operations on XML data using XU (XQuery Update)

The following SQL pattern can be used to simulate DML operations on XML data by utilizing XU (XQuery Update) functionalities.

This query will do the following XML data transformation by using XU insert/delete/rename/replace statements.

1) Convert all the XML Attributes to Elements.                ( XU Delete & Insert)

2) Change the  (more...)

How to validate XML data using XSD in Oracle XML DB

The following XML Schema can be used to validate the XML input data in the XML DB.

It enforces the following validation rules:

1) Parent_Tab/Parent_Row/dep_id is the (primary) key within Parent_Tab.
2) Parent_Tab/Parent_Row/dep_name should be one of the xs:enumeration list in DeptnameType and it is also unique within Parent_Tab.
3) (more...)

MS Exchange API for PL/SQL

| May 26, 2012
As mentioned in my earlier post, I've been working on a PL/SQL wrapper for the Microsoft Exchange Web Services (EWS) API. The code is now ready for an initial release!


Using this pure PL/SQL package, you will be able not just to search for and retrieve emails and download attachments, but you will also be able to create emails and upload attachments to existing emails. You can move emails between folders, and delete emails. You can read and create calendar items. You can get the email addresses of the people in a distribution (mailing) list, and more.


How to do XML based data manipulation in SQL and XSLT

The following SQL pattern can be used to do XML based data manipulation and transformation by utilizing XSLT functionality.

This query will do the following data manipulation and transformation:

1) Rename the xml tag <FirstName> to <FN> when the position of the element node is
either the last one or (more...)

How to do table column based data manipulation in SQL and XQuery

The following SQL pattern can be used to stragg all the table column data together
The STRAGG(column data) is grouped together based on the table column names.
This SQL also manipulates comma-delimited strings using XQuery functionalities.

(ID            NUMBER,

How to do character based data manipulation in SQL and XQuery

The following SQL pattern can be used to do character based data manipulation
by utilizing the XQuery functionalities.

COLUMN old_str  FORMAT  A28
COLUMN ordered_no_dup_chars FORMAT A23
COLUMN Reversed_chars FORMAT  A23

--------------------SQL Query-------------------

WITH data AS
(SELECT '3332221118888'   AS  old_str  FROM dual
  SELECT 'CCCBBBAAA'          FROM dual 
  SELECT  (more...)

How to convert an Internet HTML Table to CSV format in SQL

The following SQL pattern can be used to transform an HTML Table on the web to CSV format.

set long 10000;
variable url_input varchar2(500)
exec :url_input:='http://oraqa.com/2012/02/27/how-to-transform-a-csv-file-into-html-table-in-sql/'

-------------------------------------------------------------------SQL QUERY---------------------------------------------------------------------

(SELECT CAST(REGEXP_REPLACE(SUBSTR(STR, 0, REGEXP_INSTR(str,'</table>', 1,1,1, 'i') ), '( ){2,}', ' ') 
         AS VARCHAR2(4000)) AS html_str
 (SELECT SUBSTR(str,  (more...)

How to transform a RefCursor query resultset into HTML table in SQL

The following SQL pattern can be used to tranform a RefCursor query resultset into data in HTML table format by utilizing the XSLT functionality. The query can handle NULL in the data columns. The SQL input string should be based on the following format:

SELECT Col AS name_x, Function() AS (more...)