I am having some trouble with the extractvalue function.
create table tony_xml
(x xmltype);
insert into tony_xml
values
('<FacilitySiteList>
<FacilitySite>
<FS_ID>1519876</FS_ID>
<COMMON_NM>Austin Co</COMMON_NM>
<LINE_1_AD>1601 LIND AVE SW </LINE_1_AD>
<CITY_NM>RENTON </CITY_NM>
<GIS_VER_IND_CD>Y</GIS_VER_IND_CD>
<STATE_CD>WA</STATE_CD>
<ZIP_CD>98055 </ZIP_CD>
<GIS_CALC_LAT_DECIMAL_NR>47.46504</GIS_CALC_LAT_DECIMAL_NR>
<GIS_CALC_LONG_DECIMAL_NR>122.22355</GIS_CALC_LONG_DECIMAL_NR>
<LAST_UPD_DT>1997-06-26T00:00:00-07:00</LAST_UPD_DT>
</FacilitySite>
<FacilitySite>
<FS_ID>3697762</FS_ID>
<COMMON_NM>Austin Farm</COMMON_NM>
<LINE_1_AD>320 Elma Gate Rd E </LINE_1_AD>
<CITY_NM>Oakville </CITY_NM>
<GIS_VER_IND_CD>Y</GIS_VER_IND_CD>
<STATE_CD>WA</STATE_CD>
<ZIP_CD>98568-9614</ZIP_CD>
<GIS_CALC_LAT_DECIMAL_NR>46.832520217201633</GIS_CALC_LAT_DECIMAL_NR>
<GIS_CALC_LONG_DECIMAL_NR>123.18333821662898</GIS_CALC_LONG_DECIMAL_NR>
<LAST_UPD_DT>2002-08-29T11:21:23.08-07:00</LAST_UPD_DT>
</FacilitySite>
<FacilitySite>
<FS_ID>2049</FS_ID>
<COMMON_NM>AUSTIN MACK</COMMON_NM>
<LINE_1_AD>2739 6TH AVE S </LINE_1_AD>
<CITY_NM>SEATTLE </CITY_NM>
<GIS_VER_IND_CD>Y</GIS_VER_IND_CD>
<STATE_CD>WA</STATE_CD>
<ZIP_CD>98134 </ZIP_CD>
<GIS_CALC_LAT_DECIMAL_NR>47.57915</GIS_CALC_LAT_DECIMAL_NR>
<GIS_CALC_LONG_DECIMAL_NR>122.32588</GIS_CALC_LONG_DECIMAL_NR>
<LAST_UPD_DT>1999-03-19T00:00:00-07:00</LAST_UPD_DT>
</FacilitySite>
<FacilitySite>
<FS_ID>1634623</FS_ID>
<COMMON_NM>Austin Mike</COMMON_NM>
<LINE_1_AD>16407 N SUNRISE DR </LINE_1_AD>
<CITY_NM>NINE MILE FALLS </CITY_NM>
<GIS_VER_IND_CD>Y</GIS_VER_IND_CD>
<STATE_CD>WA</STATE_CD>
<ZIP_CD>99026 </ZIP_CD>
<GIS_CALC_LAT_DECIMAL_NR>47.809727</GIS_CALC_LAT_DECIMAL_NR>
<GIS_CALC_LONG_DECIMAL_NR>117.569984</GIS_CALC_LONG_DECIMAL_NR>
<LAST_UPD_DT>2005-07-21T22:17:23.177-07:00</LAST_UPD_DT>
</FacilitySite>
<FacilitySite>
<FS_ID>63488523</FS_ID>
<COMMON_NM>Austin Pavolka</COMMON_NM>
<LINE_1_AD>7711 CUSTER RD W </LINE_1_AD>
<CITY_NM>TACOMA </CITY_NM>
<GIS_VER_IND_CD>Y</GIS_VER_IND_CD>
<STATE_CD>WA</STATE_CD>
<ZIP_CD>98467-2643</ZIP_CD>
<GIS_CALC_LAT_DECIMAL_NR>47.1872</GIS_CALC_LAT_DECIMAL_NR>
<GIS_CALC_LONG_DECIMAL_NR>122.51625</GIS_CALC_LONG_DECIMAL_NR>
<LAST_UPD_DT>1997-06-23T00:00:00-07:00</LAST_UPD_DT>
</FacilitySite>
<FacilitySite>
<FS_ID>27413953</FS_ID>
<COMMON_NM>AUSTIN POWDER CO</COMMON_NM>
<LINE_1_AD>2852 CENTRALIA ALPHA RD </LINE_1_AD>
<CITY_NM>ONALASKA </CITY_NM>
<GIS_VER_IND_CD>Y</GIS_VER_IND_CD>
<STATE_CD>WA</STATE_CD>
<ZIP_CD>98570-9600</ZIP_CD>
<GIS_CALC_LAT_DECIMAL_NR>46.62614</GIS_CALC_LAT_DECIMAL_NR>
<GIS_CALC_LONG_DECIMAL_NR>122.78492</GIS_CALC_LONG_DECIMAL_NR>
<LAST_UPD_DT>2001-06-01T00:00:00-07:00</LAST_UPD_DT>
</FacilitySite>
</FacilitySiteList>');
What I would like to do is present the data in the xml file as:
ID Name Address City...
So I tried to get the data via the extractvalue function, but all I get is null values.
select
extractvalue(b.column_value,'FS_ID') fs_id
from tony_xml a, table(xmlsequence(extract(a.x,'FacilitySiteList/FacilitySite'))) b
FS_ID
(null)
(null)
(null)
(null)
(null)
(null)
The interesting thing is that I get the correct number of rows.
When I tried
select extractvalue(a.x,'*/FS_ID') fs_id
from tony_xml a
then I got the dreaded ORA-19025. I tried this on both 10.1.0.3 and 11.1.0.6 with the same result. So I am pretty sure it is a problem with my brain and not the machine.
Update:
Original:
select
extractvalue(b.column_value,'FS_ID') fs_id
from tony_xml a, table(xmlsequence(extract(a.x,'FacilitySiteList/FacilitySite'))) b
Corrected:
select
extractvalue(b.column_value,'*/FS_ID') fs_id
from tony_xml a, table(xmlsequence(extract(a.x,'/FacilitySiteList/FacilitySite'))) b
the "/*" FS_ID' and "/"FacilitySiteList/FacilitySite
did the trick
Message was edited by:
T