Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    40

    Unanswered: Extract XML Data.

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What have you tried so far?

  3. #3
    Join Date
    Aug 2010
    Posts
    40
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •