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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 Ray 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 Ray Commented:
Oh! sorry didn't know that already experts have commented.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.