MacLochlainns Weblog

Somebody asked me about how they could convert an XML file to a CSV file to upload into MySQL. They were asking the question based on an old Convert XML to CSV blog post from 2008. Amazing though that is, I had to explain the process no longer requires manual tasks, like calling Java files from the Apache XML Project. All they needed to do was use the Oxygen XML Editor, which is why I wrote this blog post.

For example, I had them use the same sample XML file from the old blog post (shown below) with one change. The encoding value needs to change from latin1 (ISO-8859-1) to unicode (UTF-8). Then, they should put it into a local Windows directory (mine went into the C:\Data directory).

<?xml version="1.0" encoding="UTF-8"?>
<character>
  <name>
    <role>Indiana Jones</role>
    <actor>Harrison Ford</actor>
    <part>protagonist</part>
    <film>Indiana Jones and Raiders of the Lost Ark</film>
    <film>Indiana Jones and the Temple of Doom</film>
    <film>Indiana Jones and the Last Crusade</film>
    <film>Indiana Jones and the Kingdom of the Crystal Skull</film>
  </name>
  <name>
    <role>Wilhelmina Scott</role>
    <actor>Kate Capshaw</actor>
    <part>support</part>
    <film>Indiana Jones and the Temple of Doom</film>
  </name>
  <name>
    <role>Marion Ravenwood</role>
    <actor>Karen Allen</actor>
    <part>support</part>
    <film>Indiana Jones and Raiders of the Lost Ark</film>
    <film>Indiana Jones and the Kingdom of the Crystal Skull</film>
  </name>
  <name>
    <role>Elsa Schneider</role>
    <actor>Alison Doody</actor>
    <part>support</part>
    <film>Indiana Jones and the Last Crusade</film>
  </name>
  <name>
    <role>Short Round</role>
    <actor>Jonathan Ke Quan</actor>
    <part>support</part>
    <film>Indiana Jones and the Temple of Doom</film>
  </name>
  <name>
    <role>Sallah</role>
    <actor>Jonn Rhys-Davies</actor>
    <part>support</part>
    <film>Indiana Jones and Raiders of the Lost Ark</film>
    <film>Indiana Jones and the Last Crusade</film>
  </name>
  <name>
    <role>Professor Henry Jones</role>
    <actor>Sean Connery</actor>
    <part>support</part>
    <film>Indiana Jones and the Last Crusade</film>
  </name>
  <name>
    <role>Henry "Mutt" Williams</role>
    <actor>Shia LaBeouf</actor>
    <part>support</part>
    <film>Indiana Jones and the Kingdom of the Crystal Skull</film>
  </name>
  <name>
    <role>Marcus Brody</role>
    <actor>Denholm Elliott</actor>
    <part>support</part>
    <film>Indiana Jones and Raiders of the Lost Ark</film>
    <film>Indiana Jones and the Last Crusade</film>
  </name>
  <name>
    <role>Amrish Puri</role>
    <actor>Mola Ram</actor>
    <part>antagonist</part>
    <film>Indiana Jones and the Temple of Doom</film>
  </name>
  <name>
    <role>Rene Belloq</role>
    <actor>Belloq</actor>
    <part>antagonist</part>
    <film>Indiana Jones and Raiders of the Lost Ark</film>
  </name>
  <name>
    <role>Walter Donovan</role>
    <actor>Julian Glover</actor>
    <part>antagonist</part>
    <film>Indiana Jones and the Last Crusade</film>
  </name>
  <name>
    <role>Colonel Vogel</role>
    <actor>Michael Bryne</actor>
    <part>antagonist</part>
    <film>Indiana Jones and the Last Crusade</film>
  </name>
  <name>
    <role>Irina Spalko</role>
    <actor>Cate Blanchett</actor>
    <part>antagonist</part>
    <film>Indiana Jones and the Kingdom of the Crystal Skull</film>
  </name>
</character>

Then, I had them copy the following XML Style Language Transformation (XSLT) file into the same C:\Data directory with the encoding value change from latin1 to unicode:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
  <!-- This loops through the branch when a sibling meets a condition. -->
  <xsl:for-each select="character/name[film='Indiana Jones and the Last Crusade']">
  <!-- Sorts based on the value in the "role" element. -->
  <xsl:sort select="role" />
  <!-- Eliminates anyone that has a "part" element value of "antagonist". -->
    <xsl:if test="part != 'antagonist'">
      <!-- An apostrophe before and after with a line return. -->
      <xsl:text>&#39;</xsl:text>
      <xsl:value-of select="role"/>
      <!-- An apostrophe followed by a comma -->
      <xsl:text>&#39;&#44;</xsl:text>
      <xsl:text>&#39;</xsl:text>
      <xsl:value-of select="actor"/>
      <xsl:text>&#39;&#44;</xsl:text>
      <xsl:text>&#39;</xsl:text>
      <xsl:value-of select="film"/>
      <!-- An apostrophe followed by a line return -->
      <xsl:text>&#39;&#10;</xsl:text>
    </xsl:if>
  </xsl:for-each>
