postgresql xpath explode one row into N

Question

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%20Media/</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!

Jim Jones · Accepted Answer · 2022-08-29 11:10:40Z

1

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;

answered Aug 29, 2022 at 11:10

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql xpath explode one row into N

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related