Home » SQL & PL/SQL » SQL & PL/SQL » Trying (and failing) to use PL/SQL variable in a XML query in a cursor
icon9.gif  Trying (and failing) to use PL/SQL variable in a XML query in a cursor [message #264707] Tue, 04 September 2007 04:56
Messages: 3
Registered: September 2007
Junior Member
I'm sure I'm just being thick, but I just cannot get the following to work.

DB: Oracle
OS: HPUX 11.23

I have an XSD file that I have loaded into an XMLType table (called xsd_table in the example below). It looks roughly like this:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="BASE_ELEMENT">
     <xs:element name="MID_ELEMENT_A">
        <xs:element name="TOP_ELEMENT_A_1">
        <xs:element name="TOP_ELEMENT_A_2">
     <xs:element name="MID_ELEMENT_B">
        <xs:element name="TOP_ELEMENT_B_1">
        <xs:element name="TOP_ELEMENT_B_2">

What I want to end up with this a table that contains a list of all the TOP_ELEMENT_X_# names, associated with the relevant MID_ELEMENT_X.


I've extracted all the MID_ELEMENT names into a table with the following code:
INSERT INTO mid_element_table
SELECT value(t).extract('/xs:element/@name',
             'xmlns:xs="http://www.w3.org/2001/XMLSchema"') schedule
FROM cesa_xsd x,
     xs:sequence/xs:element',    -- line split to reduce length
     'xmlns:xs="http://www.w3.org/2001/XMLSchema"'))) t

Now, what (I think) I want to do is use a PL/SQL cursor to loop through this list of MID_ELEMENTs and extract the relevant TOP_ELEMENTs for each one.

To this end, I've done the following:

cursor c1 is
  select mid_element
  from mid_element_table;
  for c1_rec in c1
    insert into top_element_table
      (mid_element, top_element)
    select c1_rec.mid_element,
    from xsd_table x,
              'xmlns:xs="http://www.w3.org/2001/XMLSchema"'))) t;
end loop;

(I've hard coded the value for V_MID_ELEM in the DECLARE section for now, I'll work out how to set it as a value in the loop if/when I get the extract bit working)

Problem is, it doesn't work. It inserts 0 rows. I'm fairly certain the issue lies with the V_MID_ELEM variable I've used in the extract bit.

I've tried various combinations of brackets and quote marks to no avail. I've tried replacing V_MID_ELEM with c1_rec.mid_element - this doesn't work either. If I replace it with a hardcoded value (e.g. "MID_ELEMENT_A") then it does work - though this obviously doesn't do what I want it to.

The only alternative I can think of at the moment, is to change the variable with a &&1 and run the script multiple times (or sed the script from UNIX). However, I'd like to keep the number of scripts down if possible.

My PL/SQL isn't exactly brilliant, so I'm not sure what I'm doing wrong. I've been reading manuals and web pages about this for weeks but I'm not even sure what I'm looking for.

Can anyone help me before I go totally loopy!

EDIT: split a few lines to bring them under the 80 char limit.

[Updated on: Tue, 04 September 2007 05:41]

Report message to a moderator

Previous Topic: How to Combine data in a single SQL statement of two different clauses.
Next Topic: tree base query help
Goto Forum:

Current Time: Sat Jun 24 09:11:36 CDT 2017

Total time taken to generate the page: 0.05280 seconds