Home » Developer & Programmer » JDeveloper, Java & XML » XML for dummies, XLMELEMNT
XML for dummies, XLMELEMNT [message #188405] Fri, 18 August 2006 07:27 Go to next message
apermir
Messages: 4
Registered: August 2006
Junior Member
Sorry but Im really new to Oracle support for XML ,



I have created a XMLELEMENT


insert into DATOSEMPLEADOS (CONTRATS)
(SELECT XMLELEMENT("CONTRATOS",
XMLATTRIBUTES(352 as X_PERSONA )) from dual);

<CONTRATS PERSON_ID = "352"></CONTRATS>





later in the code I want to insert into that XMLType another element inside CONTRATS

<CONTRATS PERSON_ID = "352">
<COMPANY = "MERCEDES">
<ATTR NAME="REG_JURIDICO" MULTI="0">ID_REG</ATTR>
</COMPANY>
<COMPANY = "BMW">
<ATTR NAME="REG_JURIDICO" MULTI="0">ID_REG</ATTR>
</COMPANY>

</CONTRATS>

How can I do that?

Thanks in advance
Antonio
Re: XML for dummies, XLMELEMNT [message #188559 is a reply to message #188405] Sat, 19 August 2006 12:06 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
You can do something like this:
UPDATE DATOSEMPLEADOS x
SET x.CONTRATS = 
  (
    UPDATEXML(x.CONTRATS, 
              '/CONTRATOS', 
              '<New XMLType value>')
WHERE EXTRACTVALUE(x.CONTRATS, '/CONTRATOS/@PERSON_ID') = 352
/
Re: XML for dummies, XLMELEMNT [message #189343 is a reply to message #188559] Thu, 24 August 2006 04:00 Go to previous messageGo to next message
apermir
Messages: 4
Registered: August 2006
Junior Member
Thanks,


A new doubt has arise, is there any way ( using oracle
function) to compare two XML values of a XMLType column.


I had a procedure that mus be run 60.000 times in order
to create 60.000 XML answer (XMLType), i must compare these answers with several values of a XMLType column stored in a oracle Table.

May I code a specific routine to navigate inside the XML structure, comparing each of the keys (Attribs and xmlelements)
or is there any method to support this kind of matching?


Thanks again
Re: XML for dummies, XLMELEMNT [message #189382 is a reply to message #188405] Thu, 24 August 2006 06:32 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
You could cast the XMLType as CLOB and do a DBMS_LOB.COMPARE between the table column value and the input.

Example:
SQL> desc dept1_xml
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 DATA                                               SYS.XMLTYPE

SQL> SELECT x1.data.extract('/*').getClobVal() data
  2  FROM dept1_xml x1
  3  /

DATA
-------------------------------------------------------
<xml_demo_emp>
  <dname>ACCOUNTING</dname>
  <loc>NEW YORK</loc>
</xml_demo_emp>

<xml_demo_emp>
  <dname>RESEARCH</dname>
  <loc>DALLAS</loc>
</xml_demo_emp>


SQL> SELECT x1.data.extract('/*').getClobVal() matcheddata
  2  FROM   dept1_xml x1
  3  WHERE  dbms_lob.compare(
  4    x1.data.getClobVal(),
  5    to_clob('<xml_demo_emp><dname>RESEARCH</dname><loc>DALLAS</loc></xml_demo_emp>')) = 0
  6  /

MATCHEDDATA
----------------------------------------------------------
<xml_demo_emp>
  <dname>RESEARCH</dname>
  <loc>DALLAS</loc>
</xml_demo_emp>

Re: XML for dummies, XLMELEMNT [message #189387 is a reply to message #189382] Thu, 24 August 2006 06:55 Go to previous messageGo to next message
apermir
Messages: 4
Registered: August 2006
Junior Member
Thanks indeed

I will try your solution, i think i can use these workaround
because the values of the column were generated with the same
procedure i will use for the new XMLelements for the comparation.

In a different case in which could exists different formats
and the same meaning ( for example additional spaces or tab between XML tags) , i susppect that there is no a easy workaround ... In order to compare them.
Probably I will have to follow the XML-tree asking if the
elements are the same or not in both XMLTypes...

Even worst is the case of differents orders with the
same meaning ( equivalents XMLs)

ej
XMLTYPE1
Cities
|____ City1.
| |____ Name
| |____...
|
|_____SituationA.
| |___ Cod. situation
| |___ .....
|
|
|____ City2.
| |____ Name
| |____...
|
|_____SituationB.
|___ Cod. situation
|___ .....

XMLTYPE2
Cities
|____ City2.
| |____ Name
| |____...
|
|_____SituationB.
| |___ Cod. situation
| |___ .....
|
|
|____ City1.
| |____ Name
| |____...
|
|_____SituationA.
|___ Cod. situation
|___ .....

They are differents XMLtype , but equivalents.

Maybe im completely wrong, beacuse Im really new to XML world
I thought that perhaps there was some functions/APIs to
treat these problems.
Re: XML for dummies, XLMELEMNT [message #189519 is a reply to message #188405] Fri, 25 August 2006 00:20 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
If you cast the XMLType data as TABLE, then you can access the data relationally using EXTRACT functions - this will take care of the case where the rows are equivalent but not ordered identically.

To ignore blank spaces and tabs, use the TRIM function.

Here is a simple PL/SQL example that returns 'Matched' if XMLType2 contains all elements of XMLType1, else returns 'Not Matched':
SQL> -- Equivalent data, but with spaces and different order
SQL> DECLARE
  2    xmltype1 XMLTYPE := XMLTYPE('<DATA>
  3                                   <LINE>A</LINE>
  4                                   <LINE>B</LINE>
  5                                   <LINE>C</LINE>
  6                                </DATA>');
  7    xmltype2 XMLTYPE := XMLTYPE('<DATA>
  8                                   <LINE> B </LINE>
  9                                   <LINE>A</LINE>
 10                                   <LINE>C </LINE>
 11                                 </DATA>');
 12  
 13    diffcount  NUMBER(3);
 14  
 15  BEGIN
 16  
 17   SELECT COUNT(*) INTO diffcount
 18   FROM   
 19        ((SELECT TRIM(BOTH ' ' FROM EXTRACTVALUE(VALUE(t1), '/*')) 
 20          FROM TABLE(XMLSEQUENCE(EXTRACT(xmltype1, '/DATA/LINE'))) t1)
 21          MINUS
 22         (SELECT TRIM(BOTH ' ' FROM EXTRACTVALUE(VALUE(t2), '/*'))
 23          FROM TABLE(XMLSEQUENCE(EXTRACT(xmltype2, '/DATA/LINE'))) t2));
 24   
 25   IF (diffcount = 0) THEN 
 26     dbms_output.put_line('Matched');
 27   ELSE
 28     dbms_output.put_line('Not Matched');
 29   END IF;
 30  
 31  END;
 32  /
Matched

PL/SQL procedure successfully completed.

SQL> -- Data in XMLType1 not in XMLType2
SQL> DECLARE
  2    xmltype1 XMLTYPE := XMLTYPE('<DATA>
  3                                   <LINE>A</LINE>
  4                                   <LINE>B</LINE>
  5                                   <LINE>C</LINE>
  6                                </DATA>');
  7    xmltype2 XMLTYPE := XMLTYPE('<DATA>
  8                                   <LINE> B </LINE>
  9                                   <LINE>C </LINE>
 10                                 </DATA>');
 11  
 12    diffcount  NUMBER(3);
 13  
 14  BEGIN
 15  
 16   SELECT COUNT(*) INTO diffcount
 17   FROM   
 18        ((SELECT TRIM(BOTH ' ' FROM EXTRACTVALUE(VALUE(t1), '/*')) 
 19          FROM TABLE(XMLSEQUENCE(EXTRACT(xmltype1, '/DATA/LINE'))) t1)
 20          MINUS
 21         (SELECT TRIM(BOTH ' ' FROM EXTRACTVALUE(VALUE(t2), '/*'))
 22          FROM TABLE(XMLSEQUENCE(EXTRACT(xmltype2, '/DATA/LINE'))) t2));
 23   
 24   IF (diffcount = 0) THEN 
 25     dbms_output.put_line('Matched');
 26   ELSE
 27     dbms_output.put_line('Not Matched');
 28   END IF;
 29  
 30  END;
 31  /
Not Matched

PL/SQL procedure successfully completed.

However, depending on the size and complexity of the XML, there would be a performance overhead with this approach. Do you really need to store this data as XMLType? I think the simpler and faster way in this case would be to store the data in relational tables and do all your selects and comparisons using regular SQL.
Re: XML for dummies, XLMELEMNT [message #189634 is a reply to message #189519] Fri, 25 August 2006 05:51 Go to previous messageGo to next message
apermir
Messages: 4
Registered: August 2006
Junior Member
Thanks for your advices and for your time Hobbes.
The XMLType is not optional, it is a requeriment.

I have been looking a second The 9i manual
"XML API Reference - XDK and Oracle XML DB"
Chapter 10 (Oracle XML JavaBeans)

There is a class called "XMLDiff" the manual says
"The class defines an interface for comparing two XML files. It
enables two XML files to be compared to check for their
equivalence. It provides the objects to display the
differences, if any, in a graphical format. The differences can
also be represented as XSL. The corresponding XSL stylesheet
with the differences can be generated as a file or an
XMLDocument object. The first XML file can be transformed into
the second XML file by using the XSL stylesheet generated."

It seems more a task for DOM Document Object Model
(XML DOM) defines a standard way for accessing and manipulating
XML documents.The DOM presents an XML document as a tree
structure, and gives access to the structure through a set of
objects.

I have no idea if it is my responsability as DBA, or even if
it make sense to manage this process inside the DB or not.
Perhaps the right approach is to make the 60.000 XML
comparations in the middle tier ( Aplication server) , it is
not clear for me... I dont want to work hard , learning
and using XMLDiff , if it is not the correct way for solving the Problem (Compare 60.000 XMLType with other 60.000 XMLTypes
in a daily batch night process )
Re: XML for dummies, XLMELEMNT [message #189803 is a reply to message #188405] Sun, 27 August 2006 22:57 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
I have not used XMLDiff so can't really comment on it - from the description it does sound like the solution for you.

You might store the data in relational tables in addition to (not as an alternative to) having them in XMLType - that would really be the simplest, fastest way for finding data equivalence, particularly if there is large volume of data and the XML structure is complex.

All the best!
Previous Topic: Relationships between XML documents
Next Topic: How to implement this in JAVA?
Goto Forum:
  


Current Time: Wed Dec 07 08:28:23 CST 2016

Total time taken to generate the page: 0.15494 seconds