This content has been marked as final. Show 12 replies
In what format do you want "all the values"?
Please post expected output.
Hi odie ,
In tabular format
like each tag should map the column name
And my queRy is not working when the SupplyDetail tag is 2 times
Edited by: Rahul India on Feb 12, 2013 5:06 PM
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.
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.
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.
Here's one example of "shredding" the XML, but it gives redundant information.
(adjust datatypes as necessary)
Again, the correct way of doing it is to use multiple target tables (master/details).
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
Rahul India wrote:I'll get your point when you decide to explain clearly what you want.
No you didnt get my point clearly.
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 ">- What does "all its child" mean?
And i want to display one of them with all its child.Then how will i do it
Do you want to extract a single XML fragment,
or do you want to extract all data from <SupplyDetail> in a relational form?
<SupplyDetail opcode="DELETE"> <SupplierIdentifier> <SupplierIDType>NA</SupplierIDType> ... </SupplyDetail>
- What does "one of them" mean? The first one in document order, the last, one at random? Be specific.