OraFAQ Forum: JDeveloper, Java & XML » Parsing XML containing array of data

Home » Developer & Programmer » JDeveloper, Java & XML » Parsing XML containing array of data (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Parsing XML containing array of data [message #489018]

Wed, 12 January 2011 22:33

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

_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 #489163 is a reply to message #489159]

Thu, 13 January 2011 10:26

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I think that OP want 2 rows only: conversationId, command and value of the same position but I may be wrong.

Regards
Michel

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

_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

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 &amp(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 &amp 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 &amp?
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

Previous Topic:	WL's WS-Security stack support
Next Topic:	How to change type of a field in Jaxb into a generic wildcard

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 09 08:31:59 CST 2025