Tag: Xml

Storing and retrieving strings in the SQL Server session context

Posted onAuthorEmil1 Comment

Sometimes it can be useful to store temporary data (e.g. a string with the current username for auditing or similar) in the context of the current Sql Server session and it’s actually rather easy to do. All that’s needed is to convert the data to a varbinary of max length 128 and then call SET CONTEXT_INFO like this:

-- store data
DECLARE @encodedUsername AS VARBINARY(128)
SET @encodedUsername = convert(varbinary(128),'emila')
SET CONTEXT_INFO @encodedUsername

Unfortunately it’s not quite as easy to retrieve the original string… You can retrieve the varbinary data using CONTEXT_INFO(), but that will only give the varbinary data back:

SELECT CONTEXT_INFO()

0x656D696C610000000000000...

Converting that data back to a varchar seems to work at first but the string contains trailing 0-characters (cf. CHAR(0) ):

-- Convert to a string, looks ok...
SELECT CONVERT(VARCHAR(MAX), CONTEXT_INFO())
emila

-- ... but the length is 128 characters
SELECT LEN(CONVERT(VARCHAR(MAX), CONTEXT_INFO()))
128

Removing those illegal character is surprisingly difficult (e.g. REPLACE() cannot be used) but one possible solution is to do a conversion into an Xml string:

-- convert to xml for 
SELECT CONVERT(VARCHAR(MAX), (SELECT CONVERT(VARCHAR(MAX), CONTEXT_INFO()) AS data FOR XML PATH('')))
<data>emila&#x00;&#x00;&#x00;&#x00;&#x00;&#x00;&#x00;...

Substrings of that Xml string can then be replaced like in any other string and that logic is best put in a user-defined function:

-- Retrieve data from the context and cleanup illegal characters
CREATE FUNCTION GetLoggedInUserName() RETURNS varchar(20)
AS
  BEGIN
    RETURN
      REPLACE(
        REPLACE(
          REPLACE(
            CONVERT(VARCHAR(MAX), (SELECT CONVERT(VARCHAR(MAX), CONTEXT_INFO()) AS data FOR XML PATH('')))
            , '</data><data>', '')
          , '</data>', '')
        , '&#x00;', '')
  END
GO


SELECT dbo.GetLoggedInUserName()
emila

The result is now a string of the correct length and without illegal characters.

This code works as expected, but perhaps there is a more efficient alternative out there? Please leave a comment if you have a suggestion for improvement 🙂

/Emil

Property promotion when debatching messages from the BizTalk WCF SQL adapter

Posted onAuthorEmil3 Comments

Richard Seroter has a great post about how to debatch inbound messages from the WCF SQL adapter in BizTalk 2009/2010: http://seroter.wordpress.com/2010/04/08/debatching-inbound-messages-from-biztalk-wcf-sql-adapter/

The problem is that when polling a database table for new data, we get a single message containing all matching rows. In most cases you probably want to debatch this message into separate message, one for each row, and it’s actually rather easy (see Richard’s post for the details).

There seems to be one problem though, namely that property promotions in the incoming schema (which is created automatically if you’re using typed polling) doesn’t work. One solution for that is to create a separate intermediate schema with the required promotions and transform the incoming messages into that schema using the “inbound maps” setting on the receive port. Note that the map’s source schema should be the top TypedPollingResultSet0 element of the generated schema, not the one below the TypedPolling element, since the transformation is done after debatching takes place.

As an added bonus you don’t mess up your message flows with “TypedPollingResultSet0” message types, that are rather non-descriptive. A message type name of “RaindanceNotification” (or whatever you name the intermediate schema) is much more communicative.

/Emil

How to embed an Xslt-file in an assembly

Posted onAuthorEmilLeave a comment

Problem
How do I embed an Xslt file into an assembly so that I won’t have to deploy the file together with the assembly, set configuration options to refer to the file, etc?

Solution

  1. Create a resource (.resx) file in the project
  2. In the resource designer, click “Add Resource” and choose “Add Existing File…”. Select the Xslt file.
  3. Give the new resource a describing name, such as “FilterContentXslt”. The contents of the Xslt file will be available in a string property with this name in the Resource manager.
  4. Code that performs the transformation: 
    // Parse the content into an XmlDocument
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlValue);

// Retrieve the embedded resource containing the XSLT transform
XmlDocument xsltDoc = new XmlDocument();
xsltDoc.LoadXml(Resources.FilterContentXslt);

XslCompiledTransform trans = new XslCompiledTransform();
trans.Load(xsltDoc);

// Perform the transformation
StringWriter writer = new StringWriter();
trans.Transform(doc, writer);
string newXmlValue = writer.ToString();

Simple, and it works.

/Emil