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

How to Parse XML in Oracle

90 ratings | 17062 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 (17)
Ryan Adams (2 months ago)
Love the video, I am wondering if you would be interested in breaking down an XML query I have. I would love to share it with you if interested?
Praveen mukati (3 months ago)
Hey I have an URl which is giving me the data in xml.format..so how could I store that into table...through stored procedure
Hitesh Kamble (3 months ago)
Hi , can you please explain example with namespaces in XML because it won't get parsed by simply providing path
aniket gaonkar (6 months ago)
What a great video...very much thankful to d uploader...! Keep uploading ..!
Debopriyo Simlai (8 months ago)
Is it possible to convert a XML type column to Varchar(2) in Oracle DB it self ?
Miroslav Bobenič (9 months ago)
Thx! (y)
Kiran R (11 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 (1 year 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 (1 year ago)
Thank you very much for this video. Very well explained. Great work.
gowtham varma (1 year ago)
How to retrieve data from a nested tags which are repeated n number of times
Rajesh Ravindran (1 year 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 (1 year 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 (11 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 (1 year 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.