</xsl:template>
</xsl:stylesheet>

Open or launch the Oxygen XML Editor and do these steps:

• Create a new Project called character.
• Create the C:\Data\test.xml and C:\Data\convert.xsl files in a C:\Data directory.
• Open the C:\Data\test.xml and C:\Data\convert.xsl files inside the Oxygen XML Editor.
• Click on convert.xsl file tab before clicking on the Configure Transformation Scenario(s) button. The button looks like a red wrench with a small red arrow to the bottom right.
• After launching the Configure Transformation Scenario(s) dialog, click the Edit button in the dialog box and launch the Edit Scenario dialog.
• Enter file:/C:/Data/test.xml (use the file chooser if don’t want to type it) in the XML URL field in the Edit Scenario dialog.
• Click the OK button to close the Edit Scenario dialog and the Apply associated button to close the Configure Transformation Scenario(s) dialog.
• Click the Apply Transformation Scenario button, which is red arrow button. It will transform the XML document into a result pane at the bottom.
• Select All (or Ctrl+A) in the result panel and right click on that selected area to launch a context sensitive menu. In that menu, click the Save button to launch a file chooser that will let you save your results.

If you know how to write XSLT this is simple and if you don’t it might take a little time to find a working example on the Internet. Better yet, check out the w3schools for the XSLT documentation or tutorials point’s Learn XSLT website. You can see how to Upload the CSV file into MySQL on this older blog post.

In Linux, you can make this conversion using the Command-Line Interface (CLI) by using the xsltproc program. Assume the XML file is test.xml and the XSLT file is tocsv.xsl, then you can generate the Comma-Separated Values file with this syntax:

xsltproc tocsv.xsl test.xml > text.csv

As always, I hope this helps those looking for a simple solution.

I promised my students an example of writing xquery statements in Microsoft SQL Server. This post builds on two earlier posts. The first qualifies how to build a marvel table with source data, and the second qualifies how you can create an XML Schema Collection and insert relational data into an XML structure.

You can query a sequence with xquery as follows:

DECLARE @x xml;
SET @x = N'';
SELECT @x.query('(1,2,(10,11,12,13,14,15)),-6');

It returns:

1 2 10 11 12 13 14 15 -6

You can query a sequence with an xquery FLOWR statement. FLOWR stands for: FOR, LET, ORDER BY, WHERE, and RETURN. A sample xquery with a FLOWER statement is:

