Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Extract data from XML having multiple levels of multiple subnodes

1024359Aug 29 2017 — edited Sep 11 2017

Hi,

I try to extract data from an XML with multiple subnodes. The first part (the ParentLineItems) works. But I have no Idea how to get the child nodes (TextModules of the ParentLineItems and ChildLineItems that have again TextModules).

Does somebody know how to do this?

The database version is 11.2.

CREATE TABLE d
(
    thexml   XMLTYPE
);
DECLARE
    x   XMLTYPE
        := xmltype (
               '<?xml version="1.0" encoding="UTF-8"?>
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/" xmlns:a="http://www.test.com/2016/04/quots/types">
   <s:Body>
      <GetLatestPricesAndTextsForOutdatedLineItemsResponse xmlns="http://www.test.com/2016/04/quots/webcrmintegrationservice">
         <GetLatestPricesAndTextsForOutdatedLineItemsResult xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            <a:Acknowledge>Success</a:Acknowledge>
            <a:Message/>
            <a:WebCrmLineItemPricesAndTexts>
               <a:WebCrmLineItemPricesAndTextsItem>
                  <a:ParentLineItem>
                     <a:WebCrmLineItemNumber>12312321321</a:WebCrmLineItemNumber>
                     <a:QuotsLineItemNumber>{7213C65A-2DAD-423F-8F57-55A51CCA0734}</a:QuotsLineItemNumber>
                     <a:SalesPrice>2</a:SalesPrice>
                     <a:CostPrice>1</a:CostPrice>
                     <a:TextModules>
                        <a:TextModule>
                           <a:LanguageCode>EN</a:LanguageCode>
                           <a:ShortText>S</a:ShortText>
                           <a:LongText>L</a:LongText>
                        </a:TextModule>
                        <a:TextModule>
                           <a:LanguageCode>DE</a:LanguageCode>
                           <a:ShortText>S</a:ShortText>
                           <a:LongText>L</a:LongText>
                        </a:TextModule>
                     </a:TextModules>
                  </a:ParentLineItem>
                  <a:ChildLineItems>
                     <a:LineItemPricesAndTexts>
                        <a:WebCrmLineItemNumber/>
                        <a:QuotsLineItemNumber>{101DA7DF-BF51-4E29-B592-2BA4249E77D5}</a:QuotsLineItemNumber>
                        <a:SalesPrice>2212</a:SalesPrice>
                        <a:CostPrice>112</a:CostPrice>
                        <a:TextModules>
                           <a:TextModule>
                              <a:LanguageCode>EN</a:LanguageCode>
                              <a:ShortText>S</a:ShortText>
                              <a:LongText>L</a:LongText>
                           </a:TextModule>
                           <a:TextModule>
                              <a:LanguageCode>DE</a:LanguageCode>
                              <a:ShortText>S</a:ShortText>
                              <a:LongText>L</a:LongText>
                           </a:TextModule>
                        </a:TextModules>
                     </a:LineItemPricesAndTexts>
                     <a:LineItemPricesAndTexts>
                        <a:WebCrmLineItemNumber/>
                        <a:QuotsLineItemNumber>{366C5519-C3E2-42C5-AA54-28CA8BD4C139}</a:QuotsLineItemNumber>
                        <a:SalesPrice>2222</a:SalesPrice>
                        <a:CostPrice>112</a:CostPrice>
                        <a:TextModules>
                           <a:TextModule>
                              <a:LanguageCode>EN</a:LanguageCode>
                              <a:ShortText>S</a:ShortText>
                              <a:LongText>L</a:LongText>
                           </a:TextModule>
                           <a:TextModule>
                              <a:LanguageCode>DE</a:LanguageCode>
                              <a:ShortText>S</a:ShortText>
                              <a:LongText>L</a:LongText>
                           </a:TextModule>
                        </a:TextModules>
                     </a:LineItemPricesAndTexts>
                  </a:ChildLineItems>
               </a:WebCrmLineItemPricesAndTextsItem>
               <a:WebCrmLineItemPricesAndTextsItem>
                  <a:ParentLineItem>
                     <a:WebCrmLineItemNumber>22322323212</a:WebCrmLineItemNumber>
                     <a:QuotsLineItemNumber>{EC3FF93B-C1AA-47D1-AF56-B55F50C7AE02}</a:QuotsLineItemNumber>
                     <a:SalesPrice>22</a:SalesPrice>
                     <a:CostPrice>100</a:CostPrice>
                     <a:TextModules>
                        <a:TextModule>
                           <a:LanguageCode>EN</a:LanguageCode>
                           <a:ShortText>S</a:ShortText>
                           <a:LongText>L</a:LongText>
                        </a:TextModule>
                        <a:TextModule>
                           <a:LanguageCode>DE</a:LanguageCode>
                           <a:ShortText>S</a:ShortText>
                           <a:LongText>L</a:LongText>
                        </a:TextModule>
                     </a:TextModules>
                  </a:ParentLineItem>
                  <a:ChildLineItems>
                     <a:LineItemPricesAndTexts>
                        <a:WebCrmLineItemNumber/>
                        <a:QuotsLineItemNumber>{91EC9915-C277-498A-A7B3-0D085174C458}</a:QuotsLineItemNumber>
                        <a:SalesPrice>22222</a:SalesPrice>
                        <a:CostPrice>1111</a:CostPrice>
                        <a:TextModules>
                           <a:TextModule>
                              <a:LanguageCode>EN</a:LanguageCode>
                              <a:ShortText>S</a:ShortText>
                              <a:LongText>L</a:LongText>
                           </a:TextModule>
                           <a:TextModule>
                              <a:LanguageCode>DE</a:LanguageCode>
                              <a:ShortText>S</a:ShortText>
                              <a:LongText>L</a:LongText>
                           </a:TextModule>
                        </a:TextModules>
                     </a:LineItemPricesAndTexts>
                     <a:LineItemPricesAndTexts>
                        <a:WebCrmLineItemNumber/>
                        <a:QuotsLineItemNumber>{FFD1B32B-03FA-42D8-9FB8-9F708137CE5A}</a:QuotsLineItemNumber>
                        <a:SalesPrice>12222</a:SalesPrice>
                        <a:CostPrice>1123</a:CostPrice>
                        <a:TextModules>
                           <a:TextModule>
                              <a:LanguageCode>EN</a:LanguageCode>
                              <a:ShortText>S</a:ShortText>
                              <a:LongText>L</a:LongText>
                           </a:TextModule>
                           <a:TextModule>
                              <a:LanguageCode>DE</a:LanguageCode>
                              <a:ShortText>S</a:ShortText>
                              <a:LongText>L</a:LongText>
                           </a:TextModule>
                        </a:TextModules>
                     </a:LineItemPricesAndTexts>
                     <a:LineItemPricesAndTexts>
                        <a:WebCrmLineItemNumber/>
                        <a:QuotsLineItemNumber>{7E05459D-F9FE-4258-8D1B-EC242476EB74}</a:QuotsLineItemNumber>
                        <a:SalesPrice>555555</a:SalesPrice>
                        <a:CostPrice>555</a:CostPrice>
                        <a:TextModules>
                           <a:TextModule>
                              <a:LanguageCode>EN</a:LanguageCode>
                              <a:ShortText>S</a:ShortText>
                              <a:LongText>L</a:LongText>
                           </a:TextModule>
                           <a:TextModule>
                              <a:LanguageCode>DE</a:LanguageCode>
                              <a:ShortText>S</a:ShortText>
                              <a:LongText>L</a:LongText>
                           </a:TextModule>
                        </a:TextModules>
                     </a:LineItemPricesAndTexts>
                  </a:ChildLineItems>
               </a:WebCrmLineItemPricesAndTextsItem>
            </a:WebCrmLineItemPricesAndTexts>
         </GetLatestPricesAndTextsForOutdatedLineItemsResult>
      </GetLatestPricesAndTextsForOutdatedLineItemsResponse>
   </s:Body>
</s:Envelope>'  );
BEGIN
    INSERT INTO d
         VALUES (x);
END;
/
SELECT COUNT (*)
  FROM d;

This Select gives the data of the parent line items:

SELECT x.*
  FROM d,
       XMLTABLE (
           xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' AS "s",
                          'http://www.test.com/2016/04/quots/types' AS "a",
                          DEFAULT 'http://www.test.com/2016/04/quots/webcrmintegrationservice'),
           '/s:Envelope/s:Body/GetLatestPricesAndTextsForOutdatedLineItemsResponse/GetLatestPricesAndTextsForOutdatedLineItemsResult/a:WebCrmLineItemPricesAndTexts/a:WebCrmLineItemPricesAndTextsItem'
           PASSING d.thexml
           COLUMNS lit_id VARCHAR2 (50) PATH 'a:ParentLineItem/a:WebCrmLineItemNumber',
                   lit_nr VARCHAR2 (50) PATH 'a:ParentLineItem/a:QuotsLineItemNumber',
                   sales_price NUMBER PATH 'a:ParentLineItem/a:SalesPrice',
                   cost_price NUMBER PATH 'a:ParentLineItem/a:CostPrice') x;

Thanks in advance

Peter

This post has been answered by _jum on Aug 30 2017

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 9 2017

Added on Aug 29 2017

#performance-availability, #xml-db

4 comments

6,328 views