Need to read value of multiple node: ORA-19025: EXTRACTVALUE returns value of only one node
I can successfully read from xml file : DataTransfer_HH_TWWholesale_001_004_12142020113003.xml which has only one node.
But when I am trying to read multiple node from xml file: DataTransfer_HH_TWWholesale_001_009_09282020103349.xml , I get errorORA-19025: EXTRACTVALUE returns value of only one node.
How can I read values from multiple node?
Below is my file and code:
File: DataTransfer_HH_TWWholesale_001_004_12142020113003.xml
<MeterReadsReplyMessage xmlns="http://www.emeter.com/energyip/amiinterface">
<Header>
<verb>create</verb>
<noun>DTSMeterReads</noun>
<revision>2</revision>
<source>EIP</source>
</Header>
<payload>
<MeterReading>
<ServiceDeliveryPoint>
<mRID>901291331_0001</mRID>
<idType>SDP_X_UDC_ASSET_ID</idType>
</ServiceDeliveryPoint>
<Meter>
<mRID>SIE_640 C_310149563</mRID>
<idType>METER_X_UDC_ASSET_ID</idType>
</Meter>
<IntervalBlock>
<readingTypeId>LREG</readingTypeId>
<ReadingType>
<measurementType>Register</measurementType>
<touBinNumber>0</touBinNumber>
<unit>L</unit>
<channelNumber>1</channelNumber>
<direction>Delivered</direction>
</ReadingType>
<IReading>
<endTime>2020-10-08T00:00:00.000Z</endTime>
<value>0.0</value>
<quality>
<validationStatus>EST</validationStatus>
<locked>false</locked>
</quality>
</IReading>
</IntervalBlock>
</MeterReading>
</payload>
</MeterReadsReplyMessage>
Code:
SELECT EXTRACTVALUE (VALUE (a1),'/Header/verb','xmlns="http://www.emeter.com/energyip/amiinterface')verb,
EXTRACTVALUE (VALUE (a1),'/Header/noun','xmlns="http://www.emeter.com/energyip/amiinterface')noun,
EXTRACTVALUE (VALUE (a1),'/Header/source','xmlns="http://www.emeter.com/energyip/amiinterface')source
FROM xml_tab,TABLE (XMLSEQUENCE (EXTRACT (xml_data,
'/MeterReadsReplyMessage/Header',
'xmlns="http://www.emeter.com/energyip/amiinterface"'
))) a1
WHERE file_name = 'DataTransfer_HH_TWWholesale_001_004_12142020113003.xml';
Query:
File: DataTransfer_HH_TWWholesale_001_009_09282020103349.xml
<MeterReadsReplyMessage xmlns="http://www.emeter.com/energyip/amiinterface">
<Header>
<verb>create</verb>
<noun>DTSMeterReads</noun>
<revision>2</revision>
<source>EIP</source>
</Header>
<payload>
<MeterReading>
<ServiceDeliveryPoint>
<mRID>901291331_0001</mRID>
<idType>SDP_X_UDC_ASSET_ID</idType>
</ServiceDeliveryPoint>
<Meter>
<mRID>SIE_640 C_310149563</mRID>
<idType>METER_X_UDC_ASSET_ID</idType>
</Meter>
<IntervalBlock>
<readingTypeId>LREG</readingTypeId>
<ReadingType>
<measurementType>Register</measurementType>
<touBinNumber>0</touBinNumber>
<unit>L</unit>
<channelNumber>1</channelNumber>
<direction>Delivered</direction>
</ReadingType>
<IReading>
<endTime>2020-10-08T00:00:00.000Z</endTime>
<value>0.0</value>
<quality>
<validationStatus>EST</validationStatus>
<locked>false</locked>
</quality>
</IReading>
</IntervalBlock>
</MeterReading>
<MeterReading>
<ServiceDeliveryPoint>
<mRID>112448526_0001</mRID>
<idType>SDP_X_UDC_ASSET_ID</idType>
</ServiceDeliveryPoint>
<Meter>
<mRID>SCE_640 Concentric_310037947</mRID>
<idType>METER_X_UDC_ASSET_ID</idType>
</Meter>
<IntervalBlock>
<readingTypeId>LREG</readingTypeId>
<ReadingType>
<measurementType>Register</measurementType>
<touBinNumber>0</touBinNumber>
<unit>L</unit>
<channelNumber>1</channelNumber>
<direction>Delivered</direction>
</ReadingType>
<IReading>
<endTime>2015-12-21T01:00:00.000Z</endTime>
<value>0.0</value>
<flags>0</flags>
<quality>
<validationStatus>VAL</validationStatus>
<locked>false</locked>
</quality>
</IReading>
</IntervalBlock>
</MeterReading>
</payload>
</MeterReadsReplyMessage>
query:
Thanks for reading my question.
Expected result:
mRID mfg_serial_num readingTypeId measurementType Read_time
901291331_0001 SIE_640 C_310149563 LREG Register 2020-10-08T00:00:00.000Z
112448526_0001 SCE_640 Concentric_310037947 LREG Register 2015-12-21T01:00:00.000Z
Solution
The EXTRACT and EXTRACTVALUE XML functions are deprecated. Use XMLTABLE instead:
SELECT t.file_name,
x.*
FROM xml_tab t
CROSS APPLY XMLTABLE(
XMLNAMESPACES( DEFAULT 'http://www.emeter.com/energyip/amiinterface' ),
'/MeterReadsReplyMessage/payload/MeterReading'
PASSING t.xml_data
COLUMNS
mRID VARCHAR2(20) PATH './ServiceDeliveryPoint/mRID',
mfg_serial_num VARCHAR2(20) PATH './Meter/mRID',
readingTypeID VARCHAR2(10) PATH './IntervalBlock/readingTypeId',
measurement_type VARCHAR2(10) PATH './IntervalBlock/ReadingType/measurementType',
reading_time TIMESTAMP WITH TIME ZONE PATH '//IReading/endTime'
) x;
Which, for your sample data:
CREATE TABLE xml_tab ( file_name, xml_data ) AS
SELECT 'DataTransfer_HH_TWWholesale_001_004_12142020113003.xml',
XMLTYPE(
'<MeterReadsReplyMessage xmlns="http://www.emeter.com/energyip/amiinterface">
<Header>
<verb>create</verb>
<noun>DTSMeterReads</noun>
<revision>2</revision>
<source>EIP</source>
</Header>
<payload>
<MeterReading>
<ServiceDeliveryPoint>
<mRID>901291331_0001</mRID>
<idType>SDP_X_UDC_ASSET_ID</idType>
</ServiceDeliveryPoint>
<Meter>
<mRID>SIE_640 C_310149563</mRID>
<idType>METER_X_UDC_ASSET_ID</idType>
</Meter>
<IntervalBlock>
<readingTypeId>LREG</readingTypeId>
<ReadingType>
<measurementType>Register</measurementType>
<touBinNumber>0</touBinNumber>
<unit>L</unit>
<channelNumber>1</channelNumber>
<direction>Delivered</direction>
</ReadingType>
<IReading>
<endTime>2020-10-08T00:00:00.000Z</endTime>
<value>0.0</value>
<quality>
<validationStatus>EST</validationStatus>
<locked>false</locked>
</quality>
</IReading>
</IntervalBlock>
</MeterReading>
</payload>
</MeterReadsReplyMessage>'
) FROM DUAL UNION ALL
SELECT 'DataTransfer_HH_TWWholesale_001_009_00000000000000.xml',
XMLType( '<MeterReadsReplyMessage xmlns="http://www.emeter.com/energyip/amiinterface">
<Header>
<verb>create</verb>
<noun>DTSMeterReads</noun>
<revision>2</revision>
<source>EIP</source>
</Header>
<payload>
<MeterReading>
<ServiceDeliveryPoint>
<mRID>901291331_0001</mRID>
<idType>SDP_X_UDC_ASSET_ID</idType>
</ServiceDeliveryPoint>
<Meter>
<mRID>SIE_640 C_310149563</mRID>
<idType>METER_X_UDC_ASSET_ID</idType>
</Meter>
<IntervalBlock>
<readingTypeId>LREG</readingTypeId>
<ReadingType>
<measurementType>Register</measurementType>
<touBinNumber>0</touBinNumber>
<unit>L</unit>
<channelNumber>1</channelNumber>
<direction>Delivered</direction>
</ReadingType>
<IReading>
<endTime>2020-10-08T00:00:00.000Z</endTime>
<value>0.0</value>
<quality>
<validationStatus>EST</validationStatus>
<locked>false</locked>
</quality>
</IReading>
</IntervalBlock>
</MeterReading>
<MeterReading>
<ServiceDeliveryPoint>
<mRID>112448526_0001</mRID>
<idType>SDP_X_UDC_ASSET_ID</idType>
</ServiceDeliveryPoint>
<Meter>
<mRID>SCE_640 Concentric_310037947</mRID>
<idType>METER_X_UDC_ASSET_ID</idType>
</Meter>
<IntervalBlock>
<readingTypeId>LREG</readingTypeId>
<ReadingType>
<measurementType>Register</measurementType>
<touBinNumber>0</touBinNumber>
<unit>L</unit>
<channelNumber>1</channelNumber>
<direction>Delivered</direction>
</ReadingType>
<IReading>
<endTime>2015-12-21T01:00:00.000Z</endTime>
<value>0.0</value>
<flags>0</flags>
<quality>
<validationStatus>VAL</validationStatus>
<locked>false</locked>
</quality>
</IReading>
</IntervalBlock>
</MeterReading>
</payload>
</MeterReadsReplyMessage>'
) FROM DUAL;
Outputs:
FILE_NAME | MRID | MFG_SERIAL_NUM | READINGTYPEID | MEASUREMENT_TYPE | READING_TIME :----------------------------------------------------- | :------------- | :------------------- | :------------ | :--------------- | :---------------------------------- DataTransfer_HH_TWWholesale_001_004_12142020113003.xml | 901291331_0001 | SIE_640 C_310149563 | LREG | Register | 2020-10-08T00:00:00.000000000+00:00 DataTransfer_HH_TWWholesale_001_009_00000000000000.xml | 901291331_0001 | SIE_640 C_310149563 | LREG | Register | 2020-10-08T00:00:00.000000000+00:00 DataTransfer_HH_TWWholesale_001_009_00000000000000.xml | 112448526_0001 | SCE_640 Concentric_3 | LREG | Register | 2015-12-21T01:00:00.000000000+00:00
db<>fiddle here
- Oracle JSON Query with hyphen-key
- How to change Oracle's Enterprise manager 12c runtime properties from a webservice call?
- Query all views for specific text in Oracle database views
- SQL query not fetching correct number of records
- Get 12 month running total for subgroups in Oracle
- Convert the total amount in words
- Oracle.ManagedDataAccess and ORA-01017: invalid username/password; logon denied
- When the same alias is used twice, which one will subsequent references point to?
- How to put empty string in JSON_OBJECT_T object?
- Need to read value of multiple node: ORA-19025: EXTRACTVALUE returns value of only one node
- Oracle hierarchical query joining 2 tables
- Converting timestamp to date format in Oracle 12c
- ORA-28040: No matching authentication protocol exception
- BI-Publisher RTF Template: Repeat colums or rows horizontally first before new page
- Oracle SQL find max number before repeating values start
- oracle 12c forms taking timezone -5:30
- Generate Nested JSON oracle19c using JSON functions
- how to query a different view depending on oracle version
- MIGRATING ORACLE FORMS 6I TO 12C
- How to update JSON column with new attribute value?
- Connecting to Oracle19C Server using Oracle11g client
- How to get the number of elements in a JSON array stored as CLOB with Oracle 12c?
- Pivot statement randomly produces NULL valued columns (Oracle 12c)
- Oracle Error- ORA-28374: typed master key not found in wallet
- Oracle's SDO_NET.FIND_CONNECTED_COMPONENTS fails with 'wrong number or types of arguments in call'
- Oracle catch exception code given at runtime
- Return results of a sql query as JSON in oracle 12c
- APEX_WEB_SERVICE.MAKE_REST_REQUEST results in ORA-29248: an unrecognized WRL was used to open a wallet
- Oracle 12c docker setup on Apple M1
- Oracle 12c impdp ORA-12154: TNS:could not resolve the connect identifier specified