Oracle Select Query on XML message

Hello Gurus,

I''m kinda new to using XML in Oracle procedures. Please guide.

1) Can i have (Attached) XML as input to the the stored procedure?
2) If Yes for # 1, I need to "XMLTYPE(extractValue" follwing elements fromthe xml.
  a) Needed_Element_1, Needed_Element_2 and Needed_Element_3
  b) Multiple More_Elements_Needed_1, More_Elements_Needed_2 and More_Elements_Needed_3
3) Once i have the select query on this XML and get the elements bulk collect into nested table I can use in according in my SP.

Need help with # 1 and # 2.

Thank You
sql-xml.txt
loginboyAsked:
Who is Participating?
 
sdstuberCommented:
you can test the query above with the original xml text like this...


                SELECT needed_element_1,
                       needed_element_2,
                       needed_element_3,
                       more_elements_needed_1
                  FROM XMLTABLE(
                           xmlnamespaces(
                               'http://tempuri.org' AS "x",
                               'http://www.w3.org/2003/05/soap-envelope' AS "soap",
                               'http://www.w3.org/2001/XMLSchema-instance' AS "xsi",
                               'http://www.w3.org/2001/XMLSchema' AS "xsd",
                               DEFAULT 'http://tempuri.org/'
                           ),
                           '//VA'
                           PASSING xmltype(
                                       '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <soap:Body>
      <AIA xmlns="http://tempuri.org/">
         <AIB>
            <VA>
               <Needed_Element_1>ABC</Needed_Element_1>
               <Needed_Element_2>123</Needed_Element_2>
               <Needed_Element_3>XYZ</Needed_Element_3>
               <VE>FE28</VE>
               <VF>BLACK</VF>
               <VG>04V7</VG>
               <VH>HABANERO</VH>
               <AA>
                  <More_Elements_Needed>
                     <More_Elements_Needed_1>TEST1</More_Elements_Needed_1>
                     <More_Elements_Needed_2>NAME Of TEST1</More_Elements_Needed_2>
                     <More_Elements_Needed_3>DESC Of TEST1</More_Elements_Needed_3>
                  </More_Elements_Needed>
                  <More_Elements_Needed>
                     <More_Elements_Needed_1>TEST2</More_Elements_Needed_1>
                     <More_Elements_Needed_2>NAME Of TEST2</More_Elements_Needed_2>
                     <More_Elements_Needed_3>DESC Of TEST2</More_Elements_Needed_3>
                  </More_Elements_Needed>
                  <More_Elements_Needed>
                     <More_Elements_Needed_1>TEST3</More_Elements_Needed_1>
                     <More_Elements_Needed_2>NAME Of TEST3</More_Elements_Needed_2>
                     <More_Elements_Needed_3>DESC Of TEST3</More_Elements_Needed_3>
                  </More_Elements_Needed>
                  <More_Elements_Needed>
                     <More_Elements_Needed_1>TEST4</More_Elements_Needed_1>
                     <More_Elements_Needed_2>NAME Of TEST4</More_Elements_Needed_2>
                     <More_Elements_Needed_3>DESC Of TEST4</More_Elements_Needed_3>
                  </More_Elements_Needed>
                  <More_Elements_Needed>
                     <More_Elements_Needed_1>TEST5</More_Elements_Needed_1>
                     <More_Elements_Needed_2>NAME Of TEST5</More_Elements_Needed_2>
                     <More_Elements_Needed_3>DESC Of TEST5</More_Elements_Needed_3>
                  </More_Elements_Needed>
               </AA>
            </VA>
            <errors>
               <errSeNeeded_Element_3rity>0</errSeNeeded_Element_3rity>
               <errMessage/>
            </errors>
         </AIB>
      </AIA>
   </soap:Body>
</soap:Envelope>'
                                   )
                           COLUMNS needed_element_1 VARCHAR2(20) PATH '/VA/Needed_Element_1',
                                   needed_element_2 VARCHAR2(20) PATH '/VA/Needed_Element_2',
                                   needed_element_3 VARCHAR2(20) PATH '/VA/Needed_Element_3',
                                   more XMLTYPE PATH '/VA'
                       ) x,
                       XMLTABLE(
                           xmlnamespaces(
                               'http://tempuri.org' AS "x",
                               'http://www.w3.org/2003/05/soap-envelope' AS "soap",
                               'http://www.w3.org/2001/XMLSchema-instance' AS "xsi",
                               'http://www.w3.org/2001/XMLSchema' AS "xsd",
                               DEFAULT 'http://tempuri.org/'
                           ),
                           '//More_Elements_Needed_1'
                           PASSING x.more
                           COLUMNS more_elements_needed_1 VARCHAR2(20) PATH 'text()'
                       )

Open in new window

0
 
ThommyCommented:
Your stored procedure may look like this...
CREATE OR REPLACE PROCEDURE XMLProc (inputXML in xmltype, outputxml out xmltype) IS
--outxml xmltype;
BEGIN   
  outputxml:=inputxml;
END XMLProc;

Open in new window


And you can call the above procedure passing your xml as follows...
DECLARE 
  INPUTXML XMLTYPE;
  OUTPUTXML XMLTYPE;

BEGIN 
  INPUTXML := XMLTYPE('<soap:EnNeeded_Element_3lope xmlns:soap="http://www.w3.org/2003/05/soap-enNeeded_Element_3lope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <soap:Body>
      <AIA xmlns="http://tempuri.org/">
         <AIB>
            <VA>
               <Needed_Element_1>ABC</Needed_Element_1>
               <Needed_Element_2>123</Needed_Element_2>
               <Needed_Element_3>XYZ</Needed_Element_3>
               <VE>FE28</VE>
               <VF>BLACK</VF>
               <VG>04V7</VG>
               <VH>HABANERO</VH>
               <AA>
                  <More_Elements_Needed>
                     <More_Elements_Needed_1>TEST1</More_Elements_Needed_1>
                     <More_Elements_Needed_2>NAME Of TEST1</More_Elements_Needed_2>
                     <More_Elements_Needed_3>DESC Of TEST1</More_Elements_Needed_3>
                  </More_Elements_Needed>
                  <AB>
                     <More_Elements_Needed_1>TEST2</More_Elements_Needed_1>
                     <More_Elements_Needed_2>NAME Of TEST1</More_Elements_Needed_2>
                     <More_Elements_Needed_3>DESC Of TEST1</More_Elements_Needed_3>
                  </AB>
                  <AB>
                     <More_Elements_Needed_1>TEST2</More_Elements_Needed_1>
                     <More_Elements_Needed_2>NAME Of TEST1</More_Elements_Needed_2>
                     <More_Elements_Needed_3>DESC Of TEST1</More_Elements_Needed_3>
                  </AB>
                  <AB>
                     <More_Elements_Needed_1>TEST2</More_Elements_Needed_1>
                     <More_Elements_Needed_2>NAME Of TEST1</More_Elements_Needed_2>
                     <More_Elements_Needed_3>DESC Of TEST1</More_Elements_Needed_3>
                  </AB>
                  <AB>
                     <More_Elements_Needed_1>TEST2</More_Elements_Needed_1>
                     <More_Elements_Needed_2>NAME Of TEST1</More_Elements_Needed_2>
                     <More_Elements_Needed_3>DESC Of TEST1</More_Elements_Needed_3>
                  </AB>
               </AA>
            </VA>
            <errors>
               <errSeNeeded_Element_3rity>0</errSeNeeded_Element_3rity>
               <errMessage/>
            </errors>
         </AIB>
      </AIA>
   </soap:Body>
</soap:EnNeeded_Element_3lope>
');

  XMLPROC ( INPUTXML,OUTPUTXML );
 END;

Open in new window

0
 
loginboyAuthor Commented:
Thanks for the comment Thommy. Actually, I need help with converting (reading) this XML into virtual tables and columns.

using XMLTABLE, Extactvalue , etc., functions
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ThommyCommented:
This is an example how you can query an XML using Oracle Standard function XMLTable(..)
SELECT *
     FROM  XMLTABLE ('/employees/employee'
                    PASSING XMLTYPE('<employees>
    <employee type="admin" emplid="1111">
        <firstname>John</firstname>
        <lastname>Watson</lastname>
        <age>30</age>
        <email>johnwatson@sh.com</email>
    </employee>
    <employee type="admin" emplid="2222">
        <firstname>Sherlock</firstname>
        <lastname>Homes</lastname>
        <age>32</age>
        <email>sherlock@sh.com</email>
    </employee>
    <employee type="user" emplid="3333">
        <firstname>Jim</firstname>
        <lastname>Moriarty</lastname>
        <age>52</age>
        <email>jim@sh.com</email>
    </employee>
    <employee type="user" emplid="4444">
        <firstname>Mycroft</firstname>
        <lastname>Holmes</lastname>
        <age>41</age>
        <email>mycroft@sh.com</email>
    </employee>
</employees>')
COLUMNS firstname VARCHAR2(30) PATH 'firstname', 
               lastname VARCHAR2(30) PATH 'lastname') 

Open in new window


To get familiar with ORACLE XML You should also have a look at this...
XML DB Developer's Guide
0
 
sdstuberCommented:
what do you want the results to be?

please post rows and columns of expected output that correspond to your input xml

also, your input xml above isn't valid.  it has mismatched tags.  can you post a valid xml for input?
0
 
loginboyAuthor Commented:
Hey sdstuber

Sorry for that; Corrected XML attached.

here is expected rows and columns

Needed_Element_1    Needed_Element_2  Needed_Element_3    More_Elements_Needed_1
ABC                                        123                    XYZ                               TEST1
ABC                                        123                    XYZ                               TEST2
ABC                                        123                    XYZ                               TEST3
ABC                                        123                    XYZ                               TEST4
ABC                                        123                    XYZ                               TEST5
sql-xml.txt
0
 
sdstuberCommented:
assuming your xml is in an XMLTYPE variable called "your_xml_variable"

try this as your cursor


SELECT needed_element_1,
       needed_element_2,
       needed_element_3,
       more_elements_needed_1
  FROM XMLTABLE(
           xmlnamespaces(
               'http://tempuri.org' AS "x",
               'http://www.w3.org/2003/05/soap-envelope' AS "soap",
               'http://www.w3.org/2001/XMLSchema-instance' AS "xsi",
               'http://www.w3.org/2001/XMLSchema' AS "xsd",
               DEFAULT 'http://tempuri.org/'
           ),
           '//VA'
           PASSING your_xml_variable
           COLUMNS needed_element_1 VARCHAR2(20) PATH '/VA/Needed_Element_1',
                   needed_element_2 VARCHAR2(20) PATH '/VA/Needed_Element_2',
                   needed_element_3 VARCHAR2(20) PATH '/VA/Needed_Element_3',
                   more XMLTYPE PATH '/VA'
       ) x,
       XMLTABLE(
           xmlnamespaces(
               'http://tempuri.org' AS "x",
               'http://www.w3.org/2003/05/soap-envelope' AS "soap",
               'http://www.w3.org/2001/XMLSchema-instance' AS "xsi",
               'http://www.w3.org/2001/XMLSchema' AS "xsd",
               DEFAULT 'http://tempuri.org/'
           ),
           '//More_Elements_Needed_1'
           PASSING x.more
           COLUMNS more_elements_needed_1 VARCHAR2(20) PATH 'text()'
                    )
0
 
loginboyAuthor Commented:
awesome @sdstuber.

Let me implement fully and I'll update you. Thanks!
0
 
ThommyCommented:
For querying your special XML you have to define the namespaces...

SELECT *
      FROM  XMLTABLE(
                   XMLNAMESPACES (
                       default 'http://tempuri.org/',
                       'http://www.w3.org/2003/05/soap-enNeeded_Element_3lope' AS "SOAP",
                       'http://www.w3.org/2001/XMLSchema-instance' AS "XSI",
                       'http://www.w3.org/2001/XMLSchema' AS "XSD"
                    ), 
                    'SOAP:EnNeeded_Element_3lope/SOAP:Body/AIA/AIB/VA' 
                    PASSING XMLTYPE('
<soap:EnNeeded_Element_3lope xmlns:soap="http://www.w3.org/2003/05/soap-enNeeded_Element_3lope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
>
<soap:Body>                    
      <AIA xmlns="http://tempuri.org/">
         <AIB>
            <VA>
               <Needed_Element_1>ABC</Needed_Element_1>
               <Needed_Element_2>123</Needed_Element_2>
               <Needed_Element_3>XYZ</Needed_Element_3>
               <VE>FE28</VE>
               <VF>BLACK</VF>
               <VG>04V7</VG>
               <VH>HABANERO</VH>
               <AA>
                  <AB>
                     <More_Elements_Needed_1>TEST1</More_Elements_Needed_1>
                     <More_Elements_Needed_2>NAME Of TEST1</More_Elements_Needed_2>
                     <More_Elements_Needed_3>DESC Of TEST1</More_Elements_Needed_3>
                  </AB>
                  <AB>
                     <More_Elements_Needed_1>TEST2</More_Elements_Needed_1>
                     <More_Elements_Needed_2>NAME Of TEST1</More_Elements_Needed_2>
                     <More_Elements_Needed_3>DESC Of TEST1</More_Elements_Needed_3>
                  </AB>
                  <AB>
                     <More_Elements_Needed_1>TEST2</More_Elements_Needed_1>
                     <More_Elements_Needed_2>NAME Of TEST1</More_Elements_Needed_2>
                     <More_Elements_Needed_3>DESC Of TEST1</More_Elements_Needed_3>
                  </AB>
                  <AB>
                     <More_Elements_Needed_1>TEST2</More_Elements_Needed_1>
                     <More_Elements_Needed_2>NAME Of TEST1</More_Elements_Needed_2>
                     <More_Elements_Needed_3>DESC Of TEST1</More_Elements_Needed_3>
                  </AB>
                  <AB>
                     <More_Elements_Needed_1>TEST2</More_Elements_Needed_1>
                     <More_Elements_Needed_2>NAME Of TEST1</More_Elements_Needed_2>
                     <More_Elements_Needed_3>DESC Of TEST1</More_Elements_Needed_3>
                  </AB>
               </AA>
            </VA>
            <errors>
               <errSeNeeded_Element_3rity>0</errSeNeeded_Element_3rity>
               <errMessage/>
            </errors>
         </AIB>
       </AIA>
     </soap:Body>
   </soap:EnNeeded_Element_3lope>')  
COLUMNS Needed_Element_1 VARCHAR2(30) PATH 'Needed_Element_1',
               Needed_Element_2 VARCHAR2(30) PATH 'Needed_Element_2',
               Needed_Element_3 VARCHAR2(30) PATH 'Needed_Element_3')

Open in new window

0
 
Swadhin RaySenior Technical Engineer Commented:
create table xml_table1 (col1 clob );

insert into xml_table1 values('<AIA xmlns="http://tempuri.org/">
         <AIB>
            <VA>
               <Needed_Element_1>ABC</Needed_Element_1>
               <Needed_Element_2>123</Needed_Element_2>
               <Needed_Element_3>XYZ</Needed_Element_3>
               <VE>FE28</VE>
               <VF>BLACK</VF>
               <VG>04V7</VG>
               <VH>HABANERO</VH>
            </VA>
            <errors>
               <errSeNeeded_Element_3rity>0</errSeNeeded_Element_3rity>
               <errMessage/>
            </errors>
         </AIB>
      </AIA>');



select XMLTYPE(col1).EXTRACT ('AIA/AIB/VA/Needed_Element_1/text()',
                              'xmlns="http://tempuri.org/"').GETSTRINGVAL ()
                              AS NEED_ELEMENT_1,
      XMLTYPE(col1).EXTRACT ('AIA/AIB/VA/Needed_Element_2/text()',
                              'xmlns="http://tempuri.org/"').GETSTRINGVAL ()
                              AS NEED_ELEMENT_2,
      XMLTYPE(col1).EXTRACT ('AIA/AIB/VA/Needed_Element_3/text()',
                              'xmlns="http://tempuri.org/"').GETSTRINGVAL ()
                              AS NEED_ELEMENT_3,
      XMLTYPE(col1).EXTRACT ('AIA/AIB/VA/VE/text()',
                              'xmlns="http://tempuri.org/"').GETSTRINGVAL ()
                              AS VE,
      XMLTYPE(col1).EXTRACT ('AIA/AIB/VA/VF/text()',
                              'xmlns="http://tempuri.org/"').GETSTRINGVAL ()
                              AS VF,
      XMLTYPE(col1).EXTRACT ('AIA/AIB/VA/VG/text()',
                              'xmlns="http://tempuri.org/"').GETSTRINGVAL ()
                              AS VG2
                             
                              from xml_table1;
0
 
Swadhin RaySenior Technical Engineer Commented:
Oh! sorry didn't know that already experts have commented.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.