Problem with Xquery XMLType

I have an XMLType column containing XML Data.

one of the tag names is <ns4:ContactFullName>.
I would like to extract the value from that element.  First I need to select the row which contains the xmltype data that I want to extract from.

My query looks like this:

  SELECT EXTRACTVALUE(VALUE(x) , '\ns4:ContactFullName')
  FROM table_name, XMLTABLE(XMLSEQUENCE(EXTRACT(xmltype( SELECT xmltype_column from table_name where COLUMN1 = 10 and  COLUMN2= 'somestring' and COLUMN3 ='N'), '\\ns4:ContactFullName'))) x

I am getting this error:
An exception was thrown: ORA-19102: XQuery string literal expected

any help would be appreciated.
LVL 1
Mr_OzAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
While we wait for the real XML.  I've been playing and came up with a slightly different approach.
drop table tab1 purge;
create table tab1(col1 xmltype);

insert into tab1 values(xmltype('<root>
<ns4:ContactFullName xmlns:ns4="myNamespace">Fred</ns4:ContactFullName>
<ns4:ContactFullName xmlns:ns4="myNamespace">Barney</ns4:ContactFullName>
</root>'));
commit;

select extractvalue(column_value,'/*/text()')
from tab1, table(xmlsequence(extract(col1,'//ns4:ContactFullName','xmlns:ns4="myNamespace"')))
/

Open in new window

0
 
sdstuberConnect With a Mentor Commented:
try this...


select extractvalue(value(x),'/ns4:ContactFullName')
from table_name,xmltable('//ns4:ContactFullName' passing xmltype_column) x
where column1 = 10 and column2 = 'somestring' and column3 = 'N'
0
 
slightwv (䄆 Netminder) Commented:
I'm not sure why you are doing the XMLTABLE.  This works in 10.2.0.3.
drop table tab1 purge;
create table tab1(col1 xmltype);

insert into tab1 values(xmltype('<ns4:ContactFullName xmlns:ns4="myNamespace">Fred</ns4:ContactFullName>'));
commit;

select extractvalue(col1,'/ns4:ContactFullName','xmlns:ns4="myNamespace"') from tab1 t1;

Open in new window

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
sdstuberCommented:
if there is only one instance of that node for each row  then use extractvalue as slightwv has indicated.

if there could be more than one instance of that node for each row then use xmltable.
0
 
slightwv (䄆 Netminder) Commented:
>>if there could be more than one instance

Good point.  Didn't think of that.
0
 
sdstuberCommented:
the question doesn't specify either way,  so having an example of single node is just as useful as an example of multi-node.

good catch on your part too!
0
 
Mr_OzAuthor Commented:
Hmm ok I got this error.

An exception was thrown: ORA-19228: XP0008 - undeclared identifier: prefix 'ns4' local-name 'ns4:ContactFullName'

if I take out the namespace I get this error

An exception was thrown: ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X'

also this tag is nested do I need to give the full path from the root node?

Thanks
0
 
Mr_OzAuthor Commented:
In answer to your question there is a possibility that this tag can occur more than once.
0
 
sdstuberCommented:
to fix the OGC_X problem change the "x" alias to "xx"

for the namespace error
you can either remove the namespace, or declare the namespace with the xmlnamespaces function

no, you do not need to specify the full path, that's what // is for
0
 
sdstuberConnect With a Mentor Commented:
declaring your namespaces might look something like this...


select extractvalue(value(xx),'/ns4:ContactFullName')
from table_name,xmltable(
       xmlnamespaces('http://your.name.space.here' AS "ns4"),
       '//ns4:ContactFullName' passing xmltype_column) xx
where column1 = 10 and column2 = 'somestring' and column3 = 'N'
0
 
Mr_OzAuthor Commented:
OK well for now I took out the namespace just to see if I can get it working without it.  I think that is going to be a problem because no namespace is declared when it is inserted, since the xmltype data that goes into that column can have various namespaces.

But to deal with the first problem I replaced the x's with xx and now I get this error.

An exception was thrown: ORA-00904: "XX": invalid identifier
0
 
Mr_OzAuthor Commented:
OK let me try the namespace solution quick.
0
 
Mr_OzAuthor Commented:
OK yes I think what you did fixed the name space error but running your code I am still getting An exception was thrown: ORA-00904: "XX": invalid identifier.

Thanks
0
 
sdstuberCommented:
did you replace it in both places?
0
 
Mr_OzAuthor Commented:
Yes I actually copy and pasted from yours.
0
 
sdstuberCommented:
I stripped out the namespace and this works fine...

SELECT EXTRACTVALUE(VALUE(xx), 'ContactFullName')
FROM table_name, XMLTABLE(
                     '//ContactFullName'
                     PASSING xmltype_column
                 ) xx
WHERE column1 = 10 AND column2 = 'somestring' AND column3 = 'N'


if this doesn't work.  Can you post your xml and namespace to work from?
0
 
Mr_OzAuthor Commented:
unforutnately I cannot post either, due to confidentialtiy reasons.  But I can say that the namespace appears to not be an issue whether it is there or not I get the same issue.
0
 
Mr_OzAuthor Commented:
I can try to set up a dummy xml and table that I can reproduce the issue on.  Give me a few min.
0
 
Mr_OzAuthor Commented:
OK here it is
  create table tab1(col1 xmltype, col2 number(3));
 
  insert into tab1 values(xmltype('<Root>
      <Category1><Address><CityName>Rochester</CityName></Address><ContactList><Contact><ContactFirstName>Jane</ContactFirstName>            <ContactFullName>Jane Jones</ContactFullName><ContactLastName>Jones</ContactLastName></Contact><Phone><PhoneExtensionNumber>6666</PhoneExtensionNumber>                        <PhoneNumber>4004445555</PhoneNumber></Phone><Sub><Contact><ContactFullName>Fred Smith</ContactFullName></Contact><Phone>      <PhoneNumber>3123213213</PhoneNumber></Phone></Sub></ContactList></Category1></Root>'),10);

select extractvalue(value(xx),'/ContactFullName')
from tab1,xmltable('//ContactFullName' passing Col1)xx
where col2=10


Error given
An exception was thrown: ORA-00904: "XX": invalid identifier       


Formatted xml

<Root>
      <Category1>
            <Address>
                  <CityName>Rochester</CityName>
            </Address>
            <ContactList>
                  <Contact>
                        <ContactFirstName>Jane</ContactFirstName>
                        <ContactFullName>Jane Jones</ContactFullName>
                        <ContactLastName>Jones</ContactLastName>
                  </Contact>
                  <Phone>
                        <PhoneExtensionNumber>6666</PhoneExtensionNumber>
                        <PhoneNumber>4004445555</PhoneNumber>
                  </Phone>
                  <Sub>
                        <Contact>
                              <ContactFullName>Fred Smith</ContactFullName>
                        </Contact>
                        <Phone>
                              <PhoneNumber>3123213213</PhoneNumber>
                        </Phone>
                  </Sub>
            </ContactList>
      </Category1>
</Root>
eeSample.txt
0
 
Mr_OzAuthor Commented:
slightwv:

You latest post does work but does not account for the fact that I need to select a specific row before querying the column.


I am also not really clear on what this does:
(column_value,'/*/text()')

Thanks
0
 
Mr_OzAuthor Commented:
OK never mind on the last part of my comment I understand (column_value,'/*/text()')
I just was not thinking straight while looking at it.
0
 
slightwv (䄆 Netminder) Commented:
>>I need to select a specific row before querying the column.

I don't understand what this means.  If you are referring to the where clause in your original post, just add the where clause to my example.
0
 
Mr_OzAuthor Commented:
Yes nevermind sorry I forgot to post back.  slightwv your solution worked for me thanks.  I gave a few points to sdstuber for all of his assistance as well.
0
 
Mr_OzAuthor Commented:
Thanks alot guys
0
 
acportoCommented:
When I try to use the command

update from mytable
set my_column_xmldatatye = xmltype(:data_xml)
where
  id_mytable = xxxyyy

the oracle don't accpet, this error message is displayed

ORA-01461: can bind a LONG value only for insert into a LONG column.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.