Помогите распарсить xml

Pir
Дата: 16.03.2011 11:59:50
<NS1:ExchangeRateDownloadResponseIo xmlns:NS1="http://www.ru/CurrencyExchange">
  <NS1:exchagneRateRecords>
    <centerRate>22.37330000</centerRate>
    <currencyCode>AUD</currencyCode>
    <currencyUnit>1</currencyUnit>
    <exchangeRateTableTypeCode>CBR</exchangeRateTableTypeCode>
    <refCurrencyCode>RUR</refCurrencyCode>
    <refCurrencyUnit>1</refCurrencyUnit>
    <validFrom>2007-10-10</validFrom>
  </NS1:exchagneRateRecords>
  <NS1:exchagneRateRecords>
    <centerRate>50.94890000</centerRate>
    <currencyCode>GBP</currencyCode>
    <currencyUnit>1</currencyUnit>
    <exchangeRateTableTypeCode>CBR</exchangeRateTableTypeCode>
    <refCurrencyCode>RUR</refCurrencyCode>
    <refCurrencyUnit>1</refCurrencyUnit>
    <validFrom>2007-10-10</validFrom>
  </NS1:exchagneRateRecords>
</NS1:ExchangeRateDownloadResponseIo>

Помогите распарсить и преобразовать в таблицу. Проблема с неймспейсами, без них всё понятно
select 
 extractvalue(value(d), '/exchagneRateRecords/centerRate') col1,
 extractvalue(value(d), '/exchagneRateRecords/currencyCode') col2,
 extractvalue(value(d), '/exchagneRateRecords/currencyUnit') col3,
 extractvalue(value(d), '/exchagneRateRecords/validFrom') col4 
