OraFAQ Forum: JDeveloper, Java & XML » Extract data from XML (merged)

Home » Developer & Programmer » JDeveloper, Java & XML » Extract data from XML (merged) (xml parsing.)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Extract data from XML (merged) [message #542715]

Thu, 09 February 2012 22:09

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

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

i.rajavu@gmail.com

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.

Report message to a moderator

Re: ORACLE 11.2.0.1.0 [message #542717 is a reply to message #542716]

Thu, 09 February 2012 22:26

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

how can attach file? i didn't find any option in this?

Report message to a moderator

Re: ORACLE 11.2.0.1.0 [message #542718 is a reply to message #542717]

Thu, 09 February 2012 22:29

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>

Report message to a moderator

Re: ORACLE 11.2.0.1.0 [message #542719 is a reply to message #542717]

Thu, 09 February 2012 22:29

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

i.rajavu@gmail.com

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.

Report message to a moderator

Re: ORACLE 11.2.0.1.0 [message #542720 is a reply to message #542719]

Thu, 09 February 2012 22:43

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

find the attached file

Attachment: Email.txt
(Size: 71.44KB, Downloaded 2653 times)

Report message to a moderator

11.1.0.6.0 [message #542838 is a reply to message #542715]

Fri, 10 February 2012 06:25

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 2410 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

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: 11.1.0.6.0 [message #542840 is a reply to message #542839]

Fri, 10 February 2012 06:42

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

Report message to a moderator

Re: 11.1.0.6.0 [message #542842 is a reply to message #542840]

Fri, 10 February 2012 07:04

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So once again "What prevents you for doing it?"

Regards
Michel

Report message to a moderator

Re: 11.1.0.6.0 [message #542844 is a reply to message #542842]

Fri, 10 February 2012 07:11

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

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What does "it's not working" mean?

Regards
Michel

Report message to a moderator

Re: 11.1.0.6.0 [message #542848 is a reply to message #542845]

Fri, 10 February 2012 08:00

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

it's not getting result...

Report message to a moderator

Re: 11.1.0.6.0 [message #542856 is a reply to message #542848]

Fri, 10 February 2012 08:34

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Which result?

Regards
Michel

Report message to a moderator

Re: 11.1.0.6.0 [message #542873 is a reply to message #542856]

Fri, 10 February 2012 11:10

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

I am not able to extract data from between tags using extractvalue function.

Report message to a moderator

Re: 11.1.0.6.0 [message #542883 is a reply to message #542873]

Fri, 10 February 2012 12:11

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Which data?

Regards
Michel

Report message to a moderator

Re: 11.1.0.6.0 [message #543012 is a reply to message #542883]

Sun, 12 February 2012 09:39

Barbara Boehmer
Messages: 9103
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.

Report message to a moderator

Re: 11.1.0.6.0 [message #543209 is a reply to message #543012]

Mon, 13 February 2012 08:06

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Thanks lot,it's working... You are right person to do...

Report message to a moderator

Previous Topic:	Matrix Report Creation
Next Topic:	XML extract in PL/SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 01:05:00 CST 2025