3 Replies Latest reply on Jul 10, 2019 7:07 AM by Khawerr

    Extract values using sql from xml.

    Khawerr

      I want to extract ItemCategoryAssignmentId, OrganizationId, ItemId, OrganizationCode, ItemNumber, CategoryName values using sql from below mentioned xml.

       

      <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing" xmlns:typ="http://xmlns.oracle.com/apps/scm/productModel/items/itemServiceV2/types/">

         <env:Header>

      <wsa:Action>http://xmlns.oracle.com/apps/scm/productModel/items/itemServiceV2//ItemService/findItemResponse</wsa:Action>

      <wsa:MessageID>urn:uuid:e2157f90-3928-4f9c-b104-57e0f7d39270</wsa:MessageID>

         </env:Header>

         <env:Body>

      <ns0:findItemResponse xmlns:ns0="http://xmlns.oracle.com/apps/scm/productModel/items/itemServiceV2/types/">

      <ns2:result xsi:type="ns0:DataObjectResult" xmlns:ns2="http://xmlns.oracle.com/apps/scm/productModel/items/itemServiceV2/types/" xmlns:ns1="http://xmlns.oracle.com/apps/scm/productModel/items/itemServiceV2/" xmlns:tns="http://xmlns.oracle.com/adf/svc/errors/" xmlns:ns0="http://xmlns.oracle.com/adf/svc/types/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

      <ns0:Value xsi:type="ns1:Item" xmlns:ns6="http://xmlns.oracle.com/apps/scm/productCatalogManagement/advancedItems/flex/egoItemEff/item/categories/" xmlns:ns7="http://xmlns.oracle.com/apps/scm/productModel/items/flex/item/" xmlns:ns8="http://xmlns.oracle.com/apps/scm/productModel/items/flex/itemGdf/">

      <ns1:ItemId>300000007851975</ns1:ItemId>

      <ns1:OrganizationId>300000001092261</ns1:OrganizationId>

      <ns1:OrganizationCode>RIO</ns1:OrganizationCode>

      <ns1:ItemNumber>00KXADW212</ns1:ItemNumber>

                     <ns1:ItemDescription>Doe Teal (Unstitched) by KX3</ns1:ItemDescription>

      <ns1:ItemCategory>

      <ns1:ItemId>300000007851975</ns1:ItemId>

      <ns1:OrganizationId>300000001092261</ns1:OrganizationId>

                        <ns1:ItemCatalog>RADIUS_CATALOG</ns1:ItemCatalog>

      <ns1:CategoryName>COM.L2.L3.L4.L5</ns1:CategoryName>

      <ns1:SequenceNumber xsi:nil="true"/>

      <ns1:ItemCategoryAssignmentId>300000007851976</ns1:ItemCategoryAssignmentId>

      <ns1:CreationDateTime>2018-02-23T06:12:50.023Z</ns1:CreationDateTime>

      <ns1:LastUpdateDateTime>2018-02-23T07:27:22.361Z</ns1:LastUpdateDateTime>

      </ns1:ItemCategory>

                  </ns0:Value>

      </ns2:result>

      </ns0:findItemResponse>

         </env:Body>

        </env:Envelope>

        • 1. Re: Extract values using sql from xml.
          mNem
          with t (xml) as 
          (
          select 
          '
          <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
                        xmlns:wsa="http://www.w3.org/2005/08/addressing"
                        xmlns:typ="http://xmlns.oracle.com/apps/scm/productModel/items/itemServiceV2/types/">
            <env:Header>
                <wsa:Action>http://xmlns.oracle.com/apps/scm/productModel/items/itemServiceV2//ItemService/findItemResponse
                </wsa:Action>
                <wsa:MessageID>urn:uuid:e2157f90-3928-4f9c-b104-57e0f7d39270</wsa:MessageID>
            </env:Header>
            <env:Body>
                <ns0:findItemResponse xmlns:ns0="http://xmlns.oracle.com/apps/scm/productModel/items/itemServiceV2/types/">
                  <ns2:result xsi:type="ns0:DataObjectResult"
                              xmlns:ns2="http://xmlns.oracle.com/apps/scm/productModel/items/itemServiceV2/types/"
                              xmlns:ns1="http://xmlns.oracle.com/apps/scm/productModel/items/itemServiceV2/"
                              xmlns:tns="http://xmlns.oracle.com/adf/svc/errors/"
                              xmlns:ns0="http://xmlns.oracle.com/adf/svc/types/"
                              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                      <ns0:Value xsi:type="ns1:Item"
                                xmlns:ns6="http://xmlns.oracle.com/apps/scm/productCatalogManagement/advancedItems/flex/egoItemEff/item/categories/"
                                xmlns:ns7="http://xmlns.oracle.com/apps/scm/productModel/items/flex/item/"
                                xmlns:ns8="http://xmlns.oracle.com/apps/scm/productModel/items/flex/itemGdf/">
                        <ns1:ItemId>300000007851975</ns1:ItemId>
                        <ns1:OrganizationId>300000001092261</ns1:OrganizationId>
                        <ns1:OrganizationCode>RIO</ns1:OrganizationCode>
                        <ns1:ItemNumber>00KXADW212</ns1:ItemNumber>
                        <ns1:ItemDescription>Doe Teal (Unstitched) by KX3</ns1:ItemDescription>
                        <ns1:ItemCategory>
                            <ns1:ItemId>300000007851975</ns1:ItemId>
                            <ns1:OrganizationId>300000001092261</ns1:OrganizationId>
                            <ns1:ItemCatalog>RADIUS_CATALOG</ns1:ItemCatalog>
                            <ns1:CategoryName>COM.L2.L3.L4.L5</ns1:CategoryName>
                            <ns1:SequenceNumber xsi:nil="true"/>
                            <ns1:ItemCategoryAssignmentId>300000007851976</ns1:ItemCategoryAssignmentId>
                            <ns1:CreationDateTime>2018-02-23T06:12:50.023Z</ns1:CreationDateTime>
                            <ns1:LastUpdateDateTime>2018-02-23T07:27:22.361Z</ns1:LastUpdateDateTime>
                        </ns1:ItemCategory>
                      </ns0:Value>
                  </ns2:result>
                </ns0:findItemResponse>
            </env:Body>
          </env:Envelope>
          ' from dual
          )
          select x.* 
          from t, xmltable
          (
          xmlnamespaces (
            'http://schemas.xmlsoap.org/soap/envelope/' as "env"
          , 'http://xmlns.oracle.com/apps/scm/productModel/items/itemServiceV2/types/' as "ns0" -- first ns0 
          , 'http://xmlns.oracle.com/adf/svc/types/' as "ns0-000" -- second ns0 is labelled uniquely so we can refer to them in the xpath
          , 'http://xmlns.oracle.com/apps/scm/productModel/items/itemServiceV2/' as "ns1"
          , 'http://xmlns.oracle.com/apps/scm/productModel/items/itemServiceV2/types/' as "ns2"
          )
          , 
          '/env:Envelope/env:Body/ns0:findItemResponse/ns2:result/ns0-000:Value'
          passing xmltype(t.xml)
          columns
              organizationId          varchar2(20) path 'ns1:OrganizationId'
            , assignmentId            varchar2(20) path 'ns1:ItemCategory/ns1:ItemCategoryAssignmentId'
            , ItemId                  varchar2(20) path 'ns1:ItemId'
            , OrganizationCode        varchar2(20) path 'ns1:OrganizationCode'
            , ItemNumber              varchar2(20) path 'ns1:ItemNumber'
            , CategoryName            varchar2(20) path 'ns1:ItemCategory/ns1:CategoryName'
          ) x
          ;
          
          ORGANIZATIONID      ASSIGNMENTID        ITEMID              ORGANIZATIONCODE    ITEMNUMBER          CATEGORYNAME        
          -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
          300000001092261      300000007851976      300000007851975      RIO                  00KXADW212          COM.L2.L3.L4.L5   
          
          1 person found this helpful
          • 2. Re: Extract values using sql from xml.
            mNem

            ... or you could declare ns1 as the default namespace and omit the references to it like below.

             

            select x.*
            from t, xmltable
            (
            xmlnamespaces (
              'http://schemas.xmlsoap.org/soap/envelope/' as "env"
            , 'http://xmlns.oracle.com/apps/scm/productModel/items/itemServiceV2/types/' as "ns0" -- first ns0
            , 'http://xmlns.oracle.com/adf/svc/types/' as "ns0-000" -- second ns0 is labelled uniquely so we can refer to them in the xpath
            default 'http://xmlns.oracle.com/apps/scm/productModel/items/itemServiceV2/'
            , 'http://xmlns.oracle.com/apps/scm/productModel/items/itemServiceV2/types/' as "ns2"
            )
            ,
            '/env:Envelope/env:Body/ns0:findItemResponse/ns2:result/ns0-000:Value'
            passing xmltype(t.xml)
            columns
                 organizationId          varchar2(20) path 'OrganizationId'
               , assignmentId            varchar2(20) path 'ItemCategory/ItemCategoryAssignmentId'
               , ItemId                  varchar2(20) path 'ItemId'
               , OrganizationCode        varchar2(20) path 'OrganizationCode'
               , ItemNumber              varchar2(20) path 'ItemNumber'
               , CategoryName            varchar2(20) path 'ItemCategory/CategoryName'

            ) x
            ;

            • 3. Re: Extract values using sql from xml.
              Khawerr

              Thanks a lot. (y)