как вытащить данные с xml

Question

всем привет есть xml код он выдает нулл как вытащить данные с period ? hi guys !!! , this code return null and how can i extract 012019-122019 from period ?

with qwe as (
select  xmltype('<singleAggregatePaymentResponse xmlns:ns7="http://www.mtszn.kz/services/PersonCotracts/schemas">
                  <ns7:isEspPayer xmlns="http://www.mtszn.kz/services/PersonCotracts/schemas">true</ns7:isEspPayer>

                  <ns7:periods xmlns="http://www.mtszn.kz/services/PersonCotracts/schemas">
                     <period>122019</period>
                     <period>112019</period>
                     <period>102019</period>
                     <period>092019</period>
                     <period>082019</period>
                     <period>072019</period>
                     <period>062019</period>
                     <period>052019</period>
                     <period>042019</period>
                     <period>032019</period>
                     <period>022019</period>
                     <period>012019</period>
                  </ns7:periods>

               </singleAggregatePaymentResponse>')xml
               from dual
               )

              select
              extractvalue(value(x101_1),'/period') iou,
               xml

                from qwe q,
                     table(xmlsequence(q.xml.extract('//period')))(+) x101_1

Answer 1

На SQL Fiddle:

with tbl as
(
    select
        XMLType(
        '<singleAggregatePaymentResponse xmlns:ns7="http://www.mtszn.kz/services/PersonCotracts/schemas">
            <ns7:isEspPayer xmlns="http://www.mtszn.kz/services/PersonCotracts/schemas">true</ns7:isEspPayer>
            <ns7:periods xmlns="http://www.mtszn.kz/services/PersonCotracts/schemas">
                <period>122019</period>
                <period>112019</period>
                <period>102019</period>
                <period>092019</period>
                <period>082019</period>
                <period>072019</period>
                <period>062019</period>
                <period>052019</period>
                <period>042019</period>
                <period>032019</period>
                <period>022019</period>
                <period>012019</period>
            </ns7:periods>
        </singleAggregatePaymentResponse>'
        ) xmldata
    from
        dual
)
select period
from   tbl,
       xmltable(
        xmlnamespaces('http://www.mtszn.kz/services/PersonCotracts/schemas' as "ns1"),
        '/singleAggregatePaymentResponse/ns1:periods/ns1:period'
                PASSING tbl.xmldata 
                COLUMNS period VARCHAR2(10) PATH '.');

Вывод:

+--------+
| PERIOD |
+--------+
| 122019 |
| 112019 |
| 102019 |
| 092019 |
| 082019 |
| 072019 |
| 062019 |
| 052019 |
| 042019 |
| 032019 |
| …      |
+--------+

БЛОГ НА HUSL

как вытащить данные с xml

Ответы (1 шт):