I dagens bloggpost tänkte jag skriva lite om XQuery och hur man kan använda det för att lösa att knivigt problem. Jag gjorde en proof-of-concept häromveckan där det krävdes att det gjordes ett hash-värde på en XML-kolumn i databasen för att upptäcka förändringar och dessutom använda den som en unik nyckel. Jag skriver unik nyckel eftersom den inte skulle användas om primärnyckel.
Bakgrunden är ett användargränssnitt där användarna fritt kan kombinera olika urval. Dessa användarväl modelleras i ett XML-dokument och sparas i databasen. Så här långt är det inga konstigheter, förutom att användargränssnittet inte hade ett deterministiskt sätt att gå tillväga med att spara urvalen. Den sparade helt enkelt ner urvalen i samma ordning som användaren markerade i kryssrutor osv. Det innebär att du får olika hash-värden för samma urval eftersom de i XML-dokumenten hamnar på olika ställen.
I första versionen av PoC gjorde jag urvalet med T-SQL och det blev en ganska klumpig lösning. Sedan inför PoC v2 tittade jag på XQuery och hittade en perfekt kandidat för att lösa kundens problem, nämligen funktionen Index-Of. Tyvärr visade det sig ganska snabbt att Microsoft inte har implementerat denna standardfunktion, så jag fick göra det på annat sätt.
Titta på det här XML-dokumentet
<filter>
<parameter name=“def”>
<value>200</value>
<value>100</value>
</parameter>
<parameter name=“abc”>
<value>12</value>
<value>23</value>
<value>9</value>
</parameter>
<parameter name=“abc”>
<value>23</value>
<value>45</value>
</parameter>
<parameter name=“xyz”>
<value>200</value>
<value>40</value>
</parameter>
<parameter name=“abc”>
<value>56</value>
</parameter>
</filter>
Vad jag vill ha i databasen är en deterministiskt XML-dokument som ser ut så här
<filter>
<parameter name=“abc”>
<value>9</value>
<value>12</value>
<value>23</value>
<value>45</value>
<value>56</value>
</parameter>
<parameter name=“def”>
<value>100</value>
<value>200</value>
</parameter>
<parameter name=“xyz”>
<value>40</value>
<value>200</value>
</parameter>
</filter>
Det som kommer att hjälpa oss här mest är en funktion DISTINCT-VALUES som Microsoft har implementerat. Jag startar med vanlig FLWOR-semantik och skriver
<filter>
{
for $c in distinct-values((/filter/parameter/@name))
order by string(lower-case($c))
return
<parameter name=”{lower-case($c)}” />
}
</filter>
Vad denna kod gör är att ta fram alla unika namn-attribut och returnera dem i en sorterad ordning.
<filter>
<parameter name=“abc” />
<parameter name=“def” />
<parameter name=“xyz” />
</filter>
Inga konstigheter så här långt. Nästa steg blir att Hämta fram alla värden som är kopplade till just sin parameter. Vi vill t.ex inte ha värdet 40 kopplat till parametern abc. Tänker man sig inte för här är det lätt att få till en kartesisk produkt där du fram fram en unik lista på alla värden och jämför dem med alla parametrar. Det behövs inte!
I XQuery kan vi filtrerar elementen ganska lätt och få fram enbart de värde som är relevanta genom att filtrera den innersta loopen med de attributvärden som den yttre loopen genererat!
Vad vi behöver göra är att skriva en loop i loopen, så här.
<filter>
{
for $c in distinct-values((/filter/parameter/@name))
order by string(lower-case($c))
return
<parameter name=”{lower-case($c)}”>
{
for $v in distinct-values((/filter/parameter[@name = $c]/value))
order by floor($v)
return
<value>{$v}
</value>
}
</parameter>
}
</filter>
Och där har vi lösningen!
<filter>
<parameter name=“abc”>
<value>9</value>
<value>12</value>
<value>23</value>
<value>45</value>
<value>56</value>
</parameter>
<parameter name=“def”>
<value>100</value>
<value>200</value>
</parameter>
<parameter name=“xyz”>
<value>40</value>
<value>200</value>
</parameter>
</filter>
Alla element är sorterade enligt attributet namn och sedan är alla värden sorterade numeriskt. Hur får vi då till detta som en funktion som vi kan använda oss av i T-SQL?
CREATE FUNCTION dbo.fnMakeSelectionDeterministic
(
@Content XML
)
RETURNS TABLE
RETURN (
SELECT @Content.query(‘
<filter>
{
for $c in distinct-values((/filter/parameter/@name))
order by string(lower-case($c))
return
<parameter name=”{lower-case($c)}”>
{
for $v in distinct-values((/filter/parameter[@name = $c]/value))
order by floor($v)
return
<value>{$v}
</value>
}
</parameter>
}
</filter>
‘) AS Content
)
