This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Feb 21, 2013 2:09 AM by odie_63 RSS

Need a solution for updating values in table

Nitesh. Explorer
Currently Being Moderated
My oracle version is 11 and my table name is nit_xml in which column name is cust_dtls

Data's of nit_xml table


<root>
<Cust_dtls> Nitesh </Cust_dtls>
</root>

I just want to update Nitesh to Nit ..

Its i inserted values through this way

INSERT INTO nit_xml
VALUES (
'<root>
<Cust_dtls> Nitesh </Cust_dtls>
</root>'

So i tried updating in my own way as like this way



UPDATE nit_xml
SET cust_dtls = '<root>
<Cust_dtls> Nitesh </Cust_dtls>
</root>'
WHERE cust_dtls = '<root>
<Cust_dtls> Nitesh </Cust_dtls>
</root>';

But not working so can anyone give me ideas on XML as how it works and is it possible or not to update through query .. Its note that i want to update column cust_dtls based on same column value and not any other datatype as number or varchar2 because based on other datatype it worked easily and second thing its i am using TOAD application so in that manual updation is possible which is fine but i need to update through query directly ..



Regards,
Nitkhush ...
  • 1. Re: Need a solution for updating values in table
    Nitesh. Explorer
    Currently Being Moderated
    Sorry forgot to mention that cust_dtls is xmltype datatype hope you understood but still wanna clear it ..

    Table structure :

    TABLE nit_xml
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    CUST_DTLS XMLTYPE
  • 2. Re: Need a solution for updating values in table
    Karthick_Arp Guru
    Currently Being Moderated
    you must use [url http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions205.htm#SQLRF06172]UPDATEXML
  • 3. Re: Need a solution for updating values in table
    Nitesh. Explorer
    Currently Being Moderated
    But in that link and in that example updation is based on again string as warehouse_name as 'san fransciso' but i want to update based on same column datatype in which i am facing difficulty to write where clause as can u give some hints ..
  • 4. Re: Need a solution for updating values in table
    Karthick_Arp Guru
    Currently Being Moderated
    Niteshkhush wrote:
    But in that link and in that example updation is based on again string as warehouse_name as 'san fransciso' but i want to update based on same column datatype in which i am facing difficulty to write where clause as can u give some hints ..
    Like this?
    update nit_xml 
       set cust_dtls = updatexml(cust_dtls, '/root/Cust_dtls/text()', 'Nit')
     where trim(extract(cust_dtls, '/root/Cust_dtls/text()')) = 'Nitesh'
  • 5. Re: Need a solution for updating values in table
    Nitesh. Explorer
    Currently Being Moderated
    Thank you so much Karthick...
  • 6. Re: Need a solution for updating values in table
    Nitesh. Explorer
    Currently Being Moderated
    Sorry but one more question its i am trying to update number but its not working and i tried changing this alone '/root/Cust_dtls/text()' as '/root/Cust_dtls/number' .. I mean how to update numbers then . Its if XML data's like this means

    <H>
    <x1>
    <cust_dtls>Nitesh</cust_dtls>,
    <cust_dtls>9790755104</cust_dtls>,
    <cust_dtls>Ambattur</cust_dtls>
    </x1>
    <x2>
    <cust_dtls>Bharathi</cust_dtls>,
    <cust_dtls>123</cust_dtls>,
    <cust_dtls>Mylapore</cust_dtls>
    </x2>
    </H>

    I want to change that 979055104 num to some number so i tried here but its not working again and i even tried changing that Ambattur text to other text but that also not working as showing 0 rows updated .. Can We update that part of text alone .. Give me any link in which all DML's on XML are available or else if you have some time means try to tell a bit ..

    Thanks,
    Nitkhush..
  • 7. Re: Need a solution for updating values in table
    Karthick_Arp Guru
    Currently Being Moderated
    Your xml tag seems flawed
    <H>
    <x1>
    <cust_dtls>Nitesh</cust_dtls>,
    <cust_dtls>9790755104</cust_dtls>,
    <cust_dtls>Ambattur</cust_dtls>
    </x1>
    <x2>
    <cust_dtls>Bharathi</cust_dtls>,
    <cust_dtls>123</cust_dtls>,
    <cust_dtls>Mylapore</cust_dtls>
    </x2>
    </H>
    You have same tag name cust_dtls for all the three elements, why is that? And what about the Comma(,) after the tags?
  • 8. Re: Need a solution for updating values in table
    Nitesh. Explorer
    Currently Being Moderated
    Okay I removed comma's sorry i didnt notice that and also i changed tags as

    <H>
    <x1>
    <cust_name>Nitesh</cust_name>
    <cust_phone>9790755104</cust_phone>
    <cust_place>Ambattur</cust_place>
    </x1>
    <x2>
    <cust_name>Bharathi</cust_name>
    <cust_phone>123</cust_phone>
    <cust_place>Mylapore</cust_place>
    </x2>
    </H>

    and i tried updating this way but still not working..

    UPDATE nit_xml
    SET cust_dtls = UPDATEXML (cust_dtls, '/root/Cust_phone/text()', 132345)
    WHERE TRIM (EXTRACT (cust_dtls, '/root/Cust_phone/text()')) = 9790755104;
  • 9. Re: Need a solution for updating values in table
    Karthick_Arp Guru
    Currently Being Moderated
    Niteshkhush wrote:
    Okay I removed comma's sorry i didnt notice that and also i changed tags as

    <H>
    <x1>
    <cust_name>Nitesh</cust_name>
    <cust_phone>9790755104</cust_phone>
    <cust_place>Ambattur</cust_place>
    </x1>
    <x2>
    <cust_name>Bharathi</cust_name>
    <cust_phone>123</cust_phone>
    <cust_place>Mylapore</cust_place>
    </x2>
    </H>

    and i tried updating this way but still not working..

    UPDATE nit_xml
    SET cust_dtls = UPDATEXML (cust_dtls, '/root/Cust_phone/text()', 132345)
    WHERE TRIM (EXTRACT (cust_dtls, '/root/Cust_phone/text()')) = 9790755104;
    XML Tags are case sensitive. Your tag is "cust_phone" In your update you have used "Cust_phone"
  • 10. Re: Need a solution for updating values in table
    odie_63 Guru
    Currently Being Moderated
    Hi,

    If you want help, start by giving us relevant information such as full database version, DDL for the table and INSERT statement to reproduce the case.
    Most importantly, provide a valid XML document close enough to your real structure.
  • 11. Re: Need a solution for updating values in table
    odie_63 Guru
    Currently Being Moderated
    and i tried updating this way but still not working..

    UPDATE nit_xml
    SET cust_dtls = UPDATEXML (cust_dtls, '/root/Cust_phone/text()', 132345)
    WHERE TRIM (EXTRACT (cust_dtls, '/root/Cust_phone/text()')) = 9790755104;
    There's no <root> or <Cust_phone> elements in the XML.
    And the WHERE clause is not correct either.
  • 12. Re: Need a solution for updating values in table
    Nitesh. Explorer
    Currently Being Moderated
    My oracle version is 11 and my table name is nit_xml in which column name is cust_dtls

    TABLE nit_xml
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    CUST_DTLS XMLTYPE

    This is new data I have inserted newly now ..

    <H>
    <cust_dtls>
    <cust_name>Nitesh</cust_name>
    <cust_phone>9790755104</cust_phone>
    <cust_place>Ambattur</cust_place>
    </cust_dtls>
    <x2>
    <cust_name>Bharathi</cust_name>
    <cust_phone>123</cust_phone>
    <cust_place>Mylapore</cust_place>
    </x2>
    </H>

    Update query :

    UPDATE nit_xml
    SET cust_dtls = UPDATEXML (cust_dtls, '/root/cust_phone/text()', 132345)
    WHERE TRIM (EXTRACT (cust_dtls, '/root/cust_phone/text()')) = 9790755104;

    Its showing as 0 rows updated i cant find out any reason as what's happening .. I want to know also as why without TRIM its extraction is not possible ...
  • 13. Re: Need a solution for updating values in table
    odie_63 Guru
    Currently Being Moderated
    Niteshkhush wrote:
    My oracle version is 11
    No.

    Full version number please :
    SELECT * FROM v$version;
    Its showing as 0 rows updated i cant find out any reason as what's happening ..
    I still don't see any tag named "root" in the document, do you?
  • 14. Re: Need a solution for updating values in table
    Karthick_Arp Guru
    Currently Being Moderated
    Niteshkhush wrote:
    My oracle version is 11 and my table name is nit_xml in which column name is cust_dtls

    TABLE nit_xml
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    CUST_DTLS XMLTYPE

    This is new data I have inserted newly now ..

    <H>
    <cust_dtls>
    <cust_name>Nitesh</cust_name>
    <cust_phone>9790755104</cust_phone>
    <cust_place>Ambattur</cust_place>
    </cust_dtls>
    <x2>
    <cust_name>Bharathi</cust_name>
    <cust_phone>123</cust_phone>
    <cust_place>Mylapore</cust_place>
    </x2>
    </H>

    Update query :

    UPDATE nit_xml
    SET cust_dtls = UPDATEXML (cust_dtls, '/root/cust_phone/text()', 132345)
    WHERE TRIM (EXTRACT (cust_dtls, '/root/cust_phone/text()')) = 9790755104;

    Its showing as 0 rows updated i cant find out any reason as what's happening .. I want to know also as why without TRIM its extraction is not possible ...
    Your XPath is incorrect. it should be /H/cust_dtls/cust_phone/text(). You dont have a tag as root. Its H.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points