from table(XMLSequence(XMLType(
'<ExchangeRateDownloadResponseIo>
  <exchagneRateRecords>
    <centerRate>22.37330000</centerRate>
    <currencyCode>AUD</currencyCode>
    <currencyUnit>1</currencyUnit>
    <exchangeRateTableTypeCode>CBR</exchangeRateTableTypeCode>
    <refCurrencyCode>RUR</refCurrencyCode>
    <refCurrencyUnit>1</refCurrencyUnit>
    <validFrom>2007-10-10</validFrom>
  </exchagneRateRecords>
  <exchagneRateRecords>
    <centerRate>50.94890000</centerRate>
    <currencyCode>GBP</currencyCode>
    <currencyUnit>1</currencyUnit>
    <exchangeRateTableTypeCode>CBR</exchangeRateTableTypeCode>
    <refCurrencyCode>RUR</refCurrencyCode>
    <refCurrencyUnit>1</refCurrencyUnit>
    <validFrom>2007-10-10</validFrom>
  </exchagneRateRecords>
</ExchangeRateDownloadResponseIo>').extract('ExchangeRateDownloadResponseIo/exchagneRateRecords'))) d;

субд 9.2.0.8
Elic
Дата: 16.03.2011 12:05:38
В поиске забанили?
Pir
Дата: 16.03.2011 12:27:58
Elic,

ну ткни в рабочий пример)

так пустая выборка:
select 
 extractvalue(value(d), '/exchagneRateRecords/centerRate','xmlns:NS1="http://www.ru/CurrencyExchange"') col1
from table(XMLSequence(XMLType(
'<NS1:ExchangeRateDownloadResponseIo xmlns:NS1="http://www.ru/CurrencyExchange">
  <NS1:exchagneRateRecords>
    <centerRate>22.37330000</centerRate>
    <currencyCode>AUD</currencyCode>
    <currencyUnit>1</currencyUnit>
    <exchangeRateTableTypeCode>CBR</exchangeRateTableTypeCode>
    <refCurrencyCode>RUR</refCurrencyCode>
    <refCurrencyUnit>1</refCurrencyUnit>
    <validFrom>2007-10-10</validFrom>
  </NS1:exchagneRateRecords>
</NS1:ExchangeRateDownloadResponseIo>').extract('ExchangeRateDownloadResponseIo/exchagneRateRecords','xmlns:NS1="http://www.ru/CurrencyExchange"'))) d;
Maxim Demenko
Дата: 16.03.2011 12:57:25
Кроме декларации, namespace из xpath терять не следует.
select 
 extractvalue(value(d), '/NS1:exchagneRateRecords/centerRate','xmlns:NS1="http://www.ru/CurrencyExchange"') col1
from table(XMLSequence(XMLType(
'<NS1:ExchangeRateDownloadResponseIo xmlns:NS1="http://www.ru/CurrencyExchange">
  <NS1:exchagneRateRecords>
    <centerRate>22.37330000</centerRate>
    <currencyCode>AUD</currencyCode>
    <currencyUnit>1</currencyUnit>
    <exchangeRateTableTypeCode>CBR</exchangeRateTableTypeCode>
    <refCurrencyCode>RUR</refCurrencyCode>
    <refCurrencyUnit>1</refCurrencyUnit>
    <validFrom>2007-10-10</validFrom>
  </NS1:exchagneRateRecords>
</NS1:ExchangeRateDownloadResponseIo>').extract('NS1:ExchangeRateDownloadResponseIo/NS1:exchagneRateRecords','xmlns:NS1="http://www.ru/CurrencyExchange"'))) d;

Best regards

Maxim
Pir
Дата: 16.03.2011 13:12:36
Maxim Demenko,

ошибка

SQL> 
 
select
 extractvalue(value(d), '/NS1:exchagneRateRecords/centerRate','xmlns:NS1="http://www.ru/CurrencyExchange"') col1
from table(XMLSequence(XMLType(
'<NS1:ExchangeRateDownloadResponseIo xmlns:NS1="http://www.ru/CurrencyExchange">
  <NS1:exchagneRateRecords>
    <centerRate>22.37330000</centerRate>
    <currencyCode>AUD</currencyCode>
    <currencyUnit>1</currencyUnit>
    <exchangeRateTableTypeCode>CBR</exchangeRateTableTypeCode>
    <refCurrencyCode>RUR</refCurrencyCode>
    <refCurrencyUnit>1</refCurrencyUnit>
    <validFrom>2007-10-10</validFrom>
  </NS1:exchagneRateRecords>
</NS1:ExchangeRateDownloadResponseIo>').extract('NS1:ExchangeRateDownloadResponseIo/NS1:exchagneRateRecords','xmlns:NS1="http://www.ru/CurrencyExchange"'))) d
 
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00234: namespace prefix "NS1" is not declared
Error at line 1
Maxim Demenko
Дата: 16.03.2011 13:20:35
Pir,

Не знаю, под руками девятки нету, на 10.2.0.4 - работает
SQL> select * from v$version;

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

SQL> select
  2   extractvalue(value(d), '/NS1:exchagneRateRecords/centerRate'
  3  ,'xmlns:NS1="http://www.ru/CurrencyExchange"') col1
  4  from table(XMLSequence(XMLType(
  5  '<NS1:ExchangeRateDownloadResponseIo xmlns:NS1="http://www.ru/CurrencyExchange">
  6    <NS1:exchagneRateRecords>
  7      <centerRate>22.37330000</centerRate>
  8      <currencyCode>AUD</currencyCode>
  9      <currencyUnit>1</currencyUnit>
 10      <exchangeRateTableTypeCode>CBR</exchangeRateTableTypeCode>
 11      <refCurrencyCode>RUR</refCurrencyCode>
 12      <refCurrencyUnit>1</refCurrencyUnit>
 13      <validFrom>2007-10-10</validFrom>
 14    </NS1:exchagneRateRecords>
 15  </NS1:ExchangeRateDownloadResponseIo>'
 16  ).extract('NS1:ExchangeRateDownloadResponseIo/NS1:exchagneRateRecords',
 17  'xmlns:NS1="http://www.ru/CurrencyExchange"'))) d
 18  /

COL1
-----------------------------------------------------------------------------------------------------------------------------
22.37330000

Может баг какой с extract + потеря namespace, однозначно - в поиск.

Best regards

Maxim
Dmitry.
Дата: 16.03.2011 13:36:32
--с учетом неймспейсов
select *  
FROM XMLTABLE(XMLNAMESPACES ('http://www.ru/CurrencyExchange' AS "p"),'/p:ExchangeRateDownloadResponseIo/p:exchagneRateRecords'  
          PASSING   
XMLType(
'<NS1:ExchangeRateDownloadResponseIo xmlns:NS1="http://www.ru/CurrencyExchange">
  <NS1:exchagneRateRecords>
    <centerRate>22.37330000</centerRate>
    <currencyCode>AUD</currencyCode>
    <currencyUnit>1</currencyUnit>
    <exchangeRateTableTypeCode>CBR</exchangeRateTableTypeCode>
    <refCurrencyCode>RUR</refCurrencyCode>
    <refCurrencyUnit>1</refCurrencyUnit>
    <validFrom>2007-10-10</validFrom>
  </NS1:exchagneRateRecords>
  <NS1:exchagneRateRecords>
    <centerRate>50.94890000</centerRate>
    <currencyCode>GBP</currencyCode>
    <currencyUnit>1</currencyUnit>
    <exchangeRateTableTypeCode>CBR</exchangeRateTableTypeCode>
    <refCurrencyCode>RUR</refCurrencyCode>
    <refCurrencyUnit>1</refCurrencyUnit>
    <validFrom>2007-10-10</validFrom>
  </NS1:exchagneRateRecords>
</NS1:ExchangeRateDownloadResponseIo>' 
)
          COLUMNS  
             --describe columns and path to them:  
             centerRate   numeric(20,10)    PATH 'centerRate',  
             currencyCode varchar2(10)      PATH 'currencyCode'
      ) xmlt  
/
--без учета неймспейсов
select *  
FROM XMLTABLE('/*/*'  
          PASSING   
XMLType(
'<NS1:ExchangeRateDownloadResponseIo xmlns:NS1="http://www.ru/CurrencyExchange">
  <NS1:exchagneRateRecords>
    <centerRate>22.37330000</centerRate>
    <currencyCode>AUD</currencyCode>
    <currencyUnit>1</currencyUnit>
    <exchangeRateTableTypeCode>CBR</exchangeRateTableTypeCode>
    <refCurrencyCode>RUR</refCurrencyCode>
    <refCurrencyUnit>1</refCurrencyUnit>
    <validFrom>2007-10-10</validFrom>
  </NS1:exchagneRateRecords>
  <NS1:exchagneRateRecords>
    <centerRate>50.94890000</centerRate>
    <currencyCode>GBP</currencyCode>
    <currencyUnit>1</currencyUnit>
    <exchangeRateTableTypeCode>CBR</exchangeRateTableTypeCode>
    <refCurrencyCode>RUR</refCurrencyCode>
    <refCurrencyUnit>1</refCurrencyUnit>
    <validFrom>2007-10-10</validFrom>
  </NS1:exchagneRateRecords>
</NS1:ExchangeRateDownloadResponseIo>' 
)
          COLUMNS  
             --describe columns and path to them:  
             centerRate   numeric(20,10)    PATH 'centerRate',  
             currencyCode varchar2(10)      PATH 'currencyCode'
      ) xmlt  
/
Dmitry.
Дата: 16.03.2011 13:37:24
про девятку не дочитал - тоже нет.
Pir
Дата: 16.03.2011 14:05:46
Спасибо всем ответившим.
Не дружит девятка с неймспейсами в некоторых случаях.
Хотя такой extract в pl/sql работает:

  v_response := v_response.extract('/NS1:Envelope/NS1:Body/child::node()', 'xmlns:NS1="http://schemas.xmlsoap.org/soap/envelope/"');
  v_response := v_response.extract('/NS1:ExchangeRateDownloadResponseIo/NS1:exchagneRateRecords/child::node()', 'xmlns:NS1="http://www.ru/CurrencyExchange"');