Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2014
    Posts
    2

    Unanswered: Sql querry for the scenario

    hi all,

    I have one table in that only one column having data in xmlformat and remaing normal oracle datatypes.for example column name is xml_data.I need sql to get values from that xml data output data highlighted for reference in below xml data.


    Sample xmldata for one of the record is like below

    <?xml version="1.0" encoding="utf-8"?>

    <ns0:finalvalue xmlns :ns0="http://amagdgdg.com">

    <ns0:valuableitems>

    <ns0:valuesfortheitems>dsddd</ns0:valuesfortheitems>

    <ns0:valuesid>d21</ns0:valuesid>

    <ns0:items>

    <ns0:itemid>1a>/ns0:itemid>

    <ns0:itemdfg>2a>/ns0:itemdfg>

    <ns0:itecart>11a>/ns0:itecart>

    </ns0:itesms>

    <ns0:items>

    <ns0:itemid>56a>/ns0:itemid>

    <ns0:itemdfg>2145a>/ns0:itemdfg>

    <ns0:itecart>1361a>/ns0:itecart>

    </ns0:itesms>

    <ns0:items>

    <ns0:itemid>1a>/iwwsb0:itemid>

    <ns0:itemdfg>2a>/wens0:itemdfg>

    <ns0:itecart>11a>/dns0:itecart>

    </ns0:itesms>

    </ns0:valuableitems>

    <ns0:valuableitems>

    <ns0:valuesfortheitems>qqqd</ns0:valuesfortheitems>

    <ns0:valuesid>dqw21</ns0:valuesid>

    <ns0:items>

    <ns0:itemid>1wwa>/ns0:itemid>

    <ns0:itemdfg>2qa>/ns0:itemdfg>

    <ns0:itecart>1ww1a>/ns0:itecart>

    </ns0:itesms>

    <ns0:items>

    <ns0:itemid>56wwa>/ns0:itemid>

    <ns0:itemdfg>21dd45a>/ns0:itemdfg>

    <ns0:itecart>13a61a>/ns0:itecart>

    </ns0:itesms>

    <ns0:items>

    <ns0:itemid>1addqa>/iwwsb0:itemid>

    <ns0:itemdfg>2wa>/wens0:itemdfg>

    <ns0:itecart>1aa1a>/dns0:itecart>

    </ns0:itesms>

    </ns0:valuableitems>

    </ns0:finalvalue>


    Requiredoutput:

    xml_data

    dsddd
    qqqd

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Try something like:
    Code:
    SELECT EXTRACTVALUE ( Xmltype ( Xml_Data )
                        , '/ns0:finalvalue/ns0:valuableitems/ns0:valuesfortheitems' )
      FROM My_One_Table;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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