Home » Developer & Programmer » JDeveloper, Java & XML » How to retrieve XML tag using sql
How to retrieve XML tag using sql [message #408897] Thu, 18 June 2009 04:20 Go to next message
huda10
Messages: 23
Registered: December 2008
Junior Member
Hi,

I've the following xml file in database table column.
<DOCUMENT>
<AUTOSTREAM_DOC_ID>AK_178595</AUTOSTREAM_DOC_ID>
<LEAD_XML>
<SIEBELMESSAGE MESSAGEID="1-1T96JP" INTOBJECTNAME="MM SV CS SERVICE PROVIDER LEAD INBOUND" MESSAGETYPE="INTEGRATION OBJECT" INTOBJECTFORMAT="SIEBEL HIERARCHICAL">
<LISTOFMMSV>
<MMSVCSS>
<ID>1-6XI5Z1</ID>
</MMSVCSS>
</LISTOFMMSV>
</SIEBELMESSAGE>
</LEAD_XML>
</DOCUMENT>

I need to retrive xml tag "<SIEBELMESSAGE MESSAGEID="1-1T96JP" INTOBJECTNAME="MM SV CS SERVICE PROVIDER LEAD INBOUND" MESSAGETYPE="INTEGRATION OBJECT" INTOBJECTFORMAT="SIEBEL HIERARCHICAL">" using sql query ..
Can anybody please help ..

Thanks & Regards,
Abdul
Re: How to retrieve XML tag using sql [message #408899 is a reply to message #408897] Thu, 18 June 2009 04:26 Go to previous messageGo to next message
ThomasG
Messages: 3064
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Is the column an XMLTYPE?

then look for "Example 4-6 Using XMLType and in a SELECT Statement" here.
Re: How to retrieve XML tag using sql [message #408904 is a reply to message #408899] Thu, 18 June 2009 04:42 Go to previous messageGo to next message
huda10
Messages: 23
Registered: December 2008
Junior Member
As mentioned in example 4.6 he following values inserted into table --
INSERT INTO warehouses VALUES
( 100, XMLType(
'<Warehouse whNo="100">
<Building>Owned</Building>
</Warehouse>'), 'Tower Records', 1003);

How do we retrieve tag "<Warehouse whNo="100">" (Not the value ).
Re: How to retrieve XML tag using sql [message #408910 is a reply to message #408897] Thu, 18 June 2009 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 57616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not multipost/crosspost your question.

Regards
Michel
Re: How to retrieve XML tag using sql [message #408911 is a reply to message #408910] Thu, 18 June 2009 04:58 Go to previous messageGo to next message
huda10
Messages: 23
Registered: December 2008
Junior Member
My applogies..System got stuck and was unaware of the previous message getting posted.
Re: How to retrieve XML tag using sql [message #408914 is a reply to message #408911] Thu, 18 June 2009 05:16 Go to previous messageGo to next message
huda10
Messages: 23
Registered: December 2008
Junior Member
Can you please help me..its Urgent
Re: How to retrieve XML tag using sql [message #408915 is a reply to message #408911] Thu, 18 June 2009 05:17 Go to previous message
ThomasG
Messages: 3064
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, you can also extract the entire SIEBELMESSAGE tag. Of course the entire SIEBELMESSAGE tag is everything from <SIEBELMESSAGE ..> to </SIEBELMESSAGE>

CREATE TABLE testxml (
  col xmltype
);


INSERT INTO testxml VALUES
 ( XMLType( '<DOCUMENT>
<AUTOSTREAM_DOC_ID>AK_178595</AUTOSTREAM_DOC_ID>
<LEAD_XML>
<SIEBELMESSAGE MESSAGEID="1-1T96JP" INTOBJECTNAME="MM SV CS SERVICE PROVIDER LEAD INBOUND"
 MESSAGETYPE="INTEGRATION OBJECT" INTOBJECTFORMAT="SIEBEL HIERARCHICAL">
<LISTOFMMSV>
<MMSVCSS>
<ID>1-6XI5Z1</ID>
</MMSVCSS>
</LISTOFMMSV>
</SIEBELMESSAGE>
</LEAD_XML>
</DOCUMENT>'));


SELECT t.col.getClobVal() FROM testxml t;


SELECT t.col.extract('//SIEBELMESSAGE').getClobVal() 
  FROM testxml t;



Just

<SIEBELMESSAGE MESSAGEID="1-1T96JP" INTOBJECTNAME="MM SV CS SERVICE PROVIDER LEAD INBOUND" MESSAGETYPE="INTEGRATION OBJECT" INTOBJECTFORMAT="SIEBEL HIERARCHICAL">

wouldn't be valid XML anymore anyway, since the closing tag is missing.

There are dozens of different examples on the linked page.

[Updated on: Thu, 18 June 2009 05:19]

Report message to a moderator

Previous Topic: build problem
Next Topic: Loadjava in unix server
Goto Forum:
  


Current Time: Mon Apr 21 06:32:48 CDT 2014

Total time taken to generate the page: 0.08362 seconds