CODEWITHSUNDEEP
xmlxquery
Sthe Nyandeni
Sthe Nyandeni

Reputation: 13

Using XQuery to select a specific value from a specific (conjoined) tag in an xml database

So I'm learning how to make use of XQuery and XPath in order to query from an XML database, reason being I want to make an application that interacts with my iTunes music library. iTunes stores the library data in an xml file. My goal is to create a list (or dictionary perhaps) of artists and their albums, but I'll work on albums later on. For now I'm just concentrating on getting a (distinct) list of artists.

I am struggling with creating the necessary query that will give me the correct result. Here is a sample of the data:

<key>13785</key>
    <dict>
        <key>Track ID</key><integer>13785</integer>
        <key>Size</key><integer>8921131</integer>
        <key>Total Time</key><integer>248520</integer>
        <key>Disc Number</key><integer>1</integer>
        <key>Disc Count</key><integer>1</integer>
        ...
        <key>Artist</key><string>Green Day</string>
        <key>Album Artist</key><string>Green Day</string>
        <key>Album</key><string>Revolution Radio</string>
        <key>Genre</key><string>Alternative</string>
        <key>Kind</key><string>Apple Music AAC audio file</string>
        <key>Location</key><string>file://localhost/C:/Users/Sthe%20Nyandeni/Music/iTunes/iTunes%20Media/Apple%20Music/Green%20Day/Revolution%20Radio/01%20Somewhere%20Now.m4p</string>
    </dict>

I want to create a query in XQuery that will return the string in the Artist tag.

This is what I have so far:

let $doc := doc('C:/Users/Sthe Nyandeni/Desktop/temp/db.xml') 
for $key in distinct-values($doc/plist/dict/dict/dict)
where $key/key = 'Artist'
return $key/string

What I know is that if this were SQL, the query would be something like:

SELECT DISTINCT Artist FROM [the database/table] WHERE Key = "Artist"

Can someone please help me obtain such a query in XQuery. Thank you in advanced.

Upvotes: 1

Views: 484

Answers (2)

Martin Honnen
Martin Honnen

Reputation: 167506

Use distinct-values(//key[. = 'Artist']/following-sibling::string[1]). Example at https://xqueryfiddle.liberty-development.net/pPgCcot.

If you have XQuery 3 and window clause support you can also use

for tumbling window $pair in //dict/*
start $k when $k instance of element(key) and $k = 'Artist'
end $v when $v instance of element(string)
group by $name := $v
return $name

https://xqueryfiddle.liberty-development.net/pPgCcot/1

Upvotes: 3

Aaron
Aaron

Reputation: 24802

The following XPath (which you can use as-is in XQuery) will work just fine :

//dict/key[text() = 'Artist']/following-sibling::string[1]/text()

It finds the Artist key, selects the following <string> tags, retain only the first one and return its textual content.

You should try to replace the leading // by the specific XPath leading to the <dict> tags for better performances.

Upvotes: 1

Related Questions