0

I'm very new to Oracle XML and am trying to retrieve 2 rows of 4 columns of vehicle data.

SQL:

select extract(xml, 'VEHICLES/VEHICLE/NUM_VEHICLE') as NUM_VEHICLE, extract(xml, 'VEHICLES/VEHICLE/NUM_YEAR') as NUM_YEAR, extract(xml, 'VEHICLES/VEHICLE/DES_PLATE') as DES_PLATE, extract(xml, 'VEHICLES/VEHICLE/CDE_STATE') as CDE_STATE from XML_TAB_HISTORY t where t.id = 7

XML:

<VEHICLES> <VEHICLE> <NUM_VEHICLE>1</NUM_VEHICLE> <NUM_YEAR>2011</NUM_YEAR> <DES_PLATE>ABC123</DES_PLATE> <CDE_STATE>NJ</CDE_STATE> </VEHICLE> <VEHICLE> <NUM_VEHICLE>2</NUM_VEHICLE> <NUM_YEAR>2012</NUM_YEAR> <DES_PLATE>XYZ987</DES_PLATE> <CDE_STATE>NJ</CDE_STATE> </VEHICLE> </VEHICLES>

I'm getting 1 row of 4 XMLTYPEs:

<NUM_VEHICLE>1</NUM_VEHICLE> <NUM_VEHICLE>2</NUM_VEHICLE> <NUM_YEAR>2011</NUM_YEAR> <NUM_YEAR>2012</NUM_YEAR> <DES_PLATE>ABC123</DES_PLATE> <DES_PLATE>XYZ987</DES_PLATE> <CDE_STATE>NJ</CDE_STATE> <CDE_STATE>NJ</CDE_STATE>

What I'd like to get is 2 rows of values only:

NUM_VEHICLE NUM_YEAR DES_PLATE CDE_STATE ----------- -------- --------- --------- 1 2011 ABC123 NJ 2 2012 XYZ987 NJ

I tried using "extractvalue" in place of extract in the SQL, but received this error: ORA-19025: EXTRACTVALUE returns value of only one node.

Thanks in advance for any suggestions or pointers in the right direction.

Andrew

Improve this question

1 Answer 1

Reset to default
0

Both extract and extractvalue are deprecated, so you should be using xmlquery and, in this case, xmltable:

select num_vehicle, num_year, des_plate, cde_state from xml_tab_history t cross apply xmltable ( '/VEHICLES/VEHICLE' passing t.xml columns num_vehicle number path 'NUM_VEHICLE', num_year number path 'NUM_YEAR', des_plate varchar2(6) path 'DES_PLATE', cde_state varchar2(2) path 'CDE_STATE' ) x where t.id = 7 
NUM_VEHICLE NUM_YEAR DES_PLATE CDE_STATE ----------- -------- --------- --------- 1 2011 ABC123 NJ 2 2012 XYZ987 NJ

db<>fiddle

Read more about current SQL/XML functions.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Alex, just wanted to say your example was extremely helpful. I discovered I needed to link 2 more tables for a later query and with a little study figured out how to add another "cross" apply and an "outer" apply and pass child XMLTYPEs to get everything I needed.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.