Extracting value from CLOB XML columnHow to extract data from a LONG column holding XML stringsHow Do You Parse and Process HTML/XML in PHP?XML parsing failed on OracleExtractValue from Oracle CLOB returning nullExtract data from XML (CLOB Type)Extracting XML data from NCLOB from XML with attributes in Oracle dbHow to Extract clob XML attribute name list from oracleUsing SQL to extract values from xml CLOBExtracting XML sub-tags from a clob in Oracle via SQLOracle 12c: Extract data (select) from XML (CLOB Type)
Applicability of Single Responsibility Principle
Failed to fetch jessie backports repository
How do I find the solutions of the following equation?
How does the UK government determine the size of a mandate?
How to run a prison with the smallest amount of guards?
How does it work when somebody invests in my business?
Was Spock the First Vulcan in Starfleet?
Risk of infection at the gym?
India just shot down a satellite from the ground. At what altitude range is the resulting debris field?
Escape a backup date in a file name
How can we prove that any integral in the set of non-elementary integrals cannot be expressed in the form of elementary functions?
Crossing the line between justified force and brutality
How do scammers retract money, while you can’t?
Tiptoe or tiphoof? Adjusting words to better fit fantasy races
Integer addition + constant, is it a group?
Opposite of a diet
How do we know the LHC results are robust?
Avoiding estate tax by giving multiple gifts
How to Reset Passwords on Multiple Websites Easily?
What happens if you roll doubles 3 times then land on "Go to jail?"
Why are there no referendums in the US?
What does "I’d sit this one out, Cap," imply or mean in the context?
What is paid subscription needed for in Mortal Kombat 11?
How did Arya survive the stabbing?
Extracting value from CLOB XML column
How to extract data from a LONG column holding XML stringsHow Do You Parse and Process HTML/XML in PHP?XML parsing failed on OracleExtractValue from Oracle CLOB returning nullExtract data from XML (CLOB Type)Extracting XML data from NCLOB from XML with attributes in Oracle dbHow to Extract clob XML attribute name list from oracleUsing SQL to extract values from xml CLOBExtracting XML sub-tags from a clob in Oracle via SQLOracle 12c: Extract data (select) from XML (CLOB Type)
I have a table where one of the columns is of type CLOB which has XML formatted data.
I am trying to extract value of CreatedByUserID which is 7934665 in the below example.
I tried doing it with XPATH and extractvalue functions in Oracle but for some reason I am not getting result.
Tried in one of the below ways. xmltype(Column_Name).extract('//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail/ns3:key/text()').getStringVal()
It says LPX-00601: Invalid token in. Can you please help me on this.
Column Value:
<EventPayloadDetails
xmlns:bpmn="http://schemas.oracle.com/bpm/xpath"
xmlns:ns3="urn:qi.com/EventPayloadDetails"
xmlns="urn:qi.com/EventPayloadDetails">
<ns3:PayloadDetailsList>
<ns3:PayloadDetail>
<ns3:key>NUMBER</ns3:key>
<ns3:value>0014</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Dep Num</ns3:key>
<ns3:value>120</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByUserID</ns3:key>
<ns3:value>7934665</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByFirstName</ns3:key>
<ns3:value>M</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByLastName</ns3:key>
<ns3:value>Ash</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Comments</ns3:key>
<ns3:value>"Review after this
query"
</ns3:value>
</ns3:PayloadDetail>
</ns3:PayloadDetailsList>
</EventPayloadDetails>
xml oracle xpath
asked Mar 7 at 12:53
sansan
559
add a comment |
I have a table where one of the columns is of type CLOB which has XML formatted data.
I am trying to extract value of CreatedByUserID which is 7934665 in the below example.
I tried doing it with XPATH and extractvalue functions in Oracle but for some reason I am not getting result.
Tried in one of the below ways. xmltype(Column_Name).extract('//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail/ns3:key/text()').getStringVal()
It says LPX-00601: Invalid token in. Can you please help me on this.
Column Value:
<EventPayloadDetails
xmlns:bpmn="http://schemas.oracle.com/bpm/xpath"
xmlns:ns3="urn:qi.com/EventPayloadDetails"
xmlns="urn:qi.com/EventPayloadDetails">
<ns3:PayloadDetailsList>
<ns3:PayloadDetail>
<ns3:key>NUMBER</ns3:key>
<ns3:value>0014</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Dep Num</ns3:key>
<ns3:value>120</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByUserID</ns3:key>
<ns3:value>7934665</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByFirstName</ns3:key>
<ns3:value>M</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByLastName</ns3:key>
<ns3:value>Ash</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Comments</ns3:key>
<ns3:value>"Review after this
query"
</ns3:value>
</ns3:PayloadDetail>
</ns3:PayloadDetailsList>
</EventPayloadDetails>
xml oracle xpath
asked Mar 7 at 12:53
sansan
559
Please show us the XPath expression you have tried.
– Wernfried Domscheit
Mar 7 at 12:56
Tried in one of the below ways. xmltype(Column_Name).extract('//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail/ns3:key/text()').getStringVal()
– san
Mar 7 at 13:05
Please edit your question instead of putting additional information as comment.
– Wernfried Domscheit
Mar 7 at 13:12
add a comment |
I have a table where one of the columns is of type CLOB which has XML formatted data.
I am trying to extract value of CreatedByUserID which is 7934665 in the below example.
I tried doing it with XPATH and extractvalue functions in Oracle but for some reason I am not getting result.
Tried in one of the below ways. xmltype(Column_Name).extract('//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail/ns3:key/text()').getStringVal()
It says LPX-00601: Invalid token in. Can you please help me on this.
Column Value:
<EventPayloadDetails
xmlns:bpmn="http://schemas.oracle.com/bpm/xpath"
xmlns:ns3="urn:qi.com/EventPayloadDetails"
xmlns="urn:qi.com/EventPayloadDetails">
<ns3:PayloadDetailsList>
<ns3:PayloadDetail>
<ns3:key>NUMBER</ns3:key>
<ns3:value>0014</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Dep Num</ns3:key>
<ns3:value>120</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByUserID</ns3:key>
<ns3:value>7934665</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByFirstName</ns3:key>
<ns3:value>M</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByLastName</ns3:key>
<ns3:value>Ash</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Comments</ns3:key>
<ns3:value>"Review after this
query"
</ns3:value>
</ns3:PayloadDetail>
</ns3:PayloadDetailsList>
</EventPayloadDetails>
xml oracle xpath
asked Mar 7 at 12:53
sansan
559
I have a table where one of the columns is of type CLOB which has XML formatted data.
I am trying to extract value of CreatedByUserID which is 7934665 in the below example.
I tried doing it with XPATH and extractvalue functions in Oracle but for some reason I am not getting result.
Tried in one of the below ways. xmltype(Column_Name).extract('//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail/ns3:key/text()').getStringVal()
It says LPX-00601: Invalid token in. Can you please help me on this.
Column Value:
<EventPayloadDetails
xmlns:bpmn="http://schemas.oracle.com/bpm/xpath"
xmlns:ns3="urn:qi.com/EventPayloadDetails"
xmlns="urn:qi.com/EventPayloadDetails">
<ns3:PayloadDetailsList>
<ns3:PayloadDetail>
<ns3:key>NUMBER</ns3:key>
<ns3:value>0014</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Dep Num</ns3:key>
<ns3:value>120</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByUserID</ns3:key>
<ns3:value>7934665</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByFirstName</ns3:key>
<ns3:value>M</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByLastName</ns3:key>
<ns3:value>Ash</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Comments</ns3:key>
<ns3:value>"Review after this
query"
</ns3:value>
</ns3:PayloadDetail>
</ns3:PayloadDetailsList>
</EventPayloadDetails>
xml oracle xpath
xml oracle xpath
asked Mar 7 at 12:53
sansan
559
asked Mar 7 at 12:53
sansan
559
edited Mar 7 at 13:14
san
asked Mar 7 at 12:53
sansan
559
asked Mar 7 at 12:53
sansan
559
asked Mar 7 at 12:53
sansan
559
559
Please show us the XPath expression you have tried.
– Wernfried Domscheit
Mar 7 at 12:56
Tried in one of the below ways. xmltype(Column_Name).extract('//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail/ns3:key/text()').getStringVal()
– san
Mar 7 at 13:05
Please edit your question instead of putting additional information as comment.
– Wernfried Domscheit
Mar 7 at 13:12
add a comment |
Please show us the XPath expression you have tried.
– Wernfried Domscheit
Mar 7 at 12:56
Tried in one of the below ways. xmltype(Column_Name).extract('//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail/ns3:key/text()').getStringVal()
– san
Mar 7 at 13:05
Please edit your question instead of putting additional information as comment.
– Wernfried Domscheit
Mar 7 at 13:12
Please show us the XPath expression you have tried.
– Wernfried Domscheit
Mar 7 at 12:56
Please show us the XPath expression you have tried.
– Wernfried Domscheit
Mar 7 at 12:56
Tried in one of the below ways. xmltype(Column_Name).extract('//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail/ns3:key/text()').getStringVal()
– san
Mar 7 at 13:05
Tried in one of the below ways. xmltype(Column_Name).extract('//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail/ns3:key/text()').getStringVal()
– san
Mar 7 at 13:05
Please edit your question instead of putting additional information as comment.
– Wernfried Domscheit
Mar 7 at 13:12
Please edit your question instead of putting additional information as comment.
– Wernfried Domscheit
Mar 7 at 13:12
add a comment |
2 Answers
2
active
oldest
votes
Created a query which takes your XML (in WITH part) and performs extracts (scroll to the end).
WITH test_table
AS (SELECT TO_CLOB('<EventPayloadDetails
xmlns:bpmn="http://schemas.oracle.com/bpm/xpath"
xmlns:ns3="urn:qi.com/EventPayloadDetails"
xmlns="urn:qi.com/EventPayloadDetails">
<ns3:PayloadDetailsList>
<ns3:PayloadDetail>
<ns3:key>NUMBER</ns3:key>
<ns3:value>0014</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Dep Num</ns3:key>
<ns3:value>120</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByUserID</ns3:key>
<ns3:value>7934665</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByFirstName</ns3:key>
<ns3:value>M</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByLastName</ns3:key>
<ns3:value>Ash</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Comments</ns3:key>
<ns3:value>"Review after this
query"
</ns3:value>
</ns3:PayloadDetail>
</ns3:PayloadDetailsList>
</EventPayloadDetails>') as test_data
from dual)
SELECT XMLTYPE(test_data) xml_data
, EXTRACTVALUE(
XMLTYPE(test_data)
,'//*/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key="CreatedByUserID"]/ns3:value'
,'xmlns:ns3="urn:qi.com/EventPayloadDetails"'
) value
, XMLTYPE(test_data).extract(
'//*/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key="CreatedByUserID"]/ns3:value/text()'
,'xmlns:ns3="urn:qi.com/EventPayloadDetails"'
).getStringVal() value2
FROM test_table
answered Mar 7 at 13:24
artbroartbro
13818
add a comment |
Please try the XPath below:
//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key='CreatedByUserID']/ns3:value/text()
answered Mar 7 at 13:19
Konstantinos PalaiodimosKonstantinos Palaiodimos
3719
I wanted to post the same answer but I also getLPX-00601: Invalid token
– Wernfried Domscheit
Mar 7 at 13:30
Because there is no definition of tablespace ns3 anywhere...
– artbro
Mar 7 at 13:31
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55044295%2fextracting-value-from-clob-xml-column%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Created a query which takes your XML (in WITH part) and performs extracts (scroll to the end).
WITH test_table
AS (SELECT TO_CLOB('<EventPayloadDetails
xmlns:bpmn="http://schemas.oracle.com/bpm/xpath"
xmlns:ns3="urn:qi.com/EventPayloadDetails"
xmlns="urn:qi.com/EventPayloadDetails">
<ns3:PayloadDetailsList>
<ns3:PayloadDetail>
<ns3:key>NUMBER</ns3:key>
<ns3:value>0014</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Dep Num</ns3:key>
<ns3:value>120</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByUserID</ns3:key>
<ns3:value>7934665</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByFirstName</ns3:key>
<ns3:value>M</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByLastName</ns3:key>
<ns3:value>Ash</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Comments</ns3:key>
<ns3:value>"Review after this
query"
</ns3:value>
</ns3:PayloadDetail>
</ns3:PayloadDetailsList>
</EventPayloadDetails>') as test_data
from dual)
SELECT XMLTYPE(test_data) xml_data
, EXTRACTVALUE(
XMLTYPE(test_data)
,'//*/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key="CreatedByUserID"]/ns3:value'
,'xmlns:ns3="urn:qi.com/EventPayloadDetails"'
) value
, XMLTYPE(test_data).extract(
'//*/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key="CreatedByUserID"]/ns3:value/text()'
,'xmlns:ns3="urn:qi.com/EventPayloadDetails"'
).getStringVal() value2
FROM test_table
answered Mar 7 at 13:24
artbroartbro
13818
add a comment |
Created a query which takes your XML (in WITH part) and performs extracts (scroll to the end).
WITH test_table
AS (SELECT TO_CLOB('<EventPayloadDetails
xmlns:bpmn="http://schemas.oracle.com/bpm/xpath"
xmlns:ns3="urn:qi.com/EventPayloadDetails"
xmlns="urn:qi.com/EventPayloadDetails">
<ns3:PayloadDetailsList>
<ns3:PayloadDetail>
<ns3:key>NUMBER</ns3:key>
<ns3:value>0014</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Dep Num</ns3:key>
<ns3:value>120</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByUserID</ns3:key>
<ns3:value>7934665</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByFirstName</ns3:key>
<ns3:value>M</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByLastName</ns3:key>
<ns3:value>Ash</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Comments</ns3:key>
<ns3:value>"Review after this
query"
</ns3:value>
</ns3:PayloadDetail>
</ns3:PayloadDetailsList>
</EventPayloadDetails>') as test_data
from dual)
SELECT XMLTYPE(test_data) xml_data
, EXTRACTVALUE(
XMLTYPE(test_data)
,'//*/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key="CreatedByUserID"]/ns3:value'
,'xmlns:ns3="urn:qi.com/EventPayloadDetails"'
) value
, XMLTYPE(test_data).extract(
'//*/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key="CreatedByUserID"]/ns3:value/text()'
,'xmlns:ns3="urn:qi.com/EventPayloadDetails"'
).getStringVal() value2
FROM test_table
answered Mar 7 at 13:24
artbroartbro
13818
add a comment |
Created a query which takes your XML (in WITH part) and performs extracts (scroll to the end).
WITH test_table
AS (SELECT TO_CLOB('<EventPayloadDetails
xmlns:bpmn="http://schemas.oracle.com/bpm/xpath"
xmlns:ns3="urn:qi.com/EventPayloadDetails"
xmlns="urn:qi.com/EventPayloadDetails">
<ns3:PayloadDetailsList>
<ns3:PayloadDetail>
<ns3:key>NUMBER</ns3:key>
<ns3:value>0014</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Dep Num</ns3:key>
<ns3:value>120</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByUserID</ns3:key>
<ns3:value>7934665</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByFirstName</ns3:key>
<ns3:value>M</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByLastName</ns3:key>
<ns3:value>Ash</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Comments</ns3:key>
<ns3:value>"Review after this
query"
</ns3:value>
</ns3:PayloadDetail>
</ns3:PayloadDetailsList>
</EventPayloadDetails>') as test_data
from dual)
SELECT XMLTYPE(test_data) xml_data
, EXTRACTVALUE(
XMLTYPE(test_data)
,'//*/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key="CreatedByUserID"]/ns3:value'
,'xmlns:ns3="urn:qi.com/EventPayloadDetails"'
) value
, XMLTYPE(test_data).extract(
'//*/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key="CreatedByUserID"]/ns3:value/text()'
,'xmlns:ns3="urn:qi.com/EventPayloadDetails"'
).getStringVal() value2
FROM test_table
answered Mar 7 at 13:24
artbroartbro
13818
Created a query which takes your XML (in WITH part) and performs extracts (scroll to the end).
WITH test_table
AS (SELECT TO_CLOB('<EventPayloadDetails
xmlns:bpmn="http://schemas.oracle.com/bpm/xpath"
xmlns:ns3="urn:qi.com/EventPayloadDetails"
xmlns="urn:qi.com/EventPayloadDetails">
<ns3:PayloadDetailsList>
<ns3:PayloadDetail>
<ns3:key>NUMBER</ns3:key>
<ns3:value>0014</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Dep Num</ns3:key>
<ns3:value>120</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByUserID</ns3:key>
<ns3:value>7934665</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByFirstName</ns3:key>
<ns3:value>M</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>CreatedByLastName</ns3:key>
<ns3:value>Ash</ns3:value>
</ns3:PayloadDetail>
<ns3:PayloadDetail>
<ns3:key>Comments</ns3:key>
<ns3:value>"Review after this
query"
</ns3:value>
</ns3:PayloadDetail>
</ns3:PayloadDetailsList>
</EventPayloadDetails>') as test_data
from dual)
SELECT XMLTYPE(test_data) xml_data
, EXTRACTVALUE(
XMLTYPE(test_data)
,'//*/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key="CreatedByUserID"]/ns3:value'
,'xmlns:ns3="urn:qi.com/EventPayloadDetails"'
) value
, XMLTYPE(test_data).extract(
'//*/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key="CreatedByUserID"]/ns3:value/text()'
,'xmlns:ns3="urn:qi.com/EventPayloadDetails"'
).getStringVal() value2
FROM test_table
answered Mar 7 at 13:24
artbroartbro
13818
edited Mar 7 at 13:30
answered Mar 7 at 13:24
artbroartbro
13818
answered Mar 7 at 13:24
artbroartbro
13818
answered Mar 7 at 13:24
artbroartbro
13818
13818
add a comment |
add a comment |
Please try the XPath below:
//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key='CreatedByUserID']/ns3:value/text()
answered Mar 7 at 13:19
Konstantinos PalaiodimosKonstantinos Palaiodimos
3719
I wanted to post the same answer but I also getLPX-00601: Invalid token
– Wernfried Domscheit
Mar 7 at 13:30
Because there is no definition of tablespace ns3 anywhere...
– artbro
Mar 7 at 13:31
add a comment |
Please try the XPath below:
//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key='CreatedByUserID']/ns3:value/text()
answered Mar 7 at 13:19
Konstantinos PalaiodimosKonstantinos Palaiodimos
3719
I wanted to post the same answer but I also getLPX-00601: Invalid token
– Wernfried Domscheit
Mar 7 at 13:30
Because there is no definition of tablespace ns3 anywhere...
– artbro
Mar 7 at 13:31
add a comment |
Please try the XPath below:
//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key='CreatedByUserID']/ns3:value/text()
answered Mar 7 at 13:19
Konstantinos PalaiodimosKonstantinos Palaiodimos
3719
Please try the XPath below:
//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key='CreatedByUserID']/ns3:value/text()
answered Mar 7 at 13:19
Konstantinos PalaiodimosKonstantinos Palaiodimos
3719
answered Mar 7 at 13:19
Konstantinos PalaiodimosKonstantinos Palaiodimos
3719
answered Mar 7 at 13:19
Konstantinos PalaiodimosKonstantinos Palaiodimos
3719
answered Mar 7 at 13:19
Konstantinos PalaiodimosKonstantinos Palaiodimos
3719
3719
I wanted to post the same answer but I also getLPX-00601: Invalid token
– Wernfried Domscheit
Mar 7 at 13:30
Because there is no definition of tablespace ns3 anywhere...
– artbro
Mar 7 at 13:31
add a comment |
I wanted to post the same answer but I also getLPX-00601: Invalid token
– Wernfried Domscheit
Mar 7 at 13:30
Because there is no definition of tablespace ns3 anywhere...
– artbro
Mar 7 at 13:31
I wanted to post the same answer but I also get
LPX-00601: Invalid token– Wernfried Domscheit
Mar 7 at 13:30
I wanted to post the same answer but I also get
LPX-00601: Invalid token– Wernfried Domscheit
Mar 7 at 13:30
Because there is no definition of tablespace ns3 anywhere...
– artbro
Mar 7 at 13:31
Because there is no definition of tablespace ns3 anywhere...
– artbro
Mar 7 at 13:31
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55044295%2fextracting-value-from-clob-xml-column%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please show us the XPath expression you have tried.
– Wernfried Domscheit
Mar 7 at 12:56
Tried in one of the below ways. xmltype(Column_Name).extract('//EventPayloadDetails/ns3:PayloadDetailsList/ns3:PayloadDetail/ns3:key/text()').getStringVal()
– san
Mar 7 at 13:05
Please edit your question instead of putting additional information as comment.
– Wernfried Domscheit
Mar 7 at 13:12