DECLARE @x xml;
SET @x = N'';
SELECT @x.query('for $i in ((1,2,(10,11,12,13,14,15)),-6)
                 order by $i
                 return $i');

It returns:

-6 1 2 10 11 12 13 14 15

You can query the entire node tree with the following xquery statement because it looks for the occurrence of any node with the /* search string:

DECLARE @x xml;
SET @x = N'<marvel>
             <avenger_name>Captain America</avenger_name>
           </marvel>';
SELECT @x.query('/*');

You can query the avenger_name elements from the marvel_xml table with the following syntax:

SELECT xml_table.query('/marvel/avenger_name')
FROM   marvel_xml;

It returns the following set of avenger_name elements:

<avenger_name>Hulk</avenger_name>
<avenger_name>Iron Man</avenger_name>
<avenger_name>Black Widow</avenger_name>
<avenger_name>Thor</avenger_name>
<avenger_name>Captain America</avenger_name>
<avenger_name>Hawkeye</avenger_name>
<avenger_name>Winter Soldier</avenger_name>
<avenger_name>Iron Patriot</avenger_name>

You can query the fourth avenger_name element from the marvel_xml table with the following xquery statement:

SELECT xml_table.query('/marvel[4]/avenger_name')
FROM   marvel_xml;

It returns the following avenger_name element:

<avenger_name>Thor</avenger_name>

You can use the value() function to verify an avenger_name element exists:

SELECT CASE
          WHEN xml_table.value('(/marvel[4]/avenger_name)','nvarchar') = 'T' THEN
            'Exists'
       END AS "Verified"
FROM   marvel_xml
WHERE  id = 3;

The query returns the Exists string when it finds a valid avenger_name element. You have a number of other tools to query results sets from the XML node tree.

I hope this helps my students and anybody interested in writing xquery-enable queries.

Working through Chapter 7 of the Querying Microsoft SQL Server 2012 book for Microsoft’s Exam 70-461, I found the XML examples incomplete for my students. I decided to put together a post on how to create:

• An XML Schema Collection type.
• A table that uses an XML Schema Collection as a column’s data type.
• An example on how you can transfer the contents of a table into the XML Schema Collection.

This post assumes you understand the basics about XML structures. If you’re unfamiliar with XML, please note that everything within it is case sensitive unlike SQL. You raise exceptions when the case of your XML fails to match the case of your XML Schema Collection definitions. I raised the following exception by using a Marvel element tag in title case when the XML Schema Collection uses a lowercase marvel element tag:

Msg 6913, Level 16, State 1, Line 2
XML Validation: Declaration not found for element 'Marvel'. Location: /*:Marvel[1]

The basic marvel table is defined in this earlier blog post. To ensure you don’t run into conflicts with previously existing objects, you can delete the marvel table with the following syntax:

1
2

IF OBJECT_ID('studentdb.marvel_xml','U') IS NOT NULL
  DROP TABLE marvel_xml;

There is no predefined function that lets you conditionally drop the XML Schema Collection from the data base. The alternative is to query the Microsoft SQL Server data catalog for the existence of a row before dropping the XML Schema Collection, like this:

1
2
3
4

IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'MarvelXMLTable')
  DROP XML SCHEMA COLLECTION MarvelXMLTable;
ELSE
  SELECT 'Not found.';

You can now create the MarvelXMLTable XML Schema Collection with the following syntax:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE XML SCHEMA COLLECTION MarvelXMLTable AS
N'<?xml version="1.0" encoding="UTF-16"?>
    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
                schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="Marvel">
      <xsd:complexType>
        <xsd:sequence>
          <xsd:element name="marvel_id" type="sqltypes:int" />
          <xsd:element name="avenger_name" type="sqltypes:nvarchar" />
          <xsd:element name="first_name" type="sqltypes:nvarchar" />
          <xsd:element name="last_name" type="sqltypes:nvarchar" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>';

After creating the XML Schema Collection, you can confirm whether it works correctly with the following statements. It will fail when you use semicolons on the DECLARE or SET lines, so avoid them as shown below:

1
2
3
4
5
6
7
8

DECLARE @marvel AS XML(MarvelXMLTable)
SET @marvel = '<marvel>
                 <marvel_id>1</marvel_id>
                 <avenger_name>Falcon</avenger_name>
                 <first_name>Sam</first_name>
                 <last_name>Wilson</last_name>
               </marvel>'
SELECT @marvel;

After creating and verifying the integrity of the XML Schema Collection, you can create a marvel_xml table. The xml_table column of the marvel_xml table uses a strongly-typed XML type, as shown:

1
2
3

CREATE TABLE marvel_xml
( id        INT IDENTITY(1,1) CONSTRAINT marvel_xml_pk PRIMARY KEY
, xml_table XML(MarvelXMLTable));

You can write an INSERT statement with single element like this:

1
2
3
4
5
6
7
8
9

INSERT INTO marvel_xml 
( xml_table )
VALUES
('<Marvel>
    <marvel_id>9</marvel_id>
    <avenger_name>Falcon</avenger_name>
    <first_name>Sam</first_name>
    <last_name>Wilson</last_name>
  </Marvel>');

You can insert two elements with an INSERT statement like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

INSERT INTO marvel_xml
( xml_table )
VALUES
('<Marvel>
    <marvel_id>9</marvel_id>
    <avenger_name>Falcon</avenger_name>
    <first_name>Sam</first_name>
    <last_name>Wilson</last_name>
  </Marvel>
  <Marvel>
    <marvel_id>10</marvel_id>
    <avenger_name>Scarlet Witch</avenger_name>
    <first_name>Wanda</first_name>
    <last_name>Witch</last_name>
  </Marvel>');

There’s a lot of typing to insert XML literal values. It a lot easier to transfer relational data from a table to an XML type. Unfortunately, Microsoft didn’t make it very easy. The FOR XML AUTO doesn’t work for two reasons. First, the FOR XML AUTO clause doesn’t render the correct XML structure. Second, the INSERT statement disallows nested SELECT queries that include the FOR XML AUTO clause.

Here’s the way you insert relational data into an XML type column:

1
2
3
4
5
6
7
8
9
10
11
12
13

DECLARE @xml_elements NVARCHAR(MAX);
SET @xml_elements = N'';
WITH x AS
(SELECT CONCAT(N'<marvel>'
              ,N'<marvel_id>',marvel_id,N'</marvel_id>'
              ,N'<avenger_name>',avenger_name,N'</avenger_name>'
              ,N'<first_name>',first_name,N'</first_name>'
              ,N'<last_name>',last_name,N'</last_name>'
              ,N'</marvel>') AS element
 FROM   marvel)
SELECT @xml_elements += element FROM x;
INSERT INTO marvel_xml (xml_table)
VALUES (@xml_elements);

Line 1 declares an xml_element session variable. Line 2 initializes the xml_element session variable as an empty string. The WITH clause on lines 3 through 10 creates a Common Table Expression (CTE) with the valid XML structure for all rows in the marvel table. The nested SELECT statement on line 11 returns data from the CTE and adds returned row to the session-level string variable. Finally, the INSERT statement on lines 12 and 13 inserts the XML table into the xml_table column.

You can then query the table with the following statement:

SELECT xml_table
FROM   marvel_xml;

Click on the return type in the SQL Server Management Studio (SSMS), and the XML structure will expand to show this:

<marvel>
  <marvel_id>1</marvel_id>
  <avenger_name>Hulk</avenger_name>
  <first_name>Bruce</first_name>
  <last_name>Banner</last_name>
</marvel>
<marvel>
  <marvel_id>2</marvel_id>
  <avenger_name>Iron Man</avenger_name>
  <first_name>Tony</first_name>
  <last_name>Stark</last_name>
</marvel>
<marvel>
  <marvel_id>3</marvel_id>
  <avenger_name>Black Widow</avenger_name>
  <first_name>Natasha</first_name>
  <last_name>Romanoff</last_name>
</marvel>
<marvel>
  <marvel_id>4</marvel_id>
  <avenger_name>Thor</avenger_name>
  <first_name>Thor</first_name>
  <last_name>Odinsson</last_name>
</marvel>
<marvel>
  <marvel_id>5</marvel_id>
  <avenger_name>Captain America</avenger_name>
  <first_name>Steve</first_name>
  <last_name>Rogers</last_name>
</marvel>
<marvel>
  <marvel_id>6</marvel_id>
  <avenger_name>Hawkeye</avenger_name>
  <first_name>Clint</first_name>
  <last_name>Barton</last_name>
</marvel>
<marvel>
  <marvel_id>7</marvel_id>
  <avenger_name>Winter Soldier</avenger_name>
  <first_name>Bucky</first_name>
  <last_name>Barnes</last_name>
</marvel>
<marvel>
  <marvel_id>8</marvel_id>
  <avenger_name>Iron Patriot</avenger_name>
  <first_name>James</first_name>
  <last_name>Rhodey</last_name>
</marvel>

As always, I hope this post benefits those who read it.

Having downloaded and tested some basics of the MySQL 5.5 Release Candidate, I started checking out the new features. While testing the new LOAD XML LOCAL INFILE feature, I discovered that there are restrictions governing the configuration of source XML files.

You must restrict the XML file to a list of tag names that correspond to column names within a tag defined as <row>. The tag names are case sensitive to your column names. You can replace the <row> tag name with any name of your choosing provided you append a clause that maps rows to your substitution XML tag name.

You can’t convert a file that has multiple child XML tags with the same name. Any attempt simply loads the last tag name found in the row hierarchy. Therefore, you should ensure that all source files have a unique list of case-sensitive child tags that map to the column definitions of the import table.

Either of the following table definition provides for lowercase column names. The first one uses nothing to delimit the column names.

CREATE TABLE CHARACTER
( ROLE CHAR(30) NOT NULL
, actor CHAR(30) NOT NULL
, part CHAR(20) NOT NULL
, film CHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

If you run SHOW CREATE TABLE character; you would see the more conventional definition below.

CREATE TABLE `character`
( `role` CHAR(30) NOT NULL
, `actor` CHAR(30) NOT NULL
, `part` CHAR(20) NOT NULL
, `film` CHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The following source file format supports the table definition because the XML tags are lowercase names. The source file wouldn’t work if the column names where uppercase or mixed case names.

<row>
  <role>Indiana Jones</role>
  <actor>Harrison Ford</actor>
  <part>protagonist</part>
  <film>Indiana Jones and Raiders of the Lost Ark</film>
  <film>Indiana Jones and the Temple of Doom</film>
  <film>Indiana Jones and the Last Crusade</film>
</row>

When the XML tags and column names match in case, you can load the file with the following syntax:

LOAD XML LOCAL FILE 'C:/Data/XML/character.html'
INTO TABLE `character`;

If you name the <row> tag <character>, you need to add a clause as noted below.

LOAD XML LOCAL FILE 'C:/Data/XML/character.html'
INTO TABLE `character`
ROWS IDENTIFIED BY '<character>';

If you have two film tags in a row tag, MySQL 5.5 doesn’t raise an error or warning. It simply loads the last <film> value. If you create tables with column names that don’t match on a case-sensitive basis, you’ll receive a 1263 warning message. You can see the warning message with the following command.

SHOW warnings;

The warning message only occurs when a column is not null constrained and the column name fails to match an XML tag attribute in the source file. No error or warning is raised when a column isn’t not null constrained under the same scenario. You can test it and then show warnings.

You should see something like this:

+---------+------+---------------------------------------------------------------------------------+
| Level   | Code | Message                                                                         |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1263 | COLUMN SET TO DEFAULT VALUE; NULL supplied TO NOT NULL COLUMN 'Role' at ROW 1   |
| Warning | 1263 | COLUMN SET TO DEFAULT VALUE; NULL supplied TO NOT NULL COLUMN 'Actor' at ROW 1  |
| Warning | 1263 | COLUMN SET TO DEFAULT VALUE; NULL supplied TO NOT NULL COLUMN 'Part' at ROW 1   |
| Warning | 1263 | COLUMN SET TO DEFAULT VALUE; NULL supplied TO NOT NULL COLUMN 'Film' at ROW 1   |
+---------+------+---------------------------------------------------------------------------------+

When you define a table with the AUTO_INCREMENT set to a value other than 1, the LOAD XML LOCAL FILE command resets the sequence to 1 before loading. I’d recommend you import into a table without an auto incrementing column and then use the REPLACE INTO to set the surrogate key values of auto incremented columns.

I posted how to connect to an Oracle database from an XSLT library file back in August 2008. It’s an event driven XML approach that can support web page development. One of my students wanted to do the same thing against MySQL. He quickly saw that it was simply a matter of the switching the JDBC library. He’s got the whole idea bundled on his blog here.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:sql="org.apache.xalan.lib.sql.XConnection"
  extension-element-prefixes="sql">
<xsl:output method="html" />
<xsl:template match="/">
<xsl:variable
  name="movies"
  select="sql:new('com.mysql.jdbc.Driver','jdbc:mysql:///storedb','student','student')" />
<xsl:variable name="streaming" select="sql:disableStreamingMode($movies)" />
<xsl:variable
  name="queryResults"
  select="sql:query($movies,'SELECT i.item_title, i.item_asin, i.item_release_date FROM storedb.item i')" />
<html>
<head><title>MySQL Result Set</title></head>
<body style="font-family: sans-serif;">
<table border="1" cellpadding="5">
  <tr>
    <xsl:for-each select="$queryResults/sql/metadata/column-header">
      <th><xsl:value-of select="@column-label" /></th>
    </xsl:for-each>
  </tr>
  <xsl:apply-templates select="$queryResults/sql/row-set/row" />
</table>
</body>
</html>
<xsl:value-of select="sql:close($movies)" />
</xsl:template>
<xsl:template match="row">
  <tr><xsl:apply-templates select="col" /></tr>
</xsl:template>
<xsl:template match="col">
  <td><xsl:value-of select="text()" /></td>
</xsl:template>
</xsl:stylesheet>

Hope this helps those looking for a solution.

It’s a bit awkward when a post generates a new question, but here’s a quick explanation and example of using XDB (XML Database Server) outside of the realm of APEX. More or less, XDB is an Apache Server equivalent configured inside the database. It’s really a protocol server tied into the Shared Server Oracle*Net Architecture (a correction provided by Marco Gralike). As a note, testing was done by using a NAT static IP addressing for the virtual Windows XP, Vista, and 7 environments.

This blog post will show you how to experiment with the PL/SQL Web Toolkit and build both password protected and unprotected database content. It assumes you have access to the SYS privileged account.

Setting Up a Secure DAD

There’s secure and then there’s secure. This falls in the less than secure category but it does provide a password and uses basic HTTP authentication. The USER is the schema name, and the PASSWORD is the same as that for the SQL*Plus access to the schema.

1. Connect as the privileged SYS user and run the following script. It creates a generic STUDENT user and grants minimalist privileges, then it creates a DAD (Data Access Descriptor), and authorizes the DAD. Don’t run the command if you’re actively using Oracle APEX on the default configuration of port 8080. It’s there for those folks you are running Tomcat on 8080.

-- This resets the default port so that it doesn't conflict with other environment.
EXECUTE dbms_xdb.SETHTTPPORT(8181);
 
-- This creates the STUDENT Data Access Descriptor.
EXECUTE dbms_epg.create_dad('STUDENT_DAD','/sampledb/*');
 
-- This authorizes the STUDENT_DAD
EXECUTE dbms_epg.authorize_dad('STUDENT_DAD','STUDENT');

2. Connect as the STUDENT user and run the following script to create a PL/SQL Web Toolkit procedure.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE OR REPLACE PROCEDURE HelloWorld AS
BEGIN
  -- Set an HTML meta tag and render page.
  owa_util.mime_header('text/html');  -- <META Content-type:text/html>
  htp.htmlopen;                       -- <HTML>
  htp.headopen;                       -- <HEAD>
  htp.htitle('Hello World!');         -- <TITLE>HelloWorld!</TITLE>
  htp.headclose;                      -- </HEAD>
  htp.bodyopen;                       -- <BODY>
  htp.line;                           -- <HR>
  htp.print('Hello ['||USER||']!');   -- Hello [dynamic user_name]!
  htp.line;                           -- <HR>
  htp.bodyclose;                      -- </BODY>
  htp.htmlclose;                      -- </HTML>
END HelloWorld;
/

3. Open a browser of your choice, and enter the following URL.

http://localhost:8181/sampledb/helloworld

Setting Up an Unsecured DAD

The trick here is building on what you did by eliminating the authentication. You do this by using the ANONYMOUS account, like Oracle’s APEX does. Well, not quite like it does because APEX provides a very good user authentication model. It allows you to connect to the ANONYMOUS user where you present and validate your credentials.

Since you have to do all the prior steps, these steps are numbered after those above. You start with step #4.

4. Generally, the XML configuration is missing one key node that allows repository anonymous access. The missing node disallows anonymous login. You can run the code below as the SYS privileged user to open that up if its missing. You can check whether or not it’s missing by running this as the SYS user:

SQL> @?/rdbms/admin/epgstat.sql

+-------------------------------------------------------------------+
| ANONYMOUS access to XDB repository:                               |
|  To allow public access to XDB repository without authentication, |
|  ANONYMOUS access to the repository must be allowed.              |
+-------------------------------------------------------------------+
 
Allow repository anonymous access?
----------------------------------
false
 
1 row selected.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

SET SERVEROUTPUT ON
DECLARE
  lv_configxml XMLTYPE;
  lv_value     VARCHAR2(5) := 'true'; -- (true/false)
BEGIN
  lv_configxml := DBMS_XDB.cfg_get();
 
  -- Check for the element.
  IF lv_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
    -- Add missing element.
    SELECT insertChildXML
           ( lv_configxml
           , '/xdbconfig/sysconfig/protocolconfig/httpconfig'
           , 'allow-repository-anonymous-access'
           , XMLType('<allow-repository-anonymous-access xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">'
       	   || lv_value
       	   || '</allow-repository-anonymous-access>')
       	   , 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
    INTO   lv_configxml
    FROM   dual;
 
    DBMS_OUTPUT.put_line('Element inserted.');
  ELSE
    -- Update existing element.
    SELECT updateXML
           ( DBMS_XDB.cfg_get()
           , '/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access/text()'
           , lv_value
           , 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
    INTO   lv_configxml
    FROM   dual;
 
    DBMS_OUTPUT.put_line('Element updated.');
  END IF;
 
  -- Configure the element.
  DBMS_XDB.cfg_update(lv_configxml);
  DBMS_XDB.cfg_refresh;
END;
/

5. These tasks also require the privileged SYSTEM user account, and you should sign on to that account to run these commands. The first thing you may need to do is unlock the ANONYMOUS account. It is locked by default. After you unlock it, you’ll need to verify no default password was assigned by unassigning a password. The following two commands accomplish those tasks.

-- Unlock the user account.
ALTER USER anonymous ACCOUNT UNLOCK;
-- Ensure a password is assigned to the account so you can create a synonym later.
ALTER USER anonymous IDENTIFIED BY ANONYMOUS;

6. These tasks require the privileged SYS user account because you’re going to create and authorize another DAD.

-- This creates the STUDENT_DB_DAD Data Access Descriptor.
EXECUTE dbms_epg.create_dad('STUDENT_DB_DAD','/db/*');
 
-- This authorizes the STUDENT_DB_DAD
EXECUTE dbms_epg.authorize_dad('STUDENT_DB_DAD','ANONYMOUS');
 
-- Open the anonymous account by setting the database-username parameter and value.
EXECUTE dbms_epg.set_dad_attribute('STUDENT_DB_DAD','database-username','ANONYMOUS');

7. Connect as the STUDENT user and grant EXECUTE permissions on the HELLOWORLD procedure to the ANONYMOUS user account. The GRANT allows you to give unrestricted access to the ANONYMOUS account, which in turn provides it to your web audience.

SQL> GRANT EXECUTE ON helloworld TO anonymous;

8. Connect as the ANONYMOUS user and create a local synonym that point to the STUDENT.HELLOWORLD procedure. The SYNONYM provides a program name for the URL statement. It’s hides the ownership of the actual procedure by supressing the schema name. (You may need to grant CREATE ANY SYNONYM as the SYSTEM user to the ANONYMOUS user.)

SQL> CREATE SYNONYM helloworld FOR student.helloworld;

SQL> ALTER USER anonymous IDENTIFIED BY NULL;

9. Open a browser of your choice, and enter the following URL, which won’t require a User Name or Password.

http://localhost:8181/db/helloworld

Somebody commented last week that they’d like the source code for an XSL example file found in this older post. I’ve added the requested code there. The code lets you query directly from your XSL against an Oracle database.

I’ve also created a top page for collecting references to XML blog pages and posts. It’s only got a few but it is an area that will be expanded.

Last week, I was wrapping up an on-site engagement. I couldn’t help but notice that while cows don’t fly, some may appear to fly, as shown in this photo. I took it with my iPhone, in front of the Salt Palace in Salt Lake City, Utah.

I’d been asked a question about whether you can build a view based on a DB_LINK to a LOB. My answer was no but you can write a Java socket and stored procedure to mimic it. Fortunately, they found an external API to solve their immediate problem.

Poking around on the web, there were a few apparently twisted approaches to creating a DB_LINK. There wasn’t a single place where they all had answer, so here’s a stab at it.

Failing to include a USING clause:

It seems that some new folks in the Oracle Community can be challenged by the USING clause in the CREATE DATABASE LINK command syntax. There were a few examples raising ORA-02019 errors. This is the likely syntax that causes it:

CREATE DATABASE LINK demo_db_link
CONNECT TO plsql IDENTIFIED BY plsql;

I was surprised that you could create a database link without an error with this syntax. Shouldn’t it raise an error and prevent giving the impression that it works?

It does raise an error when you try to resolve the database link, as …

SELECT   item_title
FROM     item@demo_db_link
WHERE    item_id = 1021;

This raises the following exception:

FROM    item@demo_db_link
             *
ERROR at line 2:
ORA-02019: connection description FOR remote DATABASE NOT found

The error message is clear if you understand that you can’t create a database link without the USING clause. It appears the reason newbies go down this route is this error:

CREATE DATABASE LINK demo_db_link
CONNECT TO plsql IDENTIFIED BY plsql
USING mclaughlin11g;

This raises the following exception:

USING mclaughlin11g
      *
ERROR at line 3:
ORA-02010: missing host CONNECT string

This error generates because the hostname isn’t a known identifier in SQL. The same error would occur if you substituted a valid TNS Alias name. As the documentation states, the value provided to the USING clause is a string and must be delimited by single quotes.

Reactive Programming:

Oracle’s errors aren’t bad, but then some are misleading without some experience. If a newbie got the prior error, they’d probably figure out that they need to enclose the “host connect string” in single quotes. They might create a database link like this with the machine hostname:

CREATE DATABASE LINK demo_db_link
CONNECT TO plsql IDENTIFIED BY plsql
USING 'mclaughlin11g';

It appears to work because there’s no validation of the link at creation time. Maybe there should be some validation, and it should raise an error when it isn’t found. This would be immediate feedback, and probably save beginners some time. Instead, they’ll get this error when they run it:

SELECT   item_title
FROM     item@demo_db_link
WHERE    item_id = 1021;

This raises the following exception:

FROM    item@demo_db_link
             *
ERROR at line 2:
ORA-12504: TNS:listener was NOT given the SERVICE_NAME IN CONNECT_DATA

Now, this is a definitive error message!!! It tells an experienced Oracle developer to look in the tnsnames.ora file. Hopefully, it leads a newbie to the same file. You probably agree it’s a clear error message.

Putting the pieces together:

Opening the $ORACLE_HOME/network/admin/tnsnames.ora or %ORACLE_HOME%\network\admin\tnsnames.ora file gives us the following orcl service name:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mclaughlin11g)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

The USING clause should point to a SERVICE_NAME value (oops, I forgot to overlay the color on that). You then create the database link with the following syntax:

CREATE DATABASE LINK demo_db_link
CONNECT TO plsql IDENTIFIED BY plsql
USING 'orcl';

Now the database link resolves as …

SELECT   item_title
FROM     item@demo_db_link
WHERE    item_id = 1021;

and it returns …

ITEM_TITLE
----------------------------------------
Harry Potter and the Sorcerer's Stone

Large Objects (LOBs) don’t work across database links:

While the database link works, if we attempt to access a CLOB or BLOB column it fails. This is demonstrated by the next screen shot:

SELECT   item_title
FROM     item@demo_db_link
WHERE    item_id = 1021;

This raises the following exception:

ERROR:
ORA-22992: cannot USE LOB locators selected FROM remote TABLES

Why does it fail? That’s a great question. LOBs are references in the database. They require a thread into the SGA for reading and writing. The thread can only exist in the scope of a transaction. You can’t create a transaction context across a database link. Therefore, a database link does not support a remote thread. You can fix this by writing a socket routine to place a temporary LOB in the local database. I’ll try to get back to this in a week or two and provide an example.

I’d gone through Doug Tidwell’s XSLT (2nd Edition), I was disappointed that there wasn’t any coverage in Chapter 9 about how to connect through an XSLT Processor to an Oracle database. Overall, the book is improved. I’d recommend it as a reference or a detailed read for the lost truth of XML. Seriously, it’s a very readable book.

I didn’t find anything through a quick search of the web, and I figured a little example might be nice. I used Xalan-Java of the example.  Xalan-Java is a convenient open source XSLT Processor, covered in an earlier blog. You can refer to that blog page for the download location and general configuration of Xalan-Java. There is one more configuration step after those in the other blog entry. You need to put the Oracle JDBC driver file in the class path. That’s where the Oracle class files are located. This example uses ojdbc5.jar from an Oracle Database 11g home on Windows:

C:\>SET CLASSPATH=%CLASSPATH%;C:\oracle\11.1.0\db_1\jdbc\lib\ojdbc5.jar

You can write this sample file, substituting a user, password, table and column (or two) from your own database:

The hidden rows didn’t fit nicely in the other image, but they’re required to make it work. There in the next image. They are the templates for handling columns and rows.

The command line generates an HTML page for you and redirects it to a file:

C:\> java org.apache.xalan.xslt.Process -XSL oracle_xml_query.xsl > sample.htm

or, the preferred syntax:

C:\> java org.apache.xalan.xslt.Process -XSL oracle_xml_query.xsl -OUT sample.htm

As per the request, here’s the native code for an XML direct query against an Oracle database.

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:sql="org.apache.xalan.lib.sql.XConnection"
 extension-element-prefixes="sql">
<xsl:output method="html" />
<xsl:template match="/">
<xsl:variable
  name="movies"
  select="sql:new('oracle.jdbc.driver.OracleDriver'
        ,'jdbc:oracle:thin:@mclaughlin11g:1521:orcl','plsql','plsql')" />
<xsl:variable name="streaming" select="sql:disableStreamingMode($movies)" />
<xsl:variable
  name="queryResults"
  select="sql:query($movies,'SELECT item_title, item_subtitle FROM item')" />
<html>
<head><title>Oracle Result Set</title></head>
<body style="font-family: sans-serif;">
<table border="1" cellpadding="5">
  <tr>
    <xsl:for-each select="$queryResults/sql/metadata/column-header">
      <th><xsl:value-of select="@column-label" /></th>
    </xsl:for-each>
  </tr>
  <xsl:apply-templates select="$queryResults/sql/row-set/row" />
</table>
</body>
</html>
<xsl:value-of select="sql:close($movies)" />
</xsl:template>
<xsl:template match="row">
  <tr><xsl:apply-templates select="col" /></tr>
</xsl:template>
<xsl:template match="col">
  <td><xsl:value-of select="text()" /></td>
</xsl:template>
</xsl:stylesheet>

Somebody asked for a URI reference to the PL/SQL code for my Oracle Database 11g PL/SQL ProgrammingOracle Database 11g PL/SQL Programming book. You can find it here on the McGraw-Hill site. If you find any glitches, just update my blog. I’ll fix them and get them reposted.

Also, I’ve updated the Comparative Data Modeling Relational versus XML with screen shots for the code and the mapping of a FLOWR (For, Let, Order by, Where, and Return) statements to nested loops for PL/SQL.