Hello All,
I am struggling to extract data from an XML records using the XQuery,Can someone give me the query on how to extract the data given below from the XML below:

XML

<ns2:EntityDetails xmlns:ns2="http://www.ssme.com/slmFlex">
<ns2:Entity Type="com.fourcs.clm.iwarranty.wc.domain.VehicletR egistration" Id="7018">
<ns2:Field Type="java.lang.String" Name="VEHICLEAPPLICATION">11</ns2:Field>
<ns2:Field Type="java.lang.String" Name="VEHICLEMANUFACTURER">110</ns2:Field>
<ns2:Field Type="java.lang.String" Name="VEHICLEMODEL"/>
</ns2:Entity>
</ns2:EntityDetails>


My output should be ::
11

Column name in which the above XML is stored is XML_DATA and the Table name is VEHICLE_REGN

Thanks in advance.

What have you tried so far?

I tried the following queries:

SELECT XMLQUERY('declare default elemnt namespace "http://www.ssme.com/slmFlex";
$c/ns2:EntityDetails' passing XML_DATA as "c" )

from PROWNER.VEHICLE_REGN;

select t.VEHICLEMODEL, i.REF_CD, t.VEHICLEMANUFACTURER, T.VEHICLEAPPLICATION from PROWNER.VEHICLE_FLEX i,
xmltable('$XML_DATA/*:EntityDetails/*:Entity'
columns VEHICLEMANUFACTURER varchar(100) path 'VEHICLEMANUFACTURER',
VEHICLEAPPLICATION varchar(100) path 'VEHICLEAPPLICATION',
VEHICLEMODEL varchar(100) path 'VEHICLEMODEL') as t

I'm not sure what is the source of your XQuery syntax exercises, but neither of them looks right.

May be this will give you some ideas: XQuery reference - IBM DB2 9.7 for Linux, UNIX, and Windows