Help with XQuery formulation - selecting *only* most recent element from multiple table rows
<TransactionArray>
<Transaction>
<ID>1</ID>
<AmountPaid>99.00</AmountPaid>
<Buyer>
<BuyerInfo>
<ShippingAddress>
<Name>Larry</Name>
</ShippingAddress>
</BuyerInfo>
</Buyer>
<Item>
<ItemID>1001</ItemID>
<Title>Item 1</Title>
</Item>
<Status>Sold</Status>
</Transaction>
<Transaction>
<ID>2</ID>
<AmountPaid>99.00</AmountPaid>
<Buyer>
<BuyerInfo>
<ShippingAddress>
<Name>Curly</Name>
</ShippingAddress>
</BuyerInfo>
</Buyer>
<Item>
<ItemID>1002</ItemID>
<Title>Item 2</Title>
</Item>
<Status>Shipped</Status>
<PaidTime>2005-11-04</PaidTime>
<ShippedTime>2005-11-08</ShippedTime>
</Transaction>
</TransactionArray>
And the next day it might be:
<TransactionArray>
<Transaction>
<ID>1</ID>
<AmountPaid>99.00</AmountPaid>
<Buyer>
<BuyerInfo>
<ShippingAddress>
<Name>Larry</Name>
</ShippingAddress>
</BuyerInfo>
</Buyer>
<Item>
<ItemID>1001</ItemID>
<Title>Item 1</Title>
</Item>
<Status>Paid</Status>
<PaidTime>2005-11-30</PaidTime>
</Transaction>
</TransactionArray>
Note that the transaction with ID 1 went from Sold to Paid, and a PaidTime element was added.
What I want to do is an xquery across all the rows in the table but include only the most recent occurrence of a transaction within the entire table. I've got the CROSS APPLY .nodes() working so that each transaction becomes its own row in the result set. What I can't quite figure out is to only include the most recent occurence of, in my example, Transaction ID 1.
On a related note, I need the values of multiple elements within each transaction element returned, each as different columns in the result set. I know that I can brute-force it, e.g. :
select
txn.value('(Item/ItemID)[1]', 'nvarchar(max)')) as 'ItemID',
txn.value('(ID)[1]', 'nvarchar(max)') as 'TxnID',
txn.value('(AmountPaid)[1]', 'nvarchar(max)') as 'Sale Price'
from SoapResponses rslts
CROSS APPLY SoapResponseXML.nodes('//TransactionArray/Transaction') AS R(txn)
I'm wondering if there's a better way to get each of those values out of the transaction node, with the requirement that each show up as a different column.
Thanks for bearing with this XQuery newbie. ; )
