Hi all,
I'm facing difficulties with namespaces when reading XML to database; xml is 
like this;

<?xml version="1.0" encoding="ISO-8859-1"?>
<?xml-stylesheet type="text/xsl" href="../oper_fct.xsl"?>
<test abc:thisone="O1" xmlns="abc" xmlns:abc="abc" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="abc ../abc_abc.xsd">
  <blaa>
    ABCABC
   </blaa>
</test>

Problem is to read out that attribute "abc:thisone"; I've tried many ways to 
get it in PL/SQL  like
.....
xlsprocessor.valueof(l_node, '@abc:thisone')
....
and I've tried
....
xlsprocessor.valueof(l_node, '@thisone')
....

What is the right way to get it ???
Any help would be appreciated,
BR,
Timo

timo schrieb:
> Hi all,
> I'm facing difficulties with namespaces when reading XML to database; xml is 
> like this;
> 
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <?xml-stylesheet type="text/xsl" href="../oper_fct.xsl"?>
> <test abc:thisone="O1" xmlns="abc" xmlns:abc="abc" 
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
> xsi:schemaLocation="abc ../abc_abc.xsd">
>   <blaa>
>     ABCABC
>    </blaa>
> </test>
> 
> Problem is to read out that attribute "abc:thisone"; I've tried many ways to 
> get it in PL/SQL  like
> ....
> xlsprocessor.valueof(l_node, '@abc:thisone')
> ...
> and I've tried
> ...
> xlsprocessor.valueof(l_node, '@thisone')
> ...
> 
> What is the right way to get it ???
> Any help would be appreciated,
> BR,
> Timo
> 
> 
> 

You should specify the namespaces you are using. You can do it in plain 
sql like this:

SQL> with t as (
   2  select xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>
   3  <?xml-stylesheet type="text/xsl" href="../oper_fct.xsl"?>
   4  <test abc:thisone="O1" xmlns="abc" xmlns:abc="abc"
   5  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   6  xsi:schemaLocation="abc ../abc_abc.xsd">
   7    <blaa>
   8      ABCABC
   9     </blaa>
  10  </test>') x from dual)
  11  select extractvalue(x,'//@abc:thisone','xmlns:abc="abc"') attr
  12  from t
  13  ;

ATTR
----------
O1


If your program logic requires the use of xlsprocessor.valueof , then 
you should specify your namespaces in that call as well ( it accepts a 
namespace parameter).


Best regards

Maxim

Hi Maxim,
thanks; your solution works, great !
Still I should read with "xlsprocessor.valueof"  and still I haven't found a 
way to define namespace to it in PL/SQL; should it be like
....
xlsprocessor.valueof(l_node,'@thisone','abc')
or
xlsprocessor.valueof(l_node,'@abc:thisone','abc')
or
xlsprocessor.valueof(l_node,'@thisone','xmlns=abc="abc"')
or what....???
If you've got an anwer for this please do let me know it ,
BR,
Timo

"Maxim Demenko" <mdemenko@gmail.com> wrote in message 
news:49B254BC.1010001@gmail.com...
> timo schrieb:
>> Hi all,
>> I'm facing difficulties with namespaces when reading XML to database; xml 
>> is like this;
>>
>> <?xml version="1.0" encoding="ISO-8859-1"?>
>> <?xml-stylesheet type="text/xsl" href="../oper_fct.xsl"?>
>> <test abc:thisone="O1" xmlns="abc" xmlns:abc="abc" 
>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
>> xsi:schemaLocation="abc ../abc_abc.xsd">
>>   <blaa>
>>     ABCABC
>>    </blaa>
>> </test>
>>
>> Problem is to read out that attribute "abc:thisone"; I've tried many ways 
>> to get it in PL/SQL  like
>> ....
>> xlsprocessor.valueof(l_node, '@abc:thisone')
>> ...
>> and I've tried
>> ...
>> xlsprocessor.valueof(l_node, '@thisone')
>> ...
>>
>> What is the right way to get it ???
>> Any help would be appreciated,
>> BR,
>> Timo
>>
>>
>>
>
> You should specify the namespaces you are using. You can do it in plain 
> sql like this:
>
> SQL> with t as (
>   2  select xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>
>   3  <?xml-stylesheet type="text/xsl" href="../oper_fct.xsl"?>
>   4  <test abc:thisone="O1" xmlns="abc" xmlns:abc="abc"
>   5  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>   6  xsi:schemaLocation="abc ../abc_abc.xsd">
>   7    <blaa>
>   8      ABCABC
>   9     </blaa>
>  10  </test>') x from dual)
>  11  select extractvalue(x,'//@abc:thisone','xmlns:abc="abc"') attr
>  12  from t
>  13  ;
>
> ATTR
> ----------
> O1
>
>
> If your program logic requires the use of xlsprocessor.valueof , then you 
> should specify your namespaces in that call as well ( it accepts a 
> namespace parameter).
>
>
> Best regards
>
> Maxim 

timo schrieb:
> Hi Maxim,
> thanks; your solution works, great !
> Still I should read with "xlsprocessor.valueof"  and still I haven't found a 
> way to define namespace to it in PL/SQL; should it be like
> ...
> xlsprocessor.valueof(l_node,'@thisone','abc')
> or
> xlsprocessor.valueof(l_node,'@abc:thisone','abc')
> or
> xlsprocessor.valueof(l_node,'@thisone','xmlns=abc="abc"')
> or what....???
> If you've got an anwer for this please do let me know it ,
> BR,
> Timo


This works for me:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> declare
   2    xsldoc     varchar2(2000);
   3    l_parser   dbms_xmlparser.parser;
   4    xsltdomdoc dbms_xmldom.domdocument;
   5    xsl        dbms_xslprocessor.stylesheet;
   6    xsltnode   dbms_xmldom.domnode;
   7    l_node     varchar2(2000);
   8  begin
   9    xsldoc := '<?xml version="1.0" encoding="ISO-8859-1"?>
  10  <?xml-stylesheet type="text/xsl" href="../oper_fct.xsl"?>
  11  <test abc:thisone="O1" xmlns="abc" xmlns:abc="abc"
  12  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  13  xsi:schemaLocation="abc ../abc_abc.xsd">
  14    <blaa>
  15      ABCABC
  16     </blaa>
  17  </test>';
  18
  19    l_parser := dbms_xmlparser.newparser;
  20    dbms_xmlparser.parsebuffer(l_parser, xsldoc);
  21    xsltdomdoc := dbms_xmlparser.getdocument(l_parser);
  22    xsltnode   := dbms_xmldom.makenode(xsltdomdoc);
  23    l_node     := xslprocessor.valueof(xsltnode,
  24                                       '//@abc:thisone',
  25                                       'xmlns:abc="abc"');
  26    dbms_output.put_line(l_node);
  27  end;
  28  /
O1


Best regards

Maxim