MSSQL XML max function returns null

Question

In my transaction table, I have an XML column which contains some additional information about the transaction, e.g. how some prices in the transaction were calculated (for the publisher side and the seller side). Since the calculation algorithm may change, a transaction can be calculated multiple times. The complete calculation history is saved in the XML.

Now I want to have the revision number of the algorithm of the most recent calculation. The XML structure looks like this:

<DataItems>
    <!-- ... more data here ... -->
    <CalculationHistory>
        <Seller Revision="3" Time="2013-10-22T10:00:18.6294944Z" Result="20">
            <Step Index="0" NodeId="c3e33dd5-25e0-4272-8b6f-f65fb2f0659b">bla</Step>
            <Step Index="1" NodeId="b76b0915-7527-4df4-8185-5a425e723491">bla</Step>
            <Step Index="2" NodeId="7e51947e-85ef-431f-8a21-9f4e30136805">bla</Step>
            <Step Index="3" NodeId="10212fe9-8378-463d-b35b-27225b3cf2a1">bla</Step>
        </Seller>
        <Seller Revision="4" Time="2013-10-22T14:54:53.974548Z" Result="20">
            <Step Index="0" NodeId="c3e33dd5-25e0-4272-8b6f-f65fb2f0659b">bla</Step>
            <Step Index="1" NodeId="b76b0915-7527-4df4-8185-5a425e723491">bla</Step>
            <Step Index="2" NodeId="7e51947e-85ef-431f-8a21-9f4e30136805">bla</Step>
            <Step Index="3" NodeId="10212fe9-8378-463d-b35b-27225b3cf2a1">bla</Step>
        </Seller>
        <Publisher Revision="7" Time="2013-10-22T14:54:53.9794031Z" Result="40">
            <Step Index="0" NodeId="2ce85808-903f-40b0-b8a9-c72e02d36d71">bla</Step>
            <Step Index="1" NodeId="50ca67cc-5df8-450d-06d9-72c224027406">bla</Step>
            <Step Index="2" NodeId="b2122e9f-c15b-4fa8-ce22-3dc73933d3d3">bla</Step>
        </Publisher>
    </CalculationHistory>
</DataItems>

So what I tried is to select the most recent revision number as a column like this:

SELECT XmlColumn.value('(/DataItems/CalculationHistory/Seller[@Time = max(/DataItems/CalculationHistory/Seller/@Time)]/@Revision)[1]', 'int') AS SellerRevision FROM Transactions

But this returns null every time. I found out that the problem is the max() function returning null even in this case:

SELECT XmlColumn.query('max(/DataItems/CalculationHistory/Seller/@Time)') FROM Transactions

...although replacing max with data returns the correct timestamp(s).

So I guess I'm not using the max function correctly, but what am I doing wrong?

score 1 · Accepted Answer · answered Oct 25 '13 at 14:45

It's strange, max doesn't work on string attributes/elements:

select (select cast('<A D="1"/>' as xml)).query('max(/A/@D)')
-- returns 1

select (select cast('<A D="a"/>' as xml)).query('max(/A/@D)')
-- returns nothing

some workaround could be using variable:

declare @Time nvarchar(max)

select @Time = max(T.C.value('@Time', 'nvarchar(max)'))
from @Data.nodes('DataItems/CalculationHistory/Seller') as T(C)

select @Data.value('(/DataItems/CalculationHistory/Seller[@Time=sql:variable("@Time")]/@Revision)[1]', 'int')

sql fiddle demo

I'll try to check later if it's possible to use max on strings in SQL Server xpath

Your workaround works indeed, but it's (unavoidably) complicated especially because I have to use two of those queries in one statement in Entity Framework for .NET. Of course, your answer solves the problem - although I used another workaround where I restructured my C# code to allow a simpler query. (Now I check for existence of an XML node with a specific `Revision` attribute value.) — fero, Oct 28 '13 at 08:23

MSSQL XML max function returns null

1 Answers1