Problem with Xquery XMLType

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;

Select allOpen in new window

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.

>>if there could be more than one instance

Good point.  Didn't think of that.

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!

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

In answer to your question there is a possibility that this tag can occur more than once.

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

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

OK let me try the namespace solution quick.

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

did you replace it in both places?

Yes I actually copy and pasted from yours.

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?

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.

I can try to set up a dummy xml and table that I can reproduce the issue on.  Give me a few min.

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

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

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.

>>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.

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.

Thanks alot guys

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.