By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,958 Members | 1,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,958 IT Pros & Developers. It's quick & easy.

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

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1. <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
  2.  <xs:element name="BASE_ELEMENT">
  3.    <xs:complexType>
  4.     <xs:sequence>
  5.      <xs:element name="MID_ELEMENT_A">
  6.       <xs:complexType>
  7.        <xs:sequence>
  8.         <xs:element name="TOP_ELEMENT_A_1">
  9.          BLAH
  10.         </xs:element>
  11.         <xs:element name="TOP_ELEMENT_A_2">
  12.          BLAH
  13.         </xs:element>
  14.         ...
  15.         ...
  16.        </xs:sequence>
  17.       </xs:complexType>
  18.      </xs:element>
  19.      <xs:element name="MID_ELEMENT_B">
  20.       <xs:complexType>
  21.        <xs:sequence>
  22.         <xs:element name="TOP_ELEMENT_B_1">
  23.          BLAH
  24.         </xs:element>
  25.         <xs:element name="TOP_ELEMENT_B_2">
  26.          BLAH
  27.         </xs:element>
  28.         ...
  29.         ...
  30.        </xs:sequence>
  31.       </xs:complexType>
  32.      </xs:element>
  33.      ...
  34.      ...
  35.     </xs:sequence>
  36.    </xs:complexType>
  37.  </xs:element>
  38. </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]

Expand|Select|Wrap|Line Numbers
  1. declare
  2. cursor c1 is
  3.   select mid_element
  4.   from mid_element_table;
  5. V_MID_ELEM VARCHAR2(10) := 'MID_ELEMENT_A';
  6. BEGIN
  7.   for c1_rec in c1
  8.   loop
  9.     insert into top_element_table
  10.       (mid_element, top_element)
  11.     select c1_rec.mid_element,
  12.               value(t).extract('/xs:element/@name',
  13.                                'xmlns:xs="http://www.w3.org/2001/XMLSchema"')
  14.     from xsd_table x,
  15.          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',
  16.                                             'xmlns:xs="http://www.w3.org/2001/XMLSchema"'))) t;
  17. end loop;
  18. 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?
Sep 3 '07 #1
Share this Question
Share on Google+
1 Reply


jkmyoung
Expert 100+
P: 2,057
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 .
Sep 5 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.