oracleplsqloracle10gxmltableoracle-xml-db

Reading XML element occurrences using PL SQL


I have this XML.

<a>
   <b>b1</b>
   <c>c1</c>
   <b>b2</b>
   <c>c2</c2>
</a>

I want to be able to extract the values of elements 'b' and 'c' using PL\SQL. I'm using Oracle 10g.

So far I have this,

  SELECT   XML.b                    
       , XML.c
    FROM XMLTable (
           '/a' PASSING p_xml
              COLUMNS
                 b            VARCHAR(2) PATH 'b/.'
               , c            VARCHAR(2) PATH 'c/.'
    ) XML

But I keep getting this error:

19279. 00000 -  "XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence" 
*Cause:    The XQuery sequence passed in had more than one item.
*Action:   Correct the XQuery expression to return a single item sequence.

Then I tried this:

  SELECT   XML.b                    
         , XML1.c
    FROM XMLTable (
           '/a/b' PASSING p_xml
              COLUMNS
                 b            VARCHAR(2) PATH '.'
    ) XML,
        XMLTable (
           '/a/c' PASSING p_xml
              COLUMNS
                 c            VARCHAR(2) PATH '.'
    ) XML1

But the results were:

b1,c1
b1,c2
b2,c1
b2,c2

When I want only: b1 c1 b2 c2

Can you guys help me out?


Solution

  • I didnt run it but as far as i see below code should work ;

    DECLARE
    vs_Xml VARCHAR2(32000):= '<INPUT>
      <A>
        <B>1</B>
      </A>
      <A>
        <B>2</B>
      </A>
    </INPUT>';
    
    vx_ParameterList   XMLTYPE;
    vx_Parameter       XMLTYPE;
    vn_ParameterIndex  NUMBER;
    vs_Key             VARCHAR2(64);
    vs_XPath           VARCHAR2(255);
    vs_Value           VARCHAR2(10000);
    
    BEGIN
    vx_ParameterList := xmltype(vs_Xml);
    vn_ParameterIndex := 1;
    vs_XPath := '/INPUT/A'; 
    
    WHILE vx_ParameterList.existsNode(vs_XPath || '[' || vn_ParameterIndex || ']') = 1 LOOP
      vx_Parameter := vx_ParameterList.extract(vs_XPath || '[' || vn_ParameterIndex || ']');
    
      vs_Value := vx_Parameter.extract('//B/text()').GetStringVal();
      vn_ParameterIndex := vn_ParameterIndex + 1;
    
      dbms_output.put_line(vs_Value);
    END