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:

Mask FpML with xslt Identity Transform

This script will copy a FpML xml file and match and transform the fpml:tradeid to add the current date to the node value.

<xsl:stylesheet version="2.0" 

<!--K Caulfield, Sep 2018
	Stylesheet to transform FpML XML messages into FpML XML messages with the trade id details prefixed to make then unique for repeated testing -->

	<xsl:output method="xml" encoding="UTF-8" indent="yes"/>
	<xsl:strip-space elements="*"/>

<!-- define the prefix we want to use for setting up a text cycyle -->
<!--<xsl:variable name="trade_prefix" select="'20180925_01_'" />-->
<xsl:variable name="trade_prefix" select="concat(fn:format-date(fn:current-date(),'[Y0001][M01][D01]'),'_','01','_')" />
<!-- Identity Transform -->
<xsl:template match="@* | node()">
      <xsl:apply-templates select="@* | node()"/>

<xsl:template match="//fpml:tradeHeader/fpml:partyTradeIdentifier/fpml:tradeId[../fpml:partyReference/@href='party1']/text()">
	<xsl:call-template name="kc:prefix"><xsl:with-param name="nodeVal" select="." /></xsl:call-template>

<xsl:template name="kc:prefix">
	<xsl:param name="nodeVal" />
		<xsl:value-of select="concat($trade_prefix,$nodeVal)" />


Script to transform with saxon .net

Nice DOS script to transform the most recent xml file in a directory, using net.sf.saxon.Transform

REM Script to call Saxon xslt Parser for most recent file in XML directory and save results to HTML/Output.html
rem get the latest file in the XML directory
for /f "delims=" %%i in ('dir /od /b *.xml') do (
	Set recentXMLFile=%%i
CD ..
Set xmlFile="XML\%recentXMLFile%"
Set xsltFile=GU_UI_NoNULLv2.xslt
Set outFile=HTML\Output.html
java -cp saxonHE9-8-0-8j\saxon9he.jar net.sf.saxon.Transform -t -s:%xmlFile% -xsl:%xsltFile% -o:%outFile% --suppressXsltNamespaceCheck:on
IF ERRORLEVEL 1 goto :bad  
REM now open the html file
start "" %outFile%
goto :end
REM deal with error level
echo A Bad Thing Happened