Trying (and failing) to use PL/SQL variable in a XML query in a cursor

[Ergates]

I'm sure I'm just being thick, but I just can't get the following to work.

I have loaded an XSD file into an Oracle xmltype table (called xsd_table here). The xsd looks roughly like this:

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

I have extracted all the element names at MID_ELEMENT_# level into a table (called mid_element_table here)

I'm now trying to extract all the element names at TOP_ELEMENT_X_# level (so I end up with a table containing mid_element_name, top_element_name for each top_element.

To do this, I've created the following:

[For now I've just hardcoded the variable V_MID_ELEM - once I get this working a bit, I'll try to properly integrate it with the cursor]

declare
cursor c1 is
  select mid_element
  from mid_element_table;
V_MID_ELEM VARCHAR2(10) := 'MID_ELEMENT_A';
BEGIN
  for c1_rec in c1
  loop
    insert into top_element_table
      (mid_element, top_element)
    select c1_rec.mid_element,
              value(t).extract('/xs:element/@name',
                               'xmlns:xs="http://www.w3.org/2001/XMLSchema"')
    from xsd_table x,
         table(xmlsequence(value(x).extract('/xs:schema/xs:element/xs:complexType/xs:sequence/xs:element[@name=V_MID_ELEM]/xs:complexType/xs:sequence/xs:element',
                                            'xmlns:xs="http://www.w3.org/2001/XMLSchema"'))) t;
end loop;
END;

But it doesn't work (inserts 0 rows).

I can get it to work by replacing the V_MID_ELEM within the XML query with a hardcoded value, but that's no good. It won't work if I replace it with "c1_rec.mid_element" either.

I've been reading manuals and searching the web for days, but I'm not even sure what I'm looking for. This is driving me nuts!

Can anyone help me, or at least point me in a useful direction to look?

[jkmyoung]

Sounds like string concatenation is needed:

'/xs:schema/xs:element/xs:complexType/xs:sequence/xs:element[@name=' + V_MID_ELEM + ']/xs:complexType/xs:sequence/xs:element'

not entirely familiar with the language you're using, so you might need to replace + with something else, like .