| XML for dummies, XLMELEMNT [message #188405] |
Fri, 18 August 2006 07:27  |
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  |
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 |
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 |
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 |
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 |
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 |
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 |
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!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
