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'
and 
(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
G

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

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" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
xmlns:xs="http://www.w3.org/2001/XMLSchema" 
xmlns:fn="http://www.w3.org/2005/xpath-functions"
xmlns:fpml="http://www.fpml.org/2008/FpML-4-5"
xmlns:party="urn:party:data"
xmlns:kc="http://www.caulfieldfamily.co.uk">

<!--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:copy>
      <xsl:apply-templates select="@* | node()"/>
    </xsl:copy>
</xsl:template>

<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>

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

</xsl:stylesheet>

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
C:
CD %USERPROFILE%\XSLT\XML
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
:bad
REM deal with error level
echo A Bad Thing Happened
:end