Home » Developer & Programmer » JDeveloper, Java & XML » Extract data from XML (merged) (xml parsing.)
Extract data from XML (merged) [message #542715] Thu, 09 February 2012 22:09 Go to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

How to extract the data from xml using the xsd file. attached files.

Explanation: first check the EmailMessage tage from order_conf.xml compared with Email.xml(<xsd:element name="EmailMessage">) if exists then go to next node.
EmailMessage(exists tag in order xml file)
->next <ns1:emailNotificationype> this tag should be follow under the EmailMessage tag(<xsd:element ref="emailNotificationype">) in Email.xml
->next <ns1:orderNotification> -> check this tag in <xsd:element name="orderNotification"> in Email.xml.
-> next <ns1:templateFormatInfo> -> it should follow under <xsd:element name="orderNotification"> in Email.xml.
-> next <ns1:templateFormatInfo> -> it should follow these tages <xsd:element name="templateFormatInfo"> <xsd:element ref="templatecode"/>
<xsd:element ref="templateversion"/>
i have to extract the value in between tags.

templatecode -> ORDCONF
templateversion ->1.0

Finally i have to load the data into table

Thanks,

[Updated on: Thu, 09 February 2012 22:14] by Moderator

Report message to a moderator

Re: ORACLE 11.2.0.1.0 [message #542716 is a reply to message #542715] Thu, 09 February 2012 22:14 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Please post the test XML data and expected result so that some of the expert will help you.
Also post what you tried so far.
Re: ORACLE 11.2.0.1.0 [message #542717 is a reply to message #542716] Thu, 09 February 2012 22:26 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

how can attach file? i didn't find any option in this?
Re: ORACLE 11.2.0.1.0 [message #542718 is a reply to message #542717] Thu, 09 February 2012 22:29 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

order_conf.xml file...

<?xml version="1.0" encoding="UTF-8" ?><EmailMessage xmlns:ns1="http://integration.sprint.com/v2/public/interfaces/EnterpriseEmail/EmailMessage.xsd" xmlns="http://integration.sprint.com/v2/public/interfaces/EnterpriseEmail/EmailMessage.xsd">
   <ns1:emailNotificationype>
      <ns1:orderNotification>
         <ns1:templateFormatInfo>
            <ns1:templatecode>ORDCONF</ns1:templatecode>
            <ns1:templateversion>1.0</ns1:templateversion>
         </ns1:templateFormatInfo>
         <ns1:recipientFullName> MAC VANN INC</ns1:recipientFullName>
         <ns1:recipientDistributionList>
            <ns1:recipient>
               <ns1:recipientAddressType>TO</ns1:recipientAddressType>
               <ns1:electronicAddressString>2012emailtesting@gmail.com</ns1:electronicAddressString>
            </ns1:recipient>
         </ns1:recipientDistributionList>
         <ns1:customerPurchaseOrderNumber>724040922-</ns1:customerPurchaseOrderNumber>
         <ns1:orderEntryDate>2010-06-23</ns1:orderEntryDate>
         <ns1:orderNumber>7I04493</ns1:orderNumber>
         <ns1:orderItemList>
            <ns1:orderItem>
               <ns1:orderItemQuantity>1</ns1:orderItemQuantity>
               <ns1:stockKeepingUnit>BAGP</ns1:stockKeepingUnit>
               <ns1:productDescription>WIRELESS ADVOC BAG FOR ACCESS </ns1:productDescription>
            </ns1:orderItem>
         </ns1:orderItemList>
         <ns1:billingSystemOrderNumber>DM14O21209606</ns1:billingSystemOrderNumber>
         <ns1:uspsPostalAddress>
            <ns1:formattedPostalAddressLines>
               <ns1:postalAddressLine1>1077 MAIN ST</ns1:postalAddressLine1>
               <ns1:postalAddressLine2/>
               <ns1:postalAddressLine3/>
            </ns1:formattedPostalAddressLines>
            <ns1:cityName>FISHKILLSS</ns1:cityName>
            <ns1:uspsStateAbbreviation>NY</ns1:uspsStateAbbreviation>
            <ns1:uspsPostalCd>12524</ns1:uspsPostalCd>
            <ns1:uspsPostalCdExtension>2213</ns1:uspsPostalCdExtension>
         </ns1:uspsPostalAddress>
         <ns1:expectedShipDate>2012-02-06</ns1:expectedShipDate>
      </ns1:orderNotification>
   </ns1:emailNotificationype>
</EmailMessage>
Re: ORACLE 11.2.0.1.0 [message #542719 is a reply to message #542717] Thu, 09 February 2012 22:29 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Try editing you post. You can see an option "File Attachments". There you can attach the file.
Or you can just paste the XMLin your post itself.
Re: ORACLE 11.2.0.1.0 [message #542720 is a reply to message #542719] Thu, 09 February 2012 22:43 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

find the attached file
  • Attachment: Email.txt
    (Size: 71.44KB, Downloaded 2105 times)
11.1.0.6.0 [message #542838 is a reply to message #542715] Fri, 10 February 2012 06:25 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Hi All,

I have loaded the data into table as below and want extract data from xml file. Please help me.

Find the xml file.

Thanks,
CREATE TABLE TABLE_WITH_XML_COLUMN
(
  XML_DOCUMENT  XMLTYPE
)

INSERT INTO table_with_xml_column VALUES 
(XMLType(bfilename('<<DIRECTORY_NAME>>', 'test1.xml'),nls_charset_id('AL32UTF8')));

commit;

  • Attachment: test1.txt
    (Size: 7.67KB, Downloaded 1899 times)

[Updated on: Fri, 10 February 2012 06:33] by Moderator

Report message to a moderator

Re: 11.1.0.6.0 [message #542839 is a reply to message #542838] Fri, 10 February 2012 06:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not the same question than in your Extract data from XML topic?

Quote:
want extract data from xml file.


What prevents you for doing it?

Regards
Michel
Re: 11.1.0.6.0 [message #542840 is a reply to message #542839] Fri, 10 February 2012 06:42 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Thanks for quick reply..
My requrement got changed...We have to extract the data from xml file.
Help me out
Re: 11.1.0.6.0 [message #542842 is a reply to message #542840] Fri, 10 February 2012 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So once again "What prevents you for doing it?"

Regards
Michel
Re: 11.1.0.6.0 [message #542844 is a reply to message #542842] Fri, 10 February 2012 07:11 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

i am doing like...
it's not working.....

 select extractvalue(xml_document,
'/EmailMessage/emailNotificationype/orderNotification/templateFormatInfo/templatecode') 
AS tspace_name
  from table_with_xml_column 

[Updated on: Fri, 10 February 2012 07:38] by Moderator

Report message to a moderator

Re: 11.1.0.6.0 [message #542845 is a reply to message #542844] Fri, 10 February 2012 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does "it's not working" mean?

Regards
Michel
Re: 11.1.0.6.0 [message #542848 is a reply to message #542845] Fri, 10 February 2012 08:00 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

it's not getting result...
Re: 11.1.0.6.0 [message #542856 is a reply to message #542848] Fri, 10 February 2012 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which result?

Regards
Michel
Re: 11.1.0.6.0 [message #542873 is a reply to message #542856] Fri, 10 February 2012 11:10 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

I am not able to extract data from between tags using extractvalue function.
Re: 11.1.0.6.0 [message #542883 is a reply to message #542873] Fri, 10 February 2012 12:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which data?

Regards
Michel
Re: 11.1.0.6.0 [message #543012 is a reply to message #542883] Sun, 12 February 2012 09:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11gR2> COLUMN templatecode	 FORMAT A15
SCOTT@orcl_11gR2> COLUMN templateversion FORMAT A15
SCOTT@orcl_11gR2> SELECT EXTRACTVALUE
  2  	      (x.column_value,
  3  	       '//templatecode',
  4  	       'xmlns:ns1="http://integration.sprint.com/v2/public/interfaces/EnterpriseEmail/EmailMessage.xsd"
  5  		xmlns="http://integration.sprint.com/v2/public/interfaces/EnterpriseEmail/EmailMessage.xsd"')
  6  	      AS templatecode,
  7  	    EXTRACTVALUE
  8  	      (x.column_value,
  9  	       '//templateversion',
 10  	       'xmlns:ns1="http://integration.sprint.com/v2/public/interfaces/EnterpriseEmail/EmailMessage.xsd"
 11  		xmlns="http://integration.sprint.com/v2/public/interfaces/EnterpriseEmail/EmailMessage.xsd"')
 12  	      AS templateversion
 13  FROM   TABLE
 14  	      (XMLSEQUENCE
 15  		(EXTRACT
 16  		  (XMLTYPE (BFILENAME ('MY_DIR', 'test1.xml'), NLS_CHARSET_ID ('AL32UTF8')),
 17  		   '//EmailMessage',
 18  		   'xmlns:ns1="http://integration.sprint.com/v2/public/interfaces/EnterpriseEmail/EmailMessage.xsd"
 19  		    xmlns="http://integration.sprint.com/v2/public/interfaces/EnterpriseEmail/EmailMessage.xsd"'))) x
 20  /

TEMPLATECODE    TEMPLATEVERSION
--------------- ---------------
ORDCONF         1.0

1 row selected.

Re: 11.1.0.6.0 [message #543209 is a reply to message #543012] Mon, 13 February 2012 08:06 Go to previous message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Thanks lot,it's working... You are right person to do...
Previous Topic: Matrix Report Creation
Next Topic: XML extract in PL/SQL
Goto Forum:
  


Current Time: Thu Mar 28 04:12:28 CDT 2024