HomeОбразованиеRelated VideosMore From: yrrhelp

How to Parse XML in Oracle

49 ratings | 10253 views
This Video Shows 1. How to Read and parse XML in Oracle using EXTRACTVALUE and XMLTABLE. 2. When to use extractvalue and XMLTable. 3. Brief Introduction to XMLPATH
Html code for embedding videos on your blog
Text Comments (13)
Debopriyo Simlai (1 month ago)
Is it possible to convert a XML type column to Varchar(2) in Oracle DB it self ?
Miroslav Bobenič (1 month ago)
Thx! (y)
Kiran R (4 months ago)
CREATE TABLE mytable1 (key_column VARCHAR2(10) PRIMARY KEY, xml_column XMLType); It would be helpful if you can guide me how to load/insert the file into xml_column. my file can have millions of the records
Ankit Vyas (5 months ago)
Hi Thanks for this Video. I just wanted to check 1 more condition on top of this like i need records whose lenth is greater than 5. How can we use EXTRACTVALUE on this condition.
Dinesh Paul (7 months ago)
Thank you very much for this video. Very well explained. Great work.
gowtham varma (8 months ago)
How to retrieve data from a nested tags which are repeated n number of times
Rajesh Ravindran (8 months ago)
<?xml version="1.0" encoding="utf-8"?> <tXML xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <Header> <Source>Host</Source> <Action_Type>Create</Action_Type> <Reference_ID /> <Message_Type>DistributionOrder</Message_Type> <Company_ID>1</Company_ID> </Header> <Message> <DistributionOrder> <DistributionOrderId>2303063450218180031862670100</DistributionOrderId> <OrderType>SF</OrderType> </DistributionOrder> <DistributionOrder> <DistributionOrderId>2303063450218180031862670101</DistributionOrderId> <OrderType>PK</OrderType> </DistributionOrder> </Message> </tXML> select XT.* from tran_log_message TL, XMLTABLE ( '/tXML/Message/DistributionOrder' PASSING XMLTYPE(TL.MSG_LINE_TEXT) COLUMNS "DistributionOrderId" VARCHAR2(30) PATH 'DistributionOrderId', "OrderType" VARCHAR2(30) PATH 'OrderType' ) XT; is what i am trying.... I am not able to get this work
Northwest521 (9 months ago)
Thanks a lot. This saved my day!
Prajwal Gowda (1 year ago)
how to load tat xml into msg attribute(into temp table) ?? is there any procedure
Kiran R (4 months ago)
Bit confusing?? I'm new to Oracle, I have an xml file in my local directory and created table with one column as XMLTYPE. CREATE TABLE mytable1 (key_column VARCHAR2(10) PRIMARY KEY, xml_column XMLType); It would be helpful if you can guide me how to load/inser the file data into xml_column.
Naresh Mankala (11 months ago)
Instead of using a CLOB column to load XML, I would suggest to create table column as XMLType and while inserting use XMLType.CreateXML(<xml doc>). If you want to load XMLFile directly from the file system, you can use a CLOB column. Create a BFILE and use DBMS_LOB.LOADCLOBFROMFILE procedure to load the XML File from the file system directly into CLOB. Let me know if you are facing any issues.
noe arciniega (1 year ago)
Can you explain how to extract the xml string from a xml file that contains many registrys?.. thank you for your help
Jaffar Khan (1 year ago)
Hi how to extract if it has nested XML with multiple namespaces..Below is the example. Kindly [email protected] <?xml version="1.0" encoding="UTF-8"?> <Envelope xmlns="http://schemas.microsoft.com/dynamics/2011/01/documents/Message"> <Header><MessageId>{6F72D7A7-650E-4190-A2AA-B0B55833AF14}</MessageId><Action>http://schemas.microsoft.com/dynamics/2008/01/services/SalesSalesInvoiceService/read</Action></Header> <Body><MessageParts xmlns="http://schemas.microsoft.com/dynamics/2011/01/documents/Message"> <SalesInvoice xmlns="http://schemas.microsoft.com/dynamics/2008/01/documents/SalesInvoice"> <CustInvoiceJour class="entity"> <CurrencyCode>INR</CurrencyCode> <CustInvoiceTrans class="entity"> <InvoiceDate>2017-08-04</InvoiceDate> <InvoiceId>G11111</InvoiceId> </CustInvoiceJour> </SalesInvoice> </SalesInvoice></MessageParts></Body> </Envelope>

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.