?
Solved

Problem with Xquery XMLType

Posted on 2010-01-08
26
Medium Priority
?
2,376 Views
Last Modified: 2013-11-11
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.
0
Comment
Question by:Mr_Oz
  • 13
  • 7
  • 4
  • +1
25 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 26211436
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26211594
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 26211610
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26211626
>>if there could be more than one instance

Good point.  Didn't think of that.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 26211647
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
 
LVL 1

Author Comment

by:Mr_Oz
ID: 26211651
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
 
LVL 1

Author Comment

by:Mr_Oz
ID: 26211661
In answer to your question there is a possibility that this tag can occur more than once.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 26211685
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 26211705
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
 
LVL 1

Author Comment

by:Mr_Oz
ID: 26211780
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
 
LVL 1

Author Comment

by:Mr_Oz
ID: 26211785
OK let me try the namespace solution quick.
0
 
LVL 1

Author Comment

by:Mr_Oz
ID: 26211849
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 26211857
did you replace it in both places?
0
 
LVL 1

Author Comment

by:Mr_Oz
ID: 26211891
Yes I actually copy and pasted from yours.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 26211937
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1600 total points
ID: 26211967
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
 
LVL 1

Author Comment

by:Mr_Oz
ID: 26212069
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
 
LVL 1

Author Comment

by:Mr_Oz
ID: 26212086
I can try to set up a dummy xml and table that I can reproduce the issue on.  Give me a few min.
0
 
LVL 1

Author Comment

by:Mr_Oz
ID: 26212386
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
 
LVL 1

Author Comment

by:Mr_Oz
ID: 26212933
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
 
LVL 1

Author Comment

by:Mr_Oz
ID: 26212981
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26213456
>>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
 
LVL 1

Author Comment

by:Mr_Oz
ID: 26213507
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
 
LVL 1

Author Closing Comment

by:Mr_Oz
ID: 31674616
Thanks alot guys
0
 

Expert Comment

by:acporto
ID: 33154574
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month15 days, 20 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question