Serializing table data for SOAP posts in T-SQL

In an integration setup where a SQL Server trigger uses a CLR method with a generic WS client to publish changes to table data, you need to transform the 'inserted' table data to a serialized XML. In this example the CLR method simply wraps the xml content in the common SOAP envelope, and uses HttpWebRequest to post it to the specified  SOAP action and endpoint URL.

CREATE TRIGGER oncustomermodified  
    ON dbo.[CRONUS Danmark A_S$Customer]
    DECLARE @url NVARCHAR(255) = N'http://testsrv/publish.svc';
    DECLARE @soapAction NVARCHAR(255);
    DECLARE @data XML;
    SET @soapAction = N'urn:test:ws/ITestEndPoint/OnCustomerUpdated';
    WITH xmlnamespaces (
        DEFAULT 'urn:test:ws', 
        '' as dc)
    SELECT @data = (
        SELECT TOP 1 
            '' as 'dc:__identity', 
            [No_] AS 'ContactId', 
            [Name] AS 'FirstName' 
            FROM inserted 
            FOR xml path('entity'), root('OnCustomerUpdated') 
    EXEC dbo.PublishToWS @url, @soapAction, @data;

Note the __identity element is required for the WS endpoint to deserialize the record.

Google @klinkby RSS feed  GitHub