Tag Archives: XML

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: http://www.fpml.org/spec/fpml-5-3-2-wd-2/html/reporting/schemaDocumentation/schemas/fpml-ird-5-3_xsd/complexTypes/Swap.html

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( 'http://www.fpml.org/2008/FpML-4-5' AS fpml, 
'http://www.w3.org/2001/XMLSchema-instance' AS xsi, 
'http://www.kcbank.com/messagebus/core/FpML-4-5/extension' 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: https://docs.microsoft.com/en-us/sql/t-sql/xml/nodes-method-xml-data-type?view=sql-server-2017

Transform a folder of XML files

If you ever need to bulk transform a directory of xml files, and output each transformed file into a new directory then Saxon has a great command line interface tool that will do just that, in one simple line!

CD C:\Users\caulfiek\Testing java -jar c:\Users\caulfiek\XSLT\saxonHE9-8-0-8j\saxon9he.jar -s:sourceDir\ -xsl:FPML_Masking.xslt -o:targetDir
Here I have  a stylesheet that performs an identity transform on a xml file, but uses xpath to find and mask certain xml node values (like customer name and transaction number).
Download the saxonHE9 cli tool from here:
Create a directory “sourceDir” and “targetDir”
Fill your “sourceDir” with lots of XML files and set up the command as above on your DOS command prompt.