4 Replies Latest reply: Aug 21, 2013 2:09 PM by odie_63 RSS

    Help with existNode function

    47e033bd-d313-47bd-9372-871358ce3c3e

      I am using the existNode function to check whether a specific node exists or has a value for it in the XML.

      So, am using it within an IF condition as below;

       

      IF L_xml.existSNode('/ItemFamily/Style/Item/Attributes/ItemAttributes/ExtRefNo/text()') > 0 THEN

               DBMS_OUTPUT.PUT_LINE('L_xml = Style exists');

      END IF;

       

      Here L_xml is my initial XML message received which is a CLOB converted to an XMLTYPE.

       

      The above works fine.

       

      But after I do an EXTRACT operation on the above XML to get only the portion of it (from a particular start tag to its corresponding end tag), and feed this output for my existNode function as below;

       

          IF L_itm_xml.existSNode('/ItemFamily/Style/Item/Attributes/ItemAttributes/ExtRefNo') > 0 THEN

               DBMS_OUTPUT.PUT_LINE('itm_xml = ');

          END IF;

       

      It does not give me the desired output.

       

      I would like to know, if there is any restriction on the existNode usage.

      Am asking this coz am a bit skeptical on whether this is happening due to XML being stripped off its header.

       

      Could someone please help me find an answer for this.

        • 1. Re: Help with existNode function
          47e033bd-d313-47bd-9372-871358ce3c3e

          Sorry, the second IF condition is incorrectly copied.

          It is as below;

           

               IF L_itm_xml.existSNode('/Item/Attributes/ItemAttributes/ExtRefNo') > 0 THEN
                   DBMS_OUTPUT.PUT_LINE('itm_xml = ');
                END IF;

          • 2. Re: Help with existNode function
            odie_63

            Hi,

             

            Some sample data, and at least the exact code you're using might help identifying the problem.

             

            Thanks.

            • 3. Re: Help with existNode function
              47e033bd-d313-47bd-9372-871358ce3c3e

              The sample data is as below;

              <?xml version="1.0" encoding="utf-8"?>

              <ItemFamily>

                <Style>

                  <Item>

                    <ParentExtRefNo>str1234</ParentExtRefNo>

                    <PackInd>str1234</PackInd>

                    <ItemLevel>str1234</ItemLevel>

                    <TranLevel>str1234</TranLevel>

                    <Diff1>str1234</Diff1>

                    <Diff2>str1234</Diff2>

                    <Diff3>str1234</Diff3>

                    <Diff4>str1234</Diff4>

                    <ItemDesc>str1234</ItemDesc>

                    <ShortDesc>str1234</ShortDesc>

                    <SuggRetail>str1234</SuggRetail>

                    <HandlingTemp>str1234</HandlingTemp>

                    <HandlingSens>str1234</HandlingSens>

                    <SellableInd>str1234</SellableInd>

                    <OrderableInd>str1234</OrderableInd>

                    <ShipAloneInd>str1234</ShipAloneInd>

                    <Dept>str1234</Dept>

                    <Class>str1234</Class>

                    <Subclass>str1234</Subclass>

                    <Comments>str1234</Comments>

                    <GiftWrapInd>str1234</GiftWrapInd>

                    <CheckUDAInd>str1234</CheckUDAInd>

                    <OriginalRetail>str1234</OriginalRetail>

                    <LastUpdateId>str1234</LastUpdateId>

                    <ForecastInd>str1234</ForecastInd>

                    <Suppliers>

                      <ItemSupplier>

                        <Supplier>str1234</Supplier>

                        <PrimarySuppInd>str1234</PrimarySuppInd>

                        <VPN>str1234</VPN>

                        <DirectShipInd>str1234</DirectShipInd>

                        <LastUpdateId>str1234</LastUpdateId>

                        <Countries>

                          <SuppCountry>

                            <SuppCountryType>str1234</SuppCountryType>

                            <OriginCountryId>str1234</OriginCountryId>

                            <PrimarySupplierInd>str1234</PrimarySupplierInd>

                            <PrimaryCountryInd>str1234</PrimaryCountryInd>

                            <SuppPackSize>str1234</SuppPackSize>

                            <InnerPackSize>str1234</InnerPackSize>

                            <UnitCost>str1234</UnitCost>

                            <LastUpdateId>str1234</LastUpdateId>

                            <Dimensions>

                              <SuppCountryDim>

                                <DimType>str1234</DimType>

                                <DimObject>str1234</DimObject>

                                <Height>str1234</Height>

                                <Width>str1234</Width>

                                <Length>str1234</Length>

                                <LwhUOM>str1234</LwhUOM>

                                <Weight>str1234</Weight>

                                <NetWeight>str1234</NetWeight>

                                <WeightUOM>str1234</WeightUOM>

                                <LastUpdateId>str1234</LastUpdateId>

                              </SuppCountryDim>

                            </Dimensions>

                          </SuppCountry>

                        </Countries>

                      </ItemSupplier>

                    </Suppliers>

                  </Item>

                </Style>

              </ItemFamily>

               

              The L_xml will have this as its value.

              The L_itm_xml will have the subset of the XML extracted from it as its value.

              As below;

               

                  <Item>

                    <ParentExtRefNo>str1234</ParentExtRefNo>

                    <PackInd>str1234</PackInd>

                    <ItemLevel>str1234</ItemLevel>

                    <TranLevel>str1234</TranLevel>

                    <Diff1>str1234</Diff1>

                    <Diff2>str1234</Diff2>

                    <Diff3>str1234</Diff3>

                    <Diff4>str1234</Diff4>

                    <ItemDesc>str1234</ItemDesc>

                    <ShortDesc>str1234</ShortDesc>

                    <SuggRetail>str1234</SuggRetail>

                    <HandlingTemp>str1234</HandlingTemp>

                    <HandlingSens>str1234</HandlingSens>

                    <SellableInd>str1234</SellableInd>

                    <OrderableInd>str1234</OrderableInd>

                    <ShipAloneInd>str1234</ShipAloneInd>

                    <Dept>str1234</Dept>

                    <Class>str1234</Class>

                    <Subclass>str1234</Subclass>

                    <Comments>str1234</Comments>

                    <GiftWrapInd>str1234</GiftWrapInd>

                    <CheckUDAInd>str1234</CheckUDAInd>

                    <OriginalRetail>str1234</OriginalRetail>

                    <LastUpdateId>str1234</LastUpdateId>

                    <ForecastInd>str1234</ForecastInd>

                    <Suppliers>

                      <ItemSupplier>

                        <Supplier>str1234</Supplier>

                        <PrimarySuppInd>str1234</PrimarySuppInd>

                        <VPN>str1234</VPN>

                        <DirectShipInd>str1234</DirectShipInd>

                        <LastUpdateId>str1234</LastUpdateId>

                        <Countries>

                          <SuppCountry>

                            <SuppCountryType>str1234</SuppCountryType>

                            <OriginCountryId>str1234</OriginCountryId>

                            <PrimarySupplierInd>str1234</PrimarySupplierInd>

                            <PrimaryCountryInd>str1234</PrimaryCountryInd>

                            <SuppPackSize>str1234</SuppPackSize>

                            <InnerPackSize>str1234</InnerPackSize>

                            <UnitCost>str1234</UnitCost>

                            <LastUpdateId>str1234</LastUpdateId>

                            <Dimensions>

                              <SuppCountryDim>

                                <DimType>str1234</DimType>

                                <DimObject>str1234</DimObject>

                                <Height>str1234</Height>

                                <Width>str1234</Width>

                                <Length>str1234</Length>

                                <LwhUOM>str1234</LwhUOM>

                                <Weight>str1234</Weight>

                                <NetWeight>str1234</NetWeight>

                                <WeightUOM>str1234</WeightUOM>

                                <LastUpdateId>str1234</LastUpdateId>

                              </SuppCountryDim>

                            </Dimensions>

                          </SuppCountry>

                        </Countries>

                      </ItemSupplier>

                    </Suppliers>

                    <UDAs>

                      <ItemUDA>

                        <UdaId>str1234</UdaId>

                        <UdaValue>str1234</UdaValue>

                        <LastUpdateId>str1234</LastUpdateId>

                      </ItemUDA>

                    </UDAs>

                    <Channels>

                      <ItemChannel>

                        <ChannelType>str1234</ChannelType>

                      </ItemChannel>

                    </Channels>

                    <Attributes>

                      <ItemAttributes>

                        <ExtRefNo>str1234</ExtRefNo>

                        <ParentExtRefNo>str1234</ParentExtRefNo>

                        <Collection>str1234</Collection>

                        <PerishableInd>str1234</PerishableInd>

                        <PersAvailInd>str1234</PersAvailInd>

                        <ShortDesc30>str1234</ShortDesc30>

                        <ItemType>str1234</ItemType>

                        <ConveyableType>str1234</ConveyableType>

                        <GiftWrapType>str1234</GiftWrapType>

                        <LastUpdateId>str1234</LastUpdateId>

                      </ItemAttributes>

                    </Attributes>

                    <Ticket>

                      <ItemTicket>

                        <TicketTypeId>str1234</TicketTypeId>

                        <LastUpdateId>str1234</LastUpdateId>

                      </ItemTicket>

                    </Ticket>

                    <Carriers>

                      <CarrierService>

                        <CarrierSvc>str1234</CarrierSvc>

                        <DefCarrierSvcInd>str1234</DefCarrierSvcInd>

                      </CarrierService>

                    </Carriers>

                    <Shipping>

                      <ShipRestrictions>

                        <Carrier>str1234</Carrier>

                        <AddressCode>str1234</AddressCode>

                      </ShipRestrictions>

                    </Shipping>

                    <Pricing>

                      <RetailPrice>

                        <ZoneId>str1234</ZoneId>

                        <UnitRetail>str1234</UnitRetail>

                        <LastUpdateId>str1234</LastUpdateId>

                      </RetailPrice>

                    </Pricing>

                    <PackItems>

                      <PackItem>

                        <CompExtRefNo>str1234</CompExtRefNo>

                        <Qty>str1234</Qty>

                        <LastUpdateId>str1234</LastUpdateId>

                      </PackItem>

                    </PackItems>

                  </Item>

               

              The existNode is functioning with L_xml as the argument but not with L_itm_xml.

               

              Hope you have the clarity you were seeking out for.

              • 4. Re: Help with existNode function
                odie_63
                But after I do an EXTRACT operation on the above XML to get only the portion of it (from a particular start tag to its corresponding end tag), and feed this output for my existNode function as below;

                 

                    IF L_itm_xml.existSNode('/ItemFamily/Style/Item/Attributes/ItemAttributes/ExtRefNo') > 0 THEN

                         DBMS_OUTPUT.PUT_LINE('itm_xml = ');

                    END IF;

                 

                It does not give me the desired output.

                If l_itm_xml variable contains an XML fragment starting at element "Item" then of course the XPath expression '/ItemFamily/Style...' doesn't target anything.

                 

                This should work better :

                  if l_xml.existsNode('/ItemFamily/Style/Item/Attributes/ItemAttributes/ExtRefNo') > 0 then

                    dbms_output.put_line('OK');

                  end if;

                 

                  l_itm_xml := l_xml.extract('/ItemFamily/Style/Item');

                 

                  if l_itm_xml.existsNode('/Item/Attributes/ItemAttributes/ExtRefNo') > 0 then

                    dbms_output.put_line('OK');

                  end if;