'Spark SQL (Databricks) function xpath ignores empty tags in XML

Spark SQL (Databricks) function xpath ignores empty tags in XML. For example for below XML XPATH returns array ["ABC"]. We need it as ["ABC", NULL or empty string] because when we use this along with posexplode_outer, omitting null in XPATH will cause incorrect association. Is there any option in XPATH to retain nulls? I did not find any detailed documentation for this.


SELECT xpath("<PARTY>
                <PARTY_EVENT>
                    <EVENTTYPE>VISITED</EVENTTYPE>
                    <LOCATION>ABC</LOCATION>
                </PARTY_EVENT>
                <PARTY_EVENT>
                    <EVENTTYPE>VISITED</EVENTTYPE>
                    <LOCATION />
                </PARTY_EVENT>
                </PARTY>"
        , '/PARTY/PARTY_EVENT/LOCATION/text()')

enter image description here



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source