This discussion is archived
12 Replies Latest reply: Feb 14, 2013 4:45 AM by Rahul_India RSS

XML BASICS

Rahul_India Journeyer
Currently Being Moderated
I have this xml
 create table tmp_xml of xmltype;

insert into tmp_xml values('<Roottag>
<SupplyDetail opcode="UPDATE">
   <SupplierIdentifier>
      <SupplierIDType>NA</SupplierIDType>
      <IDTypeName>NA</IDTypeName>
      <IDValue>NA</IDValue>
   </SupplierIdentifier>
   <AvailabilityCode>NA</AvailabilityCode>
   <ProductAvailability opcode="UPDATE"/>
   <ExpectedShipDate opcode="UPDATE"/>
   <Price>
      <PriceTypeCode>NET</PriceTypeCode>
      <PriceAmount>59.99</PriceAmount>
      <CurrencyCode>USD</CurrencyCode>
   </Price>
   <Price opcode="U">
      <PriceTypeCode>SINGLE</PriceTypeCode>
      <PriceAmount opcode="UPDATE"/>
      <CurrencyCode>USD</CurrencyCode>
   </Price>
</SupplyDetail>
<SupplyDetail opcode="DELETE">
   <SupplierIdentifier>
      <SupplierIDType>NA</SupplierIDType>
      <IDTypeName>NA</IDTypeName>
      <IDValue>NA</IDValue>
   </SupplierIdentifier>
   <AvailabilityCode>NA</AvailabilityCode>
   <ProductAvailability opcode="U"/>
   <ExpectedShipDate opcode="U"/>
   <Price>
      <PriceTypeCode>NET</PriceTypeCode>
      <PriceAmount>59.99</PriceAmount>
      <CurrencyCode>USD</CurrencyCode>
   </Price>
   <Price opcode="UPDATE">
      <PriceTypeCode>SINGLE</PriceTypeCode>
      <PriceAmount opcode="DELETE"/>
      <CurrencyCode>USD</CurrencyCode>
   </Price>
</SupplyDetail>
</Roottag>');
How can i retrieve all the values from the xml. I am using 11g

Modified insert in response to Odie's comment
  • 1. Re: XML BASICS
    odie_63 Guru
    Currently Being Moderated
    In what format do you want "all the values"?

    Please post expected output.
  • 2. Re: XML BASICS
    Rahul_India Journeyer
    Currently Being Moderated
    odie_63 wrote:
    In what format do you want "all the values"?

    Please post expected output.
    \
    Hi odie ,
    good evening
    In tabular format
    like each tag should map the column name
    And my queRy is not working when the SupplyDetail tag is 2 times


    select x.* from tmp_xml t,
    xmltable('SupplyDetail' passing t.object_value
              COLUMNS 
              "opcode"  varchar2(6) PATH  'SupplierIdentifier/SupplierIDType'
              )  x;
    
     
    
    i am getting this error when my table contains SUPPLYDETAIL tag 2 times
    
    ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
    19279. 00000 -  "XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence" 
    *Cause:    The XQuery sequence passed in had more than one item.
    *Action:   Correct the XQuery expression to return a single item sequence.
    Edited by: Rahul India on Feb 12, 2013 5:06 PM
  • 3. Re: XML BASICS
    odie_63 Guru
    Currently Being Moderated
    i am getting this error when my table contains SUPPLYDETAIL tag 2 times
    So you're saying SupplyDetail is not the root node? Post a relevant sample XML that shows that then.
    In tabular format
    like each tag should map the column name
    Again :
    Please post expected output.
    Thanks.
  • 4. Re: XML BASICS
    Rahul_India Journeyer
    Currently Being Moderated
    I have modified my XML file .
    I want the o/p in columns as
    opcode  SupplierIDType     IDTypeName                     ......
    U             n/a                        n/a 
                    n/a
    like this.
    I hope you get my message this time lol

    Edited by: Rahul India on Feb 12, 2013 5:54 PM
  • 5. Re: XML BASICS
    odie_63 Guru
    Currently Being Moderated
    It's not that simple.

    What about the <Price> elements?

    How many could you have per <SupplierDetail> ?

    How do you want the price data to appear in the result set? Each price in its own (new) row, or if you know the max number of occurrences, each price info in its own column i.e. PriceTypeCode_1, PriceAmount_1, etc. ?


    That kind of master/detail relationship should be treated just as we'd do in a regular data model, that is using 3rd Normal Form.
  • 6. Re: XML BASICS
    Rahul_India Journeyer
    Currently Being Moderated
    OK . Let me put in simple words.

    I just wants to shred the above XML and store in a table.

    Can you give me a simple example .Please dont give me link.Just show me a small example or if you can shred my xml data it will be useful for me.

    I have lots of questions to ask you after i get over the first hurdle.
  • 7. Re: XML BASICS
    odie_63 Guru
    Currently Being Moderated
    Here's one example of "shredding" the XML, but it gives redundant information.
    (adjust datatypes as necessary)
    SQL> select dtl.opcode
      2       , dtl.SupplierIDType
      3       , dtl.IDTypeName
      4       , dtl.IDValue
      5       , dtl.AvailabilityCode
      6       , p.*
      7  from tmp_xml t
      8     , xmltable(
      9         '/Roottag/SupplyDetail'
     10         passing t.object_value
     11         columns opcode           varchar2(6)  path '@opcode'
     12               , SupplierIDType   varchar2(10) path 'SupplierIdentifier/SupplierIDType'
     13               , IDTypeName       varchar2(10) path 'SupplierIdentifier/IDTypeName'
     14               , IDValue          varchar2(10) path 'SupplierIdentifier/IDValue'
     15               , AvailabilityCode varchar2(10) path 'AvailabilityCode'
     16               , all_prices       xmltype      path 'Price'
     17       ) dtl
     18     , xmltable(
     19         '/Price'
     20         passing dtl.all_prices
     21         columns PriceTypeCode    varchar2(10) path 'PriceTypeCode'
     22               , PriceAmount      varchar2(10) path 'PriceAmount'
     23               , CurrencyCode     varchar2(10) path 'CurrencyCode'
     24       ) p
     25  ;
     
    OPCODE SUPPLIERIDTYPE IDTYPENAME IDVALUE    AVAILABILITYCODE PRICETYPECODE PRICEAMOUNT CURRENCYCODE
    ------ -------------- ---------- ---------- ---------------- ------------- ----------- ------------
    U      NA             NA         NA         NA               NET           59.99       USD
    U      NA             NA         NA         NA               SINGLE                    USD
    U      NA             NA         NA         NA               NET           59.99       USD
    U      NA             NA         NA         NA               SINGLE                    USD
     
    Again, the correct way of doing it is to use multiple target tables (master/details).
  • 8. Re: XML BASICS
    Rahul_India Journeyer
    Currently Being Moderated
    Hi odie,
    Thanks for the help.
    Can you tell me how to read the selected tags using where clause.

    What should be my code if i was to read alll the nodes from <SupplyDetail opcode="DELETE"> till end?
  • 9. Re: XML BASICS
    odie_63 Guru
    Currently Being Moderated
    Can you tell me how to read the selected tags using where clause.
    You don't know how to use a WHERE clause ? :)
    ...
    WHERE opcode = 'DELETE'
  • 10. Re: XML BASICS
    Rahul_India Journeyer
    Currently Being Moderated
    No you didnt get my point clearly.

    I mean suppose there are multiple <SupplyDetai lopcode=" delete ">
    And i want to display one of them with all its child.Then how will i do it
  • 11. Re: XML BASICS
    odie_63 Guru
    Currently Being Moderated
    Rahul India wrote:
    No you didnt get my point clearly.
    I'll get your point when you decide to explain clearly what you want.

    If you're not sure how to convey your ideas with words, then give something we can both understand : sample data and expected output.
    You've given the sample data, now what about the expected output?
    I mean suppose there are multiple <SupplyDetai lopcode=" delete ">
    And i want to display one of them with all its child.Then how will i do it
    - What does "all its child" mean?

    Do you want to extract a single XML fragment,
    e.g.
    <SupplyDetail opcode="DELETE">
       <SupplierIdentifier>
          <SupplierIDType>NA</SupplierIDType>
        ...
    </SupplyDetail>
    or do you want to extract all data from <SupplyDetail> in a relational form?


    - What does "one of them" mean? The first one in document order, the last, one at random? Be specific.
  • 12. Re: XML BASICS
    Rahul_India Journeyer
    Currently Being Moderated
    odie_63 wrote:
    >


    - What does "one of them" mean? The first one in document order, the last, one at random? Be specific.
    Sorry i meant one at randon

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points