title: XMLTABLE in oracle - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
body: <pre><code>Declare
  lv_xmldata XMLType := XMLType('
  &lt;ProviderGroupScopeOfPractice&gt;
  &lt;Records&gt;
    &lt;Record bgid="1" ggid="39"/&gt;
    &lt;Record bgid="2" ggid="0"/&gt;
    &lt;Record bgid="3" ggid="0"/&gt;
    &lt;Record bgid="1" ggid="35"/&gt;
  &lt;/Records&gt;
  &lt;/ProviderGroupScopeOfPractice&gt;
  ');
  
  v_provider_Group_Id  Number(10);
  v_resource_Id Number(10);
  v_count Number (10);
  p_region_id number(5); 
  p_revision_id number(5);
  p_bg_revision_id number(5);
  p_scp_classification_id number(3);
  p_facility_id VARCHAR2(3);
  p_provider_group_id Number(10);
  temp number;
  
  
  Begin
    p_region_id :=51; 
    p_revision_id :=53; 
    p_bg_revision_id :=1; 
    p_scp_classification_id:= 2; 
    p_facility_id :='STR'; 
    p_provider_group_id := 211;
  
  INSERT
  INTO BG_GDLINE_SCOPE_GROUP_XREF
    (
      REGION_ID,
      REVISION_ID,
      BG_REVISION_ID,
      FACILITY_ID,
      SCP_CLASSIFICATION_ID,
      PROVIDER_GROUP_ID,
      BG_CLASSIFICATION_ID,
      GUIDELINE_GROUP_ID,
      CREATE_USER_ID,
      CREATE_TS,
      UPDATE_USER_ID,
      UPDATE_TS
    )
    SELECT
        p.p_region_id,
        p.p_revision_id,
        p.p_bg_revision_id,
        p.p_facility_id,
        p.p_scp_classification_id,
        p.p_provider_group_id,      
        bgDetails.bgid,
        bgDetails.ggid,  
        'user1',
        Sysdate,
        'user1',
        Sysdate
    FROM ( SELECT p_region_id,
              p_revision_id,
              p_bg_revision_id,
              p_facility_id,
              p_scp_classification_id,
              p_provider_group_id
        FROM DUAL) p, 
        (SELECT b.bgid, b.ggid
          FROM XMLTABLE(
                  '/ProviderGroupScopeOfPractice/Records' 
                  PASSING lv_xmldata
                  columns bgid number(3) PATH 'Record/@bgid' ,
                   ggid number(5) PATH 'Record/@ggid' 
                  ) b
        ) bgDetails
               ;
  end;
  </code></pre>
  
  <p>I get error as:</p>
  
  <pre><code>Error report:
  ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got     multi-item sequence
  ORA-06512: at line 33
  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.
  </code></pre>
  
  <p>Please advice...</p>
  
  <p>Thanks</p>