Home » Developer & Programmer » JDeveloper, Java & XML » sql query help to fetch sub string from xml (11G)
sql query help to fetch sub string from xml [message #644594] Tue, 10 November 2015 22:23 Go to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Hello All,

How you doing. I need help to fetch substring between xml tag. column data type is varchar.

here is the string i have in column. i need return value which is between <App_Error_Text> </App_Error_Text> tags. (<App_Error_Text><![CDATA[is not present in system.]]></App_Error_Text>) i.e <![CDATA[is not present in system.]]>


<Additional_References><Additional_Reference_Info><Reference_Type>Carrier</Reference_Type><Reference_ID></Reference_ID></Additional_Reference_Info></Additional_References><Receiver_Info></Receiver_Info><App_Advice_Errors><App_Advice_Error><App_Error_Text><![CDATA[is not present in system.]]></App_Error_Text><Error_Message_Tokens></Error_Message_Tokens><App_Err_Cond_Code>9999178</App_Err_Cond_Code></App_Advice_Error></App_Advice_Errors></Application_Advice></Response_Details></Response>



Appreicate your inputs

[Updated on: Tue, 10 November 2015 22:26]

Report message to a moderator

Re: sql query help to fetch sub string from xml [message #644595 is a reply to message #644594] Tue, 10 November 2015 23:02 Go to previous messageGo to next message
prabhatlnct2008
Messages: 3
Registered: November 2015
Location: india
Junior Member
Use the following query

with q as (
SELECT '<Additional_References><Additional_Reference_Info><Reference_Type>Carrier</Reference_Type><Reference_ID></Reference_ID></Additional_Reference_Info ></Additional_References><Receiver_Info></Receiver_Info><App_Advice_Errors><App_Advice_Error><App_Error_Text><![CDATA[IS NOT present IN system.]]></App_Error_Text><Error_Message_Tokens></Error_Message_Tokens><App_Err_Cond_Code>9999178</App_Err_Cond_Code></App_Advice_Error> </App_Advice_Errors></Application_Advice></Response_Details></Response>' AS text
FROM dual)
select regexp_replace(regexp_substr(text , 'App_Error_Text.+/App_Error_Text') , '<?/?App_Error_Text>?')
from q
Re: sql query help to fetch sub string from xml [message #644596 is a reply to message #644595] Tue, 10 November 2015 23:17 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Thanks for your response.

Here is my complete query, MSG_LINE_TEXT is the below xml string, i am expecting MSG_LINE_TEXT should return only <![CDATA[is not present in system.]]>

<Additional_References><Additional_Reference_Info><Reference_Type>Carrier</Reference_Type><Reference_ID></Reference_ID></Additional_Reference_Info ></Additional_References><Receiver_Info></Receiver_Info><App_Advice_Errors><App_Advice_Error><App_Error_Text><![CDATA[is not present in system.]]></App_Error_Text><Error_Message_Tokens></Error_Message_Tokens><App_Err_Cond_Code>9999178</App_Err_Cond_Code></App_Advice_Error ></App_Advice_Errors></Application_Advice></Response_Details></Response>



select TRAN_LOG_ID, MSG_LINE_TEXT from BN_LOG tl ,BN_LOG_MESSAGE blm
and tl.MESSAGE_ID = blm.LOG_ID and tl.created_dttm > sysdate-1;

Re: sql query help to fetch sub string from xml [message #644597 is a reply to message #644596] Tue, 10 November 2015 23:29 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Thank you all , i am able to fix the query.
Re: sql query help to fetch sub string from xml [message #644598 is a reply to message #644597] Wed, 11 November 2015 00:31 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How?

Previous Topic: XSD type reference
Next Topic: Generate XML Extract
Goto Forum:
  


Current Time: Thu Mar 28 14:18:54 CDT 2024