postgresql xpath explode one row into N-CodePudding

Word of notice: I'm completely new to xml data type and methods in Postresql.

I have a table, one single row, one single column, with this content:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>Major Version</key><integer>1</integer>
    <key>Minor Version</key><integer>1</integer>
    <key>Date</key><date>2022-07-20T15:03:30Z</date>
    <key>Application Version</key><string>11.1.5</string>
    <key>Features</key><integer>5</integer>
    <key>Show Content Ratings</key><true/>
    <key>Music Folder</key><string>file://localhost/C:/Users/user/Music/iTunes/iTunes Media/</string>
    <key>Library Persistent ID</key><string>23CE8FD285020EA7</string>
    <key>Tracks</key>
    <dict>
        <key>2517</key>
        <dict>
            <key>Track ID</key><integer>2517</integer>
            <key>Name</key><string>Your Sweet Six Six Six</string>
            <key>Artist</key><string>HIM</string>
            <key>Album</key><string>Greatest Lovesongs Vol. 666 </string>
        </dict>
        <key>2519</key>
        <dict>
            <key>Track ID</key><integer>2519</integer>
            <key>Name</key><string>Wicked Game</string>
            <key>Artist</key><string>HIM</string>
            <key>Album</key><string>Greatest Lovesongs Vol. 666 </string>
        </dict>
    </dict>
    <key>Playlists</key>
    <array>
        <dict>
            <key>Name</key><string>Library</string>
            <key>Master</key><true/>
            <key>Playlist ID</key><integer>52895</integer>
            <key>Playlist Persistent ID</key><string>37B8BC84B0503164</string>
            <key>Visible</key><false/>
            <key>All Items</key><true/>
            <key>Playlist Items</key>
            <array>
                <dict>
                    <key>Track ID</key><integer>2517</integer>
                    <key>Track ID</key><integer>2519</integer>
                </dict>
            </array>
        </dict>
    </array>
</dict>
</plist>

Sorry for the extension, but for the sake of completeness.

I'd like to extract, in a SELECT, from that single row containing all that xml in a xml data type, the following content:

 ------------------------ -------- ----------------------------- 
|          Name          | Artist |            Album            |
 ------------------------ -------- ----------------------------- 
| Your Sweet Six Six Six | HIM    | Greatest Lovesongs Vol. 666 |
| Wicked Game            | HIM    | Greatest Lovesongs Vol. 666 |
 ------------------------ -------- -----------------------------

Any idea how can I achieve it?

Thanks so much!

CodePudding user response：

Quite tricky XML to parse. Assuming that the order of the keys Name, Artist and Album never change, the following query might help you:

SELECT
 (xpath('/dict[key/text()="Name"]/string/text()',j))[1],
 (xpath('/dict[key/text()="Name"]/string/text()',j))[2],
 (xpath('/dict[key/text()="Name"]/string/text()',j))[3]
FROM mytable
CROSS JOIN LATERAL unnest((xpath('//dict/dict/dict',xmlcol))) j;