7 Replies Latest reply on Sep 28, 2018 5:39 AM by M Prasad-Oracle

    Ora-19279 when extracting data from xml

    M Prasad-Oracle

      I wanted to retrieve data with space or comma seperated from xml with same tag value e.g. Name = 'access'.Please let me know if there is any function to handle it.

      I am getting below error when I try to retrieve data from xml.

       

       

      SQL:

      SELECT  case ip_addresses  when  'all' then 'all'

      when 'disabled' then 'disabled'

      Else

      'IP'

      END "ssh"

      FROM (

                  SELECT x.* FROM  ip_Details  t,

        XMLTABLE ( '/methodResponse/params/param/value'   

                            PASSING a   

            COLUMNS

            ip_addresses          VARCHAR2(40) PATH 'struct/member[name="access"]/value/array/data/value/string'

            ) x

            )

          ;

       

      Output:

      IP Address

      ----------------------------------

      10.244.202.22 10.244.202.23

       

      XML:

      <?xml version='1.0' encoding='UTF-8' standalone='yes'?>

      <methodResponse>

          <params>

              <param>

                  <value>

                      <struct>

                          <member>

                              <name>access</name>

                              <value>

                                  <array>

                                      <data>

                                          <value>

                                              <string>10.244.202.22</string>

                                          </value>

                                          <value>

                                              <string>10.244.202.23</string>

                                          </value>

                                      </data>

                                  </array>

                              </value>

                          </member>

                      </struct>

                  </value>

              </param>

          </params>

      </methodResponse>

       

       

      Error:

      ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

      19279. 00000 -  "XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence"

      *Cause:    The XQuery sequence passed in had more than one item.

      *Action:   Correct the XQuery expression to return a single item sequence.

        • 1. Re: Ora-19279 when extracting data from xml
          mNem
          > with ip_details (a) as 
          (
          select xmltype(
          '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
          <methodResponse>
              <params>
                  <param>
                      <value>
                          <struct>
                              <member>
                                  <name>access</name>
                                  <value>
                                      <array>
                                          <data>
                                              <value>
                                                  <string>10.244.202.22</string>
                                              </value>
                                              <value>
                                                  <string>10.244.202.23</string>
                                              </value>
                                          </data>
                                      </array>
                                  </value>
                              </member>
                          </struct>
                          <struct>
                              <member>
                                  <name>access</name>
                                  <value>
                                      <array>
                                          <data>
                                              <value>
                                                  <string>100.244.202.33</string>
                                              </value>
                                              <value>
                                                  <string>100.244.202.34</string>
                                              </value>
                                          </data>
                                      </array>
                                  </value>
                              </member>
                              <member>
                                  <name>access-dummy</name>
                                  <value>
                                      <array>
                                          <data>
                                              <value>
                                                  <string>200.244.202.33</string>
                                              </value>
                                              <value>
                                                  <string>200.244.202.34</string>
                                              </value>
                                          </data>
                                      </array>
                                  </value>
                              </member>
                          </struct>
                      </value>
                  </param>
              </params>
          </methodResponse>
          '
          ) from dual
          )
          SELECT x.* FROM  ip_Details  t,
          XMLTABLE ( 
          '
          for $x in /methodResponse/params/param/value/struct
            for $y in $x/member[name="access"]
              for $z in $y/value/array/data/value
              return $z  
          '    
          PASSING a    
          COLUMNS
          ip_addresses          VARCHAR2(40) PATH '.'
          ) x
          IP_ADDRESSES                           
          ----------------------------------------
          10.244.202.22                            
          10.244.202.23                            
          100.244.202.33                           
          100.244.202.34            
          

           

           

          NOTE: Added additional test element of <struct>

          • 2. Re: Ora-19279 when extracting data from xml
            mNem
            > with ip_details (a) as 
            (
            select xmltype(
            '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
            <methodResponse>
                <params>
                    <param>
                        <value>
                            <struct>
                                <member>
                                    <name>access</name>
                                    <value>
                                        <array>
                                            <data>
                                                <value>
                                                    <string>10.244.202.22</string>
                                                </value>
                                                <value>
                                                    <string>10.244.202.23</string>
                                                </value>
                                            </data>
                                        </array>
                                    </value>
                                </member>
                            </struct>
                            <struct>
                                <member>
                                    <name>access</name>
                                    <value>
                                        <array>
                                            <data>
                                                <value>
                                                    <string>100.244.202.33</string>
                                                </value>
                                                <value>
                                                    <string>100.244.202.34</string>
                                                </value>
                                            </data>
                                        </array>
                                    </value>
                                </member>
                                <member>
                                    <name>access-dummy</name>
                                    <value>
                                        <array>
                                            <data>
                                                <value>
                                                    <string>200.244.202.33</string>
                                                </value>
                                                <value>
                                                    <string>200.244.202.34</string>
                                                </value>
                                            </data>
                                        </array>
                                    </value>
                                </member>
                            </struct>
                        </value>
                    </param>
                </params>
            </methodResponse>
            '
            ) from dual
            )
            SELECT x.* FROM  ip_Details  t,
            XMLTABLE ( 
            '
            for $x in /methodResponse/params/param/value/struct
              for $y in $x/member[name="access"]
              let $z := string-join(
                for $zz in $y/value/array/data/value
                return $zz  
                , " ")
              return $z
            '    
            PASSING a    
            COLUMNS
            ip_addresses          VARCHAR2(40) PATH '.'
            ) x
            IP_ADDRESSES                           
            ----------------------------------------
            10.244.202.22 10.244.202.23              
            100.244.202.33 100.244.202.34            
            
            
            • 3. Re: Ora-19279 when extracting data from xml
              M Prasad-Oracle

              It worked perfectly . Can we do it for multiple columns using same logic? Are there any issue?

              • 4. Re: Ora-19279 when extracting data from xml
                mNem

                it would be better if you post the xml and the desired output as you did here.

                • 5. Re: Ora-19279 when extracting data from xml
                  cormaco

                  No need for nested loops:

                  SELECT x.* FROM  ip_Details  t,  
                  XMLTABLE (   
                  '/methodResponse/params/param/value/struct/member[name="access"]'  
                  PASSING a      
                  COLUMNS  
                      ip_addresses   VARCHAR2(40) PATH 'string-join(value/array/data/value/string," ")'  
                  ) x  
                  

                   

                  Using your example xml:

                  
                  IP_ADDRESSES                            
                  ----------------------------------------
                  10.244.202.22 10.244.202.23
                  100.244.202.33 100.244.202.34
                  
                  
                  1 person found this helpful
                  • 6. Re: Ora-19279 when extracting data from xml
                    mNem

                    Hi cormaco,

                     

                    That's much neater. Thanks.