OPENXML and SQL

Recently I needed to implement a feature that allows a user to specify a “Rate Reset” on a BondID/Counterparty pair.

The new Rate needed to be applied to all “open” trades that were on the BONDID and with the particular Counterparty.

I wanted to avoid making multiple calls to the database for each trade update and I also wanted to avoid writing SQL code that would loop through a delimited parameter list.

One solution was to make use of the OPENXML syntax that has been built into SQLServer since SQLServer 2000.

In a nutshell OPENXML allows you to treat an XML string as a SQL table with control over the typed field datatypes.

Here are the basic steps:

1. Create a well formed XML string with rows as Elements and fields as Attributes.

What’s great is that C# lets you Serialize an IList<T> to XML very easily for this purpose. But . . . thats for another posting :)

<ArrayOfRepoRate>
<RepoRate BONDID="123456"
          CounterpartyID="1"
          ValuationDate="08/31/2010"
          RateRepo="21.2" IsFixed="1"
/>
<RepoRate BONDID="AAAA"
          CounterpartyID="0"
          ValuationDate="08/31/2010"
          RateRepo="2.4"
          IsFixed="0"
/>
</ArrayOfRepoRate>
2. Within the stored procedure set the data type of you XML to nvarchar(max) and also declare an int place holder variable for use with the system proc 'sp_xml_preparedocument'
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
3. Use the OPENXML on the @docHandle to use your XML as a table
SELECT * INTO #RepoRates
FROM OPENXML(@docHandle, N'/ArrayOfRepoRate/RepoRate')
  WITH (BONDID varchar(25), CounterPartyID int,
        ValuationDate datetime,
        RateRepo numeric(10,5), IsFixed bit)
4. Make sure you close the dochandle after you are finished using it!
EXEC sp_xml_removedocument @docHandle