Sniffing xml messages stored in SQL Server with xPath.

If you have a database table with actual xml messages stored as a column value on a record (an xml payload) you can use the “.nodes” method to inspect the xml in each record using xpath queries.

Lets say you have a table of records that store details on interest rate swap trade….

The FpML schema for a IR Swap is here:

CROSS Apply the results of your xPath queries to SQL placeholder aliases such then can then be used as regular SQL data column values in the rest of the query:

-- IRD XCCY Swap, find me a fixed fixed IRD Swap 
WITH xmlnamespaces( '' AS fpml, 
'' AS xsi, 
'' AS kc )
SELECT trade_id, update_time, trade_event
,x.col.value('.', 'VARCHAR(100)') [productType]
,y.col.value('.', 'VARCHAR(100)') [fixRate1]
,z.col.value('.', 'VARCHAR(100)') [fixRate2]
FROM [dbo].[tradeMessages] 
CROSS apply xml_trade_payload.nodes('//fpml:swap/fpml:productType') x(col) 
CROSS apply xml_trade_payload.nodes('//fpml:swapStream[1]/fpml:calculationPeriodAmount/fpml:calculation/fpml:fixedRateSchedule/fpml:initialValue') y(col) 
CROSS apply xml_trade_payload.nodes('//fpml:swapStream[2]/fpml:calculationPeriodAmount/fpml:calculation/fpml:fixedRateSchedule/fpml:initialValue') z(col) 
where asset_class = 'IRD' 
	and product_type = 'Interest Rate Swap' 
	and update_time >= CONVERT(datetime, '2018-01-01')
	and trade_event = 'ADD'
(x.col.value('.', 'VARCHAR(100)') != y.col.value('.', 'VARCHAR(100)')
or y.col.value('.', 'VARCHAR(100)') != z.col.value('.', 'VARCHAR(100)')
or x.col.value('.', 'VARCHAR(100)') != z.col.value('.', 'VARCHAR(100)'))
order by unique_trade_id, seq_id desc

This will find all trades added to a TradeMessages table that have 2 fixed rate schedules (a fix/fix swap), that were traded in 2018.

Nice link here to CROSS Apply:

Leave a Reply