12 Replies Latest reply: Feb 14, 2013 6:45 AM by Rahul_India RSS

    XML BASICS

    Rahul_India
      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
          In what format do you want "all the values"?

          Please post expected output.
          • 2. Re: XML BASICS
            Rahul_India
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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