There are two methods you can use for extracting values from XML string (a column in a table). The column type will be XMLType.
These methods are EXTRACT(columnName, xpath). This returns list of nodes, also a record if extracting with a unique filter value.
You can also use ExtractValue(columnName, xpath) - This will work only for a single node, not for a collection of nodes. This way you can retrieve value from a unique record.
See examples below:
<Person>
<Name>Test</Name>
<Address>
<Street Number=1345>Monroe St</Street>
<City>TestCity</City>
<Zip>12345</Zip>
</Address>
</Person>
Assume this xml data in column
You can extract the street number attribute value using the query as below:
This can return multiple records:
select extract(columnName,'/Person/Address/Street/@Number') as StreetNumber from Table1;
For returning single record:
select extract(columnName,'/Person/Address/Street/@Number') as StreetNumber from Table1 where ID = 200;
Also For returning single record only:
select extractvalue(columnName, '/Person/Address/Street/@Number') as StreetNumber from Table1 where ID = 200;
I have used this and works like a charm.
Hope this finds useful to you also.
Njoyy...
