SQL & PL/SQL

Below is my script that you can run successfully, and i have version Oracle 11g.
Script has Xml namespace as constant at the moment there:

   xmlnamespaces(default 'http://elion.ee/webservices/Sales/Dynamics')

I want to have a variable there instead, like this:

   xmlnamespaces(default    l_sSOAP_Namespace )

But if i add such variable there i get such error:

PL/SQL: ORA-19102: XQuery string literal expected

Can i avoid such error somehow and use still the namespace as variable somehow, not the constant?


----

My script:

declare
   l_resp varchar2(4000);
   l_sSOAP_Namespace varchar2(4000) := 'http://elion.ee/webservices/Sales/Dynamics';
begin
  l_resp :=
  '<ns0:FindItemMetaDataResponse xmlns:ns0="http://elion.ee/webservices/Sales/Dynamics">
        <ns0:ItemMetaData>
          <ns0:ItemMetaData>
            <ns0:ItemGroupId>IT.LS.VS.DSL</ns0:ItemGroupId>
            <ns0:ItemGroupName>IT lisad võrguseadmed DSL</ns0:ItemGroupName>
            <ns0:ItemId>DSLGP603</ns0:ItemId>
            <ns0:ItemName>ADSL SIP ST546</ns0:ItemName>
           <ns0:ItemType>Item</ns0:ItemType>
           <ns0:MacAddressMandatory>No</ns0:MacAddressMandatory>
           <ns0:SalesUnit>tk</ns0:SalesUnit>
           <ns0:SerialNumMandatory>No</ns0:SerialNumMandatory>
           <ns0:TaxValue>20.00</ns0:TaxValue>
         </ns0:ItemMetaData>
         <ns0:ItemMetaData>
           <ns0:CurrencyCode>EUR</ns0:CurrencyCode>
           <ns0:ItemGroupId>KL.PS.HGW</ns0:ItemGroupId>
           <ns0:ItemGroupName>Ruuterid</ns0:ItemGroupName>
           <ns0:ItemId>DSLGP603NY</ns0:ItemId>
           <ns0:ItemName>ADSL stardikomplekt Thomson ST546 stardi</ns0:ItemName>
           <ns0:ItemType>Item</ns0:ItemType>
           <ns0:MacAddressMandatory>No</ns0:MacAddressMandatory>
           <ns0:PriceWithoutVAT>12.78</ns0:PriceWithoutVAT>
           <ns0:PriceWithVAT>15.34</ns0:PriceWithVAT>
           <ns0:SalesUnit>tk</ns0:SalesUnit>
           <ns0:SerialNumMandatory>Yes</ns0:SerialNumMandatory>
           <ns0:TaxValue>20.00</ns0:TaxValue>
         </ns0:ItemMetaData>
       </ns0:ItemMetaData>
     </ns0:FindItemMetaDataResponse>';
  
  for rec in
  (
     with xml_doc (doc) as (
        select xmlparse(document
        l_resp)
       from dual
     )
     select x.*
     from xml_doc t
        , xmltable (
            xmlnamespaces(default 'http://elion.ee/webservices/Sales/Dynamics')
          , '/FindItemMetaDataResponse/ItemMetaData/ItemMetaData'
            passing t.doc
            columns 
            CurrencyCode varchar2(4000) path 'CurrencyCode', 
            ItemGroupId varchar2(4000) path 'ItemGroupId', 
            ItemGroupName varchar2(4000) path 'ItemGroupName', 
            ItemId varchar2(4000) path 'ItemId', 
            ItemName varchar2(4000) path 'ItemName', 
            ItemType varchar2(4000) path 'ItemType', 
            MacAddressMandatory varchar2(4000) path 'MacAddressMandatory', 
            PriceWithoutVAT varchar2(4000) path 'PriceWithoutVAT', 
            PriceWithVAT varchar2(4000) path 'PriceWithVAT', 
            SalesUnit varchar2(4000) path 'SalesUnit', 
            SerialNumMandatory varchar2(4000) path 'SerialNumMandatory', 
            TaxValue varchar2(4000) path 'TaxValue'          
          ) x
  ) loop
    dbms_output.put_line('ItemId=' || rec.ItemId);
  end loop;
end;
/* Output:
ItemId=DSLGP603
ItemId=DSLGP603NY
*/

Edited by: CharlesRoos on 11.04.2013 14:46