| XML parsing using PL\SQL [message #135582] |
Thu, 01 September 2005 05:30  |
bxsing2
Messages: 11
Registered: March 2005
|
Junior Member |
|
|
I've got the soap response from a webservice in a string of varchar2(32767).I am trying to get a Procedure for Parsing the elements from the soap response.
Can someone help me out with a sample stub?
Thanks,
Bags
|
|
|
|
| Re: XML parsing using PL\SQL [message #136120 is a reply to message #135582] |
Tue, 06 September 2005 04:48  |
Frank Naude
Messages: 4579
Registered: April 1998
|
Senior Member |
|
|
If you are using Oracle 9i or 10g you can use the supplied "extract" and "extractValue" functions. Here is a quick example:
SQL> create table xml_tab (xml XMLType);
Table created.
SQL>
SQL> insert into xml_tab values ('<DATA>
2 <Common>
3 <Field>
4 <Label>Begin Date </Label>
5 <Value Name="BEG_DATE">02.05.2006</Value>
6 </Field>
7 <Field>
8 <Label>End Date</Label>
9 <Value Name="END_DATE">01.06.2016</Value>
10 </Field>
11 <Field>
12 <Label>type</Label>
13 <Value Name="TYPE">Example</Value>
14 </Field>
15 </Common>
16 <Customer>
17 <Field>
18 <Label>Name</Label>
19 <Value Name="CUST_NAME">Smith</Value>
20 </Field>
21 <Field>
22 <Label>Date of birth</Label>
23 <Value Name="CUST_BIRTH_DATE">27.08.1972</Value>
24 </Field>
25 </Customer>
26 </DATA>');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select extract(xml, '/DATA/Customer/Field/Label') from XML_TAB;
EXTRACT(XML,'/DATA/CUSTOMER/FIELD/LABEL')
------------------------------------------------------------------------
<Label>Name</Label>
<Label>Date of birth</Label>
SQL> select extractValue(xml, '/DATA/Customer/Field/Value[@Name="CUST_BIRTH_DATE"]') from XML_TAB;
EXTRACTVALUE(XML,'/DATA/CUSTOMER/FIELD/VALUE[@NAME="CUST_BIRTH_DATE"]')
------------------------------------------------------------------------
27.08.1972
Best regards.
Frank
|
|
|
|
| Re: XML parsing using PL\SQL [message #175811 is a reply to message #136120] |
Mon, 05 June 2006 09:10 |
glsantini
Messages: 2
Registered: May 2006
|
Junior Member |
|
|
Frank,
maybe you can help me too.
I would to obtain this result
START
surami 1 - ralf 2 - 25 3 -
sanchez 1 - ana 2 - 18 3 -
FINISH
from this PL/Sql block
DECLARE
x XMLTYPE :=
XMLTYPE(
'<?xml version="1.0" encoding="UTF-8"?>
<Output>
<Find>
<queryName>NameAndAge</queryName>
<listField>
<Field>
<id>1</id>
<Name>firstname</Name>
<type>VARCHAR2</type>
</Field>
<Field>
<id>2</id>
<Name>lastname</Name>
<type>VARCHAR2</type>
</Field>
<Field>
<id>3</id>
<Name>age</Name>
<type>NUMBER</type>
</Field>
</listField>
<listRecord>
<record>
<Field id="1">surami</Field>
<Field id="2">ralf</Field>
<Field id="3">25</Field>
</record>
<record>
<Field id="1">sanchez</Field>
<Field id="2">ana</Field>
<Field id="3">18</Field>
</record>
</listRecord>
</Find>
</Output>'
);
BEGIN
DBMS_OUTPUT.PUT_LINE('START ');
FOR i IN (
SELECT EXTRACTVALUE(value(t),'/Field[@id="1"]') field1,
EXTRACTVALUE(value(t),'/Field[@id="2"]') field2,
EXTRACTVALUE(value(t),'/Field[@id="3"]') field3
FROM TABLE(XMLSEQUENCE(
EXTRACT(x,'Output/Find/listRecord/record'))
) t)
LOOP
DBMS_OUTPUT.PUT_LINE(
' '||nvl(SUBSTR(i.field1,1,255),'NOTFIND') ||' 1 - '||
' '||nvl(SUBSTR(i.field2,1,255),'NOTFIND') ||' 2 - '||
' '||nvl(SUBSTR(i.field3,1,255),'NOTFIND') ||' 3 - ' );
END LOOP;
DBMS_OUTPUT.PUT_LINE('FINISH ');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' ERROR '||SQLERRM);
END;
But dont work, my results is:
START
NOTFIND 1 - NOTFIND 2 - NOTFIND 3 -
NOTFIND 1 - NOTFIND 2 - NOTFIND 3 -
FINISH
Can you seggest me something?
Thank you
Gianluca
|
|
|
|
|
|
| Re: XML parsing using PL\SQL [message #175953 is a reply to message #175833] |
Tue, 06 June 2006 03:27 |
glsantini
Messages: 2
Registered: May 2006
|
Junior Member |
|
|
Thanks, but the real question is: how get value from xml node.
This sentence is uncorrect
SELECT EXTRACTVALUE(value(t),'/Field[@id="1"]') field1
FROM TABLE(XMLSEQUENCE(
EXTRACT(x,'Output/Find/listRecord/record'))) t)
They return NULL.
What could write in the XPATH to obtain "surami"?
What is rules for XPATH?
|
|
|
|
| Re: XML parsing using PL\SQL [message #181628 is a reply to message #135582] |
Mon, 10 July 2006 22:32 |
hobbes
Messages: 173
Registered: January 2006
|
Senior Member |
|
|
The XPath expression in EXTRACTVALUE should start a level up.
SQL> DECLARE
2 x XMLTYPE :=
3 XMLTYPE(
4 '<?xml version="1.0" encoding="UTF-8"?>
5 <Output>
6 <Find>
7 <queryName>NameAndAge</queryName>
8 <listField>
9 <Field>
10 <id>1</id>
11 <Name>firstname</Name>
12 <type>VARCHAR2</type>
13 </Field>
14 <Field>
15 <id>2</id>
16 <Name>lastname</Name>
17 <type>VARCHAR2</type>
18 </Field>
19 <Field>
20 <id>3</id>
21 <Name>age</Name>
22 <type>NUMBER</type>
23 </Field>
24 </listField>
25 <listRecord>
26 <record>
27 <Field id="1">surami</Field>
28 <Field id="2">ralf</Field>
29 <Field id="3">25</Field>
30 </record>
31 <record>
32 <Field id="1">sanchez</Field>
33 <Field id="2">ana</Field>
34 <Field id="3">18</Field>
35 </record>
36 </listRecord>
37 </Find>
38 </Output>'
39 );
40
41 BEGIN
42 DBMS_OUTPUT.PUT_LINE('START ');
43 FOR i IN (
44 SELECT EXTRACTVALUE(value(t),'/record/Field[@id="1"]') field1,
45 EXTRACTVALUE(value(t),'/record/Field[@id="2"]') field2,
46 EXTRACTVALUE(value(t),'/record/Field[@id="3"]') field3
47 FROM TABLE(XMLSEQUENCE(
48 EXTRACT(x,'Output/Find/listRecord/record'))
49 ) t)
50 LOOP
51 DBMS_OUTPUT.PUT_LINE(
52 ' '||nvl(SUBSTR(i.field1,1,255),'NOTFIND') ||' 1 - '||
53 ' '||nvl(SUBSTR(i.field2,1,255),'NOTFIND') ||' 2 - '||
54 ' '||nvl(SUBSTR(i.field3,1,255),'NOTFIND') ||' 3 - ');
55 END LOOP;
56 DBMS_OUTPUT.PUT_LINE('FINISH ');
57 EXCEPTION
58 WHEN OTHERS THEN
59 DBMS_OUTPUT.PUT_LINE(' ERROR '||SQLERRM);
60 END;
61
62 /
START
surami 1 - ralf 2 - 25 3 -
sanchez 1 - ana 2 - 18 3 -
FINISH
PL/SQL procedure successfully completed. |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
