Select an XML tag or node using Oracle PL SQL

Hi guys,
I’ve been using this trick for a while and it’s quite useful when querying Oracle Service Bus logs. I found myself trying to explain this one to a colleague and thought it made a nice post 🙂
Let’s start with the basic command:
extractvalue
Which translates into:
[code language=”sql”]
SELECT EXTRACTVALUE(
xmltype(xml_val),
‘/xml-fragment/tns:product’,
‘xmlns:tns="http://example.org/"’,
‘xmlns:ans="http://anothernamespace.org/"’,
‘xmlns:yans="http://yetanothernamespace.org/"’
)
x
FROM xml_table
[/code]
Note: the first argument is being cast from CLOB to XMLTYPE and that you can keep adding namespaces at the end by adding commas.
I’ve added three rows in my table “XML_TABLE” for this example:
[code language=”XML”]
ROW1:
<book category="WEB">
<title lang="en">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
ROW2:
<book category="CHILDREN">
<title lang="en">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
ROW3:
<book category="WEB">
<title lang="en">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
[/code]
Next we’ll query our XML_TABLE:
[code language=”sql”]
SELECT
EXTRACTVALUE( xmltype(xmlval), ‘/book/title’ ) AS title,
EXTRACTVALUE( xmltype(xmlval), ‘/book/author’ ) AS author,
EXTRACTVALUE( xmltype(xmlval), ‘/book/year’ ) AS year,
EXTRACTVALUE( xmltype(xmlval), ‘/book/price’ ) AS price
FROM xml_table;
[/code]
Which results in this output:
Workspace 1_034
Awesome, right!? 🙂
Continue reading “Select an XML tag or node using Oracle PL SQL”

Dynamic HTTP endpoint in Oracle Service Bus 12c based on values in a database routing table

This article outlines how to set a dynamic endpoint in an OSB HTTP Business Service. The endpoint is retrieved from a routing table which resides in an Oracle 12c database.

Components used for this solution:

  1. Ubuntu Linux 14.04 64bit
    1. JDeveloper, running the Quick Start Oracle Fusion Middleware suite
      1. Oracle Service Bus 12c
      2. Oracle Weblogic 12c
      3. OSB Project location:
        1. https://github.com/visscher/Fusion/tree/master/DBRouting
    2. Oracle Virtualbox Developer Days image for DB 12c, running:
      1. Oracle Database 12c
    3. Oracle SQL Developer 4

This picture shows the running solution in the OSB test console:
Oracle Service Bus Console 12c : Pipeline Testing - DBRouting_v1Pipeline - Google Chrome_019

Database table preparation

We need a routing table in our schema, I’m using this table setup:

CREATE TABLE "C##JORIS"."ROUTINGTABLE"
  (
    "ROUTE" VARCHAR2(50),
    "ENDPOINT" VARCHAR2(100)
  );
Where ROUTE stands for the identifier and ENDPOINT is the HTTP endpoint we try to reach.
I’ve inserted two rows:
Insert into ROUTINGTABLE (ROUTE,ENDPOINT) values ('SalesOrder','http://localhost:7101/salesEndpoint');
Insert into ROUTINGTABLE (ROUTE,ENDPOINT) values ('FinanceReceipt','http://localhost:7101/financeEndpoint');
These two endpoints will point to very simple OSB services which we will create in a moment.
Selection_022

Weblogic configuration: JDBC Data Source

We need to configure a JDBC data source in our Weblogic server, this data source is used by the XQuery function to execute SQL.
Start JDEVeloper, select your integrated Weblogic Server and start it up.
When your domain is started, open the WLS Console:
http://127.0.0.1:7101/console/
Login and open the Data Sources summary:
Selection_020
Navigation in Console: DefaultDomain - Services - Data Sources
Create a new datasource, in my example I use the JNDI name “LocalDB
When you’re done with the configuration, test the datasource to make sure all is well:
Selection_021
The status message will be green and show a check mark if you’ve configured your data source correctly.

JDeveloper: Oracle Service Bus project

If you import the DBRouting project from here, you should have all the necessary services.
I will only discuss the assign steps which are needed in the DBRouting_v1Pipeline.pipeline.
Selection_023
There are three assign actions:
1.) Assign $route: node-name($body/*[1])
This assign determines our routing key. It is the same key as
the first column in the routing table.
The XPath here is used to select the name of the first node
but you can change this to what you want to route on.
2.) Assign $query: 
fn:concat("select ENDPOINT from ROUTINGTABLE where ROUTE = '", $route, "'")
This assign determines the query which will be executed in
the next step. We want to select the ENDPOINT which belongs
to the ROUTE which was assigned in step 1.
3.) Assign $query: 
(fn-bea:execute-sql(
 xs:string("LocalDB"),
 xs:string("ENDPOINT"),
 $query
 )/*:ENDPOINT)[1]
This assign actually executes the SQL query to our database,
which is the first argument.
The second argument names the re-occurring rows, in this
case "ENDPOINT".
The thirst argument is the query to execute.
The XPath after the execute-sql statement is to make sure
we only get one endpoint.
4.) After those assigns, we use place a task “Routing Options” in the HTTP Route node:
We only use the “URI” Routing Option:
$endpoint/text()
Selection_024
This ends the article, if you execute the pipeline you will see the endpoint has become dynamic, it is retreived from the routing table:
Oracle Service Bus Console 12c : Pipeline Testing - DBRouting_v1Pipeline - Google Chrome_019

Oracle SQL – BLOB to XML Type ( ORA-06502 PL/SQL : numeric or value error : raw variable length too long )

I am working with a database schema where xml content is being stored in a BLOB and I’m working on querying that XML.
When you cast the BLOB to VARCHAR, you’ll run into the limit of 2000 characters with the following error:

ORA-06502 PL/SQL : numeric or value error : raw variable length too long

So this trick came in handy:

SELECT XMLTYPE( BLOB_FIELD, 871 ) AS AWESOMEXML FROM YOUR_TABLE;

Please be very aware of the character set that you are using, you can enter a world of hurt when you’re using the wrong one. The number 871 is the character set UTF8, which we are using.
For other character sets, check http://www.mydul.net/charsets.html

Format any XML (windows)

With this blog post, it’s possible to format any xml you’ve selected.
It will take about 5 minutes to get this working, a couple of manual steps are necessary.
These are the tools being used:

Steps to get it working: (All steps are necessary!)

  1. First install AutoHotKey_L and start it, you’ll see the logo if it’s running: AutoHotKey logo
  2. Create a folder on your Hard Drive called “C:\autohotkey”
  3. Create a file “tidycfg.ini” in the location “C:\autohotkey\tidycfg.ini” with the following contents:
    1. [Clean Indent XML]
      input-xml: yes
      bare:no
      clean:no
      fix-bad-comments:no
      fix-backslash:no
      indent:yes
      indent-attributes:no
      indent-spaces:4
      char-encoding:raw
      replace-color:no
      wrap:0
      wrap-asp:no
      wrap-jste:no
      wrap-php:no
      write-back:yes
  4. Place tidy.exe in the location C:\autohotkey
  5. Create a file called “autohotkey.ahk” in C:\autohotkey with the following contents:
    1. #x::
      sleep 50
      Send ^c
      ClipWait
      clipboard = %clipboard%
      FileDelete, C:\autohotkey\format_with_tidy.xml
      FileAppend, %clipboard%, C:\autohotkey\format_with_tidy.xml
      sleep 50
      RunWait, %comspec% /c C:\autohotkey\tidy.exe -config C:\autohotkey\tidycfg.ini C:\autohotkey\format_with_tidy.xml
      clipboard =
      FileRead, clipboard, C:\autohotkey\format_with_tidy.xml
      Return
  6. Doubleclick the file “autohotkey.ahk” in C:\autohotkey
  7. Select unformatted xml and press [ WINKEY+X ]
  8. The formatted xml is now in your clipboard and can be pasted anywhere.

Extra information:

These are the steps what is happening in the autohotkey file:

  1. Configure the keyboard shortcut (# = winkey, x = x-key)
  2. Wait 50 milliseconds
  3. Send the ctrl-c with keyboard combination ^c ( ^ = ctrl, c = c-key)
  4. Wait until the clipboard is filled
  5. remove all formatting from the clipboard
  6. Delete the temporary file (if present)
  7. Paste the clipboard contents in the temporary file
  8. Wait 50 milliseconds
  9. Run tidy.exe on the temporary file with the configuration settings
  10. empty the clipboard
  11. Paste the contents of the temporary file in the clipboard
  12. End script

Pretty print XML on Linux Command Line BASH

It’s quite handy to indent XML when you need to read it with the human eye, but on a terminal it’s often not as easily readible.
Fortunatly there’s a command which’ll indent it so you can actually read it;
xmllint –format file.xml
This will show you how ugly it could be:
 

Screenshot Unformatted XML on terminal
Screenshot Unformatted XML on terminal

 
And behold the indented beauty of this little gem:
Screenshot indented XML on terminal
Screenshot indented XML on terminal

Validate XML to XSD with XMLLINT on CLI

Awesome-ness!!
I was looking for a nice tool to validate my XML messages against an XML Schema, when I ran into this command where you can really easily validate on the command line. That’s really cool since we’re becoming CLI ninjas anyway!
Update: This also works on Windows with this little gem.
The command is as follows:

xmllint --noout --schema schema.xsd ./message.xml

Which delivered the following output:

joris@jorislatitude:~/workspaces/_examples/XSD$ xmllint --noout --schema sample_schema.xsd ./messageone.xml
./messageone.xml:2: element S_UNB: Schemas validity error : Element 'S_UNB': This element is not expected. Expected is ( INSDES ).
./messageone.xml fails to validate

As shown in above message, my example failed to validate, which is what I suspected, but now I can send my integration partner a better and really precise explanation! As said above: Awesomeness! 🙂

Create sample XML message from XSD in Eclipse (Free solution!)

On an Oracle Service Bus project you are often supplied with XSD’s or WSDL’s when you’re integrating with clients. The customer or client expects you to test their web service but before you do, you might want to take a look at the XML which should be sent to make sure you’re sending compliant XML.
One way to do this is by importing the WSDL in SoapUI and creating sample test requests, this comes in quite handy when you are actually using a WSDL, but what if the customer only supplies an XSD? SoapUI does not support creating sample XML from an XSD element.
There are several commercial products which can solve this problem but if you’re only looking for generating sample XML messages from XSD, you might want to look at using Eclipse’s built in XML generating option:
First step is to import the XSD in your project, the right click the Schema and select “Generate” and then “XML”

Screenshot - Eclipse - Generate XML from XSD
Screenshot – Eclipse – Generate XML from XSD

You will be presented with a “New XML file” dialogue which lets you save your new XML sample. Choose any location you want, and click “Next”. Then you will be presented with the options for your new XML sample. Select the options you desire and press “Finish”
Screenshot - Generate XML - New file dialogue
Screenshot – Generate XML – New file dialogue

Your new XML file has been generated and ofcourse is compliant with your XSD right now!

Select sample WS Callouts straight from your Oracle DB

It’s often nice to have the most recent data when you want to test a web service, so in the best scenario you would want to query existing data with some criteria such as active contracts and/or within a certain range.
This example will show you how to get ready-to-use web service callout requests from SQL Developer (or any other SQL tool) which can be used in a Service Bus Console or SoapUI. We will use the HR example database -which can be found in every Oracle DB- as the source.
This is the request which we’ll use:

select '<v1:IncreaseSalaryRequest xmlns:v1="http://example.joris.visscher/hr/types/v1/">
 <v1:EmployeeId>
 <v1:Type>DEFAULT</v1:Type>
 <v1:Identifier>'||emp.employee_id||'</v1:Identifier>
 </v1:EmployeeId>
 <v1:FirstName>'||emp.first_name||'</v1:FirstName>
 <v1:LastName>'||emp.last_name||'</v1:LastName>
 <v1:Department>
 <v1:Type>DEFAULT</v1:Type>
 <v1:Identifier>'||dep.department_id||'</v1:Identifier>
 </v1:Department>
 </v1:IncreaseSalaryRequest>' WSRequest
from employees emp
, departments dep
where emp.department_id = dep.department_id
-- Let's add some conditions which are needed to finish the process succesfully.
and emp.last_name is not null
and dep.department_name = 'IT' -- return the most valuable employees
-- We only need 5 results.
and rownum <= 5;

This will result in five instantly usable requests from live data so we’ll know for sure that all the criteria that should be met for a functional correct case are indeed met:

<v1:IncreaseSalaryRequest xmlns:v1="http://example.joris.visscher/hr/types/v1/">
  <v1:EmployeeId>
    <v1:Type>DEFAULT</v1:Type>
    <v1:Identifier>103</v1:Identifier>
  </v1:EmployeeId>
  <v1:FirstName>Alexander</v1:FirstName>
  <v1:LastName>Hunold</v1:LastName>
  <v1:Department>
    <v1:Type>DEFAULT</v1:Type>
    <v1:Identifier>60</v1:Identifier>
  </v1:Department>
</v1:IncreaseSalaryRequest>

This query should work on most databases, but it works on an Oracle DB for sure 🙂
Happy testing!

Oracle SQL developer queries an OracleXE11g db on Ubuntu 11.10

Use XSLT to display your RSS feeds on Google Chrome (Live Bookmark alternative)

I’ve recently made the transition from Firefox to Chrome as my main browser. Unfortunatly there still isn’t an alternative on Google Chrome for the Live Bookmark functionality which I used on Firefox, and this might not come soon according to these sources:
RSS Live Bookmarks @ Google Support forums
I did found this link, the extension was a bit too buggy for me (but maybe you’ll be fine)
Chrome Web Store – Live Bookmarks
While trying several extentions I did not find one which offers the same functionality as the Live Bookmarks on Firefox, only RSS readers which wouldn’t work as intended.
My use for these rss feeds is to point to servers and/or environments. So it’s more used as a dynamic bookmark than as a RSS reader. The bookmarks are placed on a local server at my company so our operations department only has to update 1 resource when there are updates.
The end result isn’t exactly the same as the live bookmark in Firefox but to me it’s a pretty good alternative:

Instructions:

First we’ll show you the XML which is used for this RSS feed:

<?xml version="1.0" encoding="utf-8"?>
<rss version="2.0"
 xmlns:atom="http://www.w3.org/2005/Atom"
 xmlns:dc="http://purl.org/dc/elements/1.1/">
 <channel>
 <title>jorisvisscher.wordpress.com Example RSS Feed</title>
 <link>http://localhost/links/example.xml</link>
 <description>Example RSS Feed for showing XSLT transformation</description>
 <atom:link href="http://localhost/links/example.xml" rel="self" type="application/rss+xml" />
 <item>
 <title>====== First Category ======</title>
 <link>http://google.com</link>
 </item>
 <item>
 <title>Local ALSB Server</title>
 <link>http://localhost:7001/console</link>
 </item>
 <item>
 <title>Local WLS Server</title>
 <link>http://localhost:7002/console</link>
 </item>
 <item>
 <title>Local BPM Workspace</title>
 <link>http://localhost:8585/workspace</link>
 </item>
 <item>
 <title>====== Second Category ======</title>
 <link>http://google.com</link>
 </item>
 <item>
 <title>Link to another server</title>
 <link>http://notlocalhost:7001:sbconsole</link>
 </item>
 <item>
 <title>Link to yet another server</title>
 <link>http://notlocahost:7001/console</link>
 </item>
 </channel>
</rss>

Previously on Firefox this feed would look like this:

Without a XSLT transformation, this RSS feed would look like this when opened in Google Chrome:

This ofcourse isn’t useful since we can’t click on the links. To fix this, we’ll add the following line to the XML file:

<?xml-stylesheet href="rss.xsl" type="text/xsl" media="screen"?>

The RSS Feed would look then like this:

<?xml version="1.0" encoding="utf-8"?>
<?xml-stylesheet href="rss.xsl" type="text/xsl" media="screen"?>
<rss version="2.0"
 xmlns:atom="http://www.w3.org/2005/Atom"
 xmlns:dc="http://purl.org/dc/elements/1.1/">
 <channel>
 <title>jorisvisscher.wordpress.com Example RSS Feed</title>
 <link>http://localhost/links/example.xml</link>
 <description>Example RSS Feed for showing XSLT transformation</description>
 <atom:link href="http://localhost/links/example.xml" rel="self" type="application/rss+xml" />
 <item>
 <title>====== First Category ======</title>
 <link>http://google.com</link>
 </item>
 <item>
 <title>Local ALSB Server</title>
 <link>http://localhost:7001/console</link>
 </item>
 <item>
 <title>Local WLS Server</title>
 <link>http://localhost:7002/console</link>
 </item>
 <item>
 <title>Local BPM Workspace</title>
 <link>http://localhost:8585/workspace</link>
 </item>
 <item>
 <title>====== Second Category ======</title>
 <link>http://google.com</link>
 </item>
 <item>
 <title>Link to another server</title>
 <link>http://notlocalhost:7001:sbconsole</link>
 </item>
 <item>
 <title>Link to yet another server</title>
 <link>http://notlocahost:7001/console</link>
 </item>
 </channel>
</rss>

As you can see, we are pointing to a xslt file to transform our RSS feed for use on the screen (or any other XML file for that matter). Place the XSLT file in the same directory as the RSS feed, or adjust the locator to the correct URL.
This is the XSLT file which we’ll use:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:wfw="http://wellformedweb.org/CommentAPI/">
 <xsl:output method="html"/>
 <xsl:template match="/">
 <xsl:apply-templates select="/rss/channel"/>
 </xsl:template>
 <xsl:template match="/rss/channel">
 <div>
 <h3><xsl:value-of select="title"/></h3>
 <p><xsl:value-of select="description"/></p>
 </div>
 <div>
 <xsl:apply-templates select="item"/>
 </div>
 </xsl:template>
 <xsl:template match="/rss/channel/item">
 <div>
 <a href="{link}" rel="bookmark"><xsl:value-of select="title"/></a>
 <xsl:value-of select="description"/>
 </div>
 </xsl:template>
</xsl:stylesheet>

Please note; It’s pretty basic, I didn’t do much formatting since I’m not that much of a CSS guru 🙂
When opened in Google Chrome, it will look like this:

Which is completely usable for me, since I don’t need Firefox anymore for my live bookmarks.
Happy bookmarking!