Select data based on XML attributes

oracle-12c

My table contain two columns: Branch and data

Here's an example:

Branch having value finance
Data having value xml packet i.e.

<field id="0" value="2200"/>
              <field id="2" value="9048001001234567890"/>
              <field id="3" value="000000"/>
              <field id="4" currency="356" type="amount" value="100.00"/>
              <field id="7" value="0702175451"/>
              <field id="11" value="818317649211"/>

The data column contains some XML packet. I want to select some particular data out of that XML. For example, I want to select only the value of field id=4 (i.e. 100.00).

How can I do that?

Best Answer

Based on your sample data and revised question: id=4 result 100.00

Table:

Create Table #tbl
(
id Int,
[data] xml
)
Insert Into #tbl Values
(1,'<field id="0" value="2200"/> <field id="2" value="9048001001234567890"/> <field id="3" value="000000"/> <field id="4" currency="356" type="amount" value="100.00"/> <field id="7" value="0702175451"/> <field id="11" value="818317649211"/> ')

Query:

SELECT 
       Cast(d.value('(./@value)[1]', 'VarChar(20)') As Decimal(10,2)) AS yourdata
FROM 
       #tbl 
       CROSS APPLY [data].nodes('/field') t(d)
Where  d.value('(./@id)[1]', 'Int') = 4

Result:

yourdata
100.00