OraFAQ Forum: JDeveloper, Java & XML » sql query help to fetch sub string from xml

Home » Developer & Programmer » JDeveloper, Java & XML » sql query help to fetch sub string from xml (11G)

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

sql query help to fetch sub string from xml [message #644594]

Tue, 10 November 2015 22:23

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

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

Report message to a moderator

Re: sql query help to fetch sub string from xml [message #644596 is a reply to message #644595]

Tue, 10 November 2015 23:17

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;

Report message to a moderator