postgresql-xpath将一行分解为N

Question

注意事项：我对Postresql中的xml数据类型和方法完全陌生。

我有一张表，一行一列，内容如下：

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

很抱歉延期，但为了完整起见。

我想在SELECT中，从包含xml数据类型中所有xml的单行中提取以下内容：

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

你知道我该怎么做到吗？

非常感谢！

Answer 1

解析XML相当棘手。假设密钥Name、Artist和Album的顺序从未更改，则以下查询可能会对您有所帮助：

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;