SQL Query to iterate within XML Nodes to check for a value and return the record

Question

One of my database table has multiple records. In each record there is a column which stores the XML of following type . I need to write a query which should fetch the records wherein inside the parent node ,if any of the sub node contains only the value 0 entirely. In this example, the fourth node contains all 0. This satisfies the condition and the record must be fetched. May I know how to write this condition in SQL to fetch like this?

Note : The parent node may contain multiple sub nodes.

XML:

        <videoDetails>
            <video>
                <V1>
                    <display>0</display>
                </V1>
                <V2>
                    <display>0</display>
                </V2>
                <V3>
                    <display>0</display>
                </V3>
                <V4>
                    <display>0</display>
                </V4>
                <V5>
                    <display>1</display>
                </V5>
            </video>
            <video>                 
                <V1>
                    <display>0</display>
                </V1>
                <V2>
                    <display>0</display>
                </V2>
                <V3>
                    <display>1</display>
                </V3>
                <V4>
                    <display>0</display>
                </V4>
                <V5>
                    <display>0</display>
                </V5>
            </video>
            <video>                 
                <V1>
                    <display>0</display>
                </V1>
                <V2>
                    <display>1</display>
                </V2>
                <V3>
                    <display>0</display>
                </V3>
                <V4>
                    <display>0</display>
                </V4>
                <V5>
                    <display>1</display>
                </V5>
            </video>
            <video>                 
                <V1>
                    <display>0</display>
                </V1>
                <V2>
                    <display>0</display>
                </V2>
                <V3>
                    <display>0</display>
                </V3>
                <V4>
                    <display>0</display>
                </V4>
                <V5>
                    <display>0</display>
                </V5>
            </video>
        </videoDetails>

I tried to use something like NOT xmlexists('$N/../../../videoDetails/video[1]/*[display=1]' passing USER_VIDEO_XML as "N") . This works only when the XML contains one node which has all 0 and doesn't work with multiple nodes.

Request your valuable inputs.

XML support is **highly vendor-specific** - so please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Dec 01 '20 at 10:22

score 0 · Answer 1 · answered Dec 06 '20 at 12:34

Use the following query:

SELECT 1
FROM MY_TAB T
WHERE XMLCAST(XMLQUERY
(
'
let $d :=
for $v in $doc/videoDetails/video
  return fn:exists($v/*[display != 0])
return fn:exists($d[. = fn:false()])
' PASSING T.DOC AS "doc" 
) AS INT) = 1

The for loop iterates all video sub-nodes, and returns the following sequence in $d for your given example:
(true, true, true, false)
4 video sub-nodes. The first 3 of them contain display !=0, but the 4-th doesn't. The outer return clause returns true, if the sequence above contains the false value. You may play with sample data checking the result at the following db<>fiddle link.

SQL Query to iterate within XML Nodes to check for a value and return the record

1 Answers1