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 Sep 27, 2018 8:45 AM

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

<param>

<value>

<name>access</name>

<value>

<array>

<data>

<value>

</value>

<value>

</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 Sep 27, 2018 9:04 AM (in response to M Prasad-Oracle)

> 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 Sep 27, 2018 9:07 AM (in response to M Prasad-Oracle)

> 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 Sep 27, 2018 10:09 AM (in response to mNem)

It worked perfectly . Can we do it for multiple columns using same logic? Are there any issue?
Like Show 0 Likes(0)

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

mNem Sep 27, 2018 10:11 AM (in response to M Prasad-Oracle)

it would be better if you post the xml and the desired output as you did here.
Like Show 0 Likes(0)

Actions

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

cormaco Sep 27, 2018 3:43 PM (in response to mNem)

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 Sep 27, 2018 3:56 PM (in response to cormaco)

Hi cormaco,

That's much neater. Thanks.
Like Show 0 Likes(0)

Actions
7. Re: Ora-19279 when extracting data from xml

M Prasad-Oracle Sep 28, 2018 5:39 AM (in response to cormaco)

great!
Like Show 0 Likes(0)

Actions

Go to original post