Home » Developer & Programmer » JDeveloper, Java & XML » Parsing XML containing array of data (Oracle 10g)
Parsing XML containing array of data [message #489018] |
Wed, 12 January 2011 22:33 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hi,
I have to parse an XML and store the data in a table.
My XML :-
WITH data AS (
SELECT XMLTYPE(
'<?xml version=''1.0'' encoding=''UTF-8''?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>
<ns2:executeResponse xmlns:ns2="http://service.axf.imaging.oracle/">
<response>
<conversationId>a949-9614-4e9c-9f49-831ab6cc6a41</conversationId>
<pid>8b10-bed9-46e6-a9f8-9ac64330535c</pid>
<responseCommands>
<command>OPEN_BROWSER</command> <value>V1
</value>
</responseCommands>
</response>
</ns2:executeResponse>
</S:Body>
</S:Envelope>') val from dual)
SELECT extractvalue(val, '//conversationId') conversationId,
extractvalue(value(x), '//command') command,
extractvalue(value(y), '//value') value
FROM data, table(xmlsequence(extract(val, '//command'))) x,
table(xmlsequence(extract(val, '//value'))) y
The output is :-
CONVERSATIONID COMMAND VALUE
a949-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V1
There will be cases when I will get multiple values for the columns conversationid, command, value.
<?xml version=''1.0'' encoding=''UTF-8''?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>
<ns2:executeResponse xmlns:ns2="http://service.axf.imaging.oracle/">
<response>
<conversationId>a949-9614-4e9c-9f49-831ab6cc6a41</conversationId>
<pid>8b10-bed9-46e6-a9f8-9ac64330535c</pid>
<responseCommands>
<command>OPEN_BROWSER</command> <value>V1
</value>
</responseCommands>
<conversationId>iiii-9614-4e9c-9f49-831ab6cc6a41</conversationId>
<pid>iiii-bed9-46e6-a9f8-9ac64330535c</pid>
<responseCommands>
<command>OPEN_BROWSER1</command> <value>V2
</value>
</responseCommands>
</response>
</ns2:executeResponse>
</S:Body>
</S:Envelope>
In that case the output should be as :-
CONVERSATIONID COMMAND VALUE
a949-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V1
iiii-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V2
Not sure how to go about XML parsing when I have multiple values.
Please advice,
Mahi
[Edit MC: changed values "http://....." to V1 and V2 to make it concise and the lines shorter]
[Updated on: Fri, 14 January 2011 01:16] by Moderator Report message to a moderator
|
|
|
Re: Parsing XML containing array of data [message #489159 is a reply to message #489018] |
Thu, 13 January 2011 09:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
Not the most elegant way, but it works:
WITH data AS (
SELECT XMLTYPE(
'<?xml version=''1.0'' encoding=''UTF-8''?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>
<ns2:executeResponse xmlns:ns2="http://service.axf.imaging.oracle/">
<response>
<conversationId>a949-9614-4e9c-9f49-831ab6cc6a41</conversationId>
<pid>8b10-bed9-46e6-a9f8-9ac64330535c</pid>
<responseCommands>
<command>OPEN_BROWSER</command> <value>V1
</value>
</responseCommands>
<conversationId>iiii-9614-4e9c-9f49-831ab6cc6a41</conversationId>
<pid>iiii-bed9-46e6-a9f8-9ac64330535c</pid>
<responseCommands>
<command>OPEN_BROWSER1</command> <value>V2
</value>
</responseCommands>
</response>
</ns2:executeResponse>
</S:Body>
</S:Envelope>') val from dual)
SELECT extractvalue(value(c), '//conversationId') convID,
extractvalue(value(y), '//command') command,
extractvalue(value(y), '//value') value
FROM data,
table(xmlsequence(extract(val, '//conversationId'))) c,
table(xmlsequence(extract(val, '//response'))) x,
table(xmlsequence(extract(x.column_value, '//responseCommands'))) y;
CONVID COMMAND VALUE
-------------------------------------------------------------
a949-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V1
a949-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER1 V2
iiii-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V1
iiii-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER1 V2
[Updated on: Fri, 14 January 2011 01:18] by Moderator Report message to a moderator
|
|
|
|
Re: Parsing XML containing array of data [message #489254 is a reply to message #489163] |
Fri, 14 January 2011 01:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
You could check and compare the position of //conversationId and //responseCommands:
WITH data AS (
SELECT XMLTYPE(
'<?xml version=''1.0'' encoding=''UTF-8''?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>
<ns2:executeResponse xmlns:ns2="http://service.axf.imaging.oracle/">
<response>
<conversationId>a949-9614-4e9c-9f49-831ab6cc6a41</conversationId>
<pid>8b10-bed9-46e6-a9f8-9ac64330535c</pid>
<responseCommands>
<command>OPEN_BROWSER</command> <value>V1
</value>
</responseCommands>
<conversationId>iiii-9614-4e9c-9f49-831ab6cc6a41</conversationId>
<pid>iiii-bed9-46e6-a9f8-9ac64330535c</pid>
<responseCommands>
<command>OPEN_BROWSER1</command> <value>V2
</value>
</responseCommands>
</response>
</ns2:executeResponse>
</S:Body>
</S:Envelope>') val from dual)
SELECT oid, cmd, rval,
xcid.pos pi, xres.pos pr
FROM data,
xmltable('//conversationId'
PASSING val COLUMNS oid VARCHAR2(20) PATH '//conversationId',
pos FOR ORDINALITY) xcid,
xmltable('//responseCommands'
PASSING val COLUMNS cmd VARCHAR2(20) PATH '//command',
rval VARCHAR2(80) PATH '//value',
pos FOR ORDINALITY) xres
WHERE xcid.pos = xres.pos;
OID CMD RVAL PI PR
---------------------------------------------------------------------------
a949-9614-4e9c-9f49- OPEN_BROWSER V1 1 1
iiii-9614-4e9c-9f49- OPEN_BROWSER1 V2 2 2
[Updated on: Fri, 14 January 2011 01:39] by Moderator Report message to a moderator
|
|
|
Re: Parsing XML containing array of data [message #489255 is a reply to message #489163] |
Fri, 14 January 2011 01:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hi Jum,
Michel is right. I needed only two rows as mentioned in my post for the expected records.
I have tried another approach for this,
My XML file is :-
<?xml version='1.0' encoding='UTF-8'?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>
<ns2:executeResponse xmlns:ns2="http://service.axf.imaging.oracle/">
<response>
<conversationId>a949-9614-4e9c-9f49-831ab6cc6a41</conversationId>
<pid>8b10-bed9-46e6-a9f8-9ac64330535c</pid>
<responseCommands>
<command>OPEN_BROWSER</command> <value>V1
</value>
</responseCommands>
<conversationId>iiii-9614-4e9c-9f49-831ab6cc6a41</conversationId>
<pid>iiii-bed9-46e6-a9f8-9ac64330535c</pid>
<responseCommands>
<command>OPEN_BROWSER1</command> <value>V2
</value>
</responseCommands>
</response>
</ns2:executeResponse>
</S:Body>
</S:Envelope>
I have two records starting with <response>
I have to insert the records in table TEST
CREATE TABLE TEST
(
CONVERSATIONID VARCHAR2(300),
PID VARCHAR2(100),
COMMAND VARCHAR2(25),
VALUE VARCHAR2(500)
)
I created a procedure to parse this:-
CREATE OR REPLACE PROCEDURE load_xml_webservice (inxml IN CLOB)
AS
doc xmldom.domdocument;
node xmldom.domnode;
node1 xmldom.domnode;
nodelist xmldom.domnodelist;
conversationid_row TEST%ROWTYPE;
num_nodes NUMBER;
i NUMBER;
BEGIN
doc := DBMS_XMLDOM.newdomdocument (inxml);
node := DBMS_XMLDOM.makenode (doc);
DBMS_XSLPROCESSOR.valueof (node,
'//response',
conversationid_row.conversationid
);
--process all responses
nodelist := DBMS_XSLPROCESSOR.selectnodes (node, '//response');
num_nodes := DBMS_XMLDOM.getlength (nodelist);
DBMS_OUTPUT.put_line ('Nodes: ' || num_nodes);
FOR i IN 0 .. num_nodes - 1
LOOP
node1 := DBMS_XMLDOM.item (nodelist, i);
DBMS_XSLPROCESSOR.valueof (node1,
'./conversationId',
conversationid_row.conversationid
);
DBMS_XSLPROCESSOR.valueof (node1, './pid', conversationid_row.pid);
DBMS_XSLPROCESSOR.valueof (node1,
'.//responseCommands/command',
conversationid_row.command
);
DBMS_XSLPROCESSOR.valueof (node1,
'.//responseCommands/value',
conversationid_row.VALUE
);
INSERT INTO TEST
VALUES conversationid_row;
END LOOP;
END;
To run the above procedure it gives error for the single quotes and &(in the link) at two places in the XML for inserting in table.
I had to add one more quote to
<?xml version='1.0' encoding='UTF-8'?>
<?xml version=''1.0'' encoding=''UTF-8''?>
And I have to change the & in the XML to _amp at two places in the link(V1, V2) to let it insert in table
The below code inserts two records in table test.
declare
nxml clob := '<?xml version=''1.0'' encoding=''UTF-8''?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>
<ns2:executeResponse xmlns:ns2="http://service.axf.imaging.oracle/">
<response>
<conversationId>a949-9614-4e9c-9f49-831ab6cc6a41</conversationId>
<pid>8b10-bed9-46e6-a9f8-9ac64330535c</pid>
<responseCommands>
<command>OPEN_BROWSER</command> <value>V1
</value>
</responseCommands>
<conversationId>iiii-9614-4e9c-9f49-831ab6cc6a41</conversationId>
<pid>iiii-bed9-46e6-a9f8-9ac64330535c</pid>
<responseCommands>
<command>OPEN_BROWSER1</command> <value>V2
</value>
</responseCommands>
</response>
</ns2:executeResponse>
</S:Body>
</S:Envelope>';
begin
load_XML_webservice(nxml);
end;
But Then at times I need to again fetch the data from table test to go to the link V1 or V2 in the <value>, then will I have to again use the replace function to replace _amp to &?
Is the approach I am trying is good or do you have any better advice.
Please suggest!!
Mahi
[Updated on: Fri, 14 January 2011 01:38] by Moderator Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:52:15 CDT 2024
|