如何在SQL Server中创建存储过程生成带安全头的SOAP Web Service XML？

当然可以在SQL Server里生成这个带WS-Security的SOAP XML！我帮你拆解整个实现过程，重点解决你关心的UsernameToken、Nonce和Created字段的生成问题。

1. 核心安全字段生成逻辑 #

先搞定你最关注的几个关键字段：

Nonce生成 #

Nonce是随机的Base64编码字符串，我们可以用SQL Server的CRYPT_GEN_RANDOM生成16字节随机数，再通过系统函数转成Base64格式：

DECLARE @Nonce VARBINARY(16) = CRYPT_GEN_RANDOM(16);
DECLARE @NonceBase64 NVARCHAR(MAX) = sys.fn_varbintobase64(@Nonce);

CRYPT_GEN_RANDOM生成的随机数安全性足够，完全符合WS-Security的要求。

Created字段生成 #

Created是UTC时间戳，格式要求为yyyy-MM-ddTHH:mm:ss.fffZ，直接用GETUTCDATE()配合FORMAT函数就能生成：

DECLARE @Created NVARCHAR(30) = FORMAT(GETUTCDATE(), 'yyyy-MM-ddTHH:mm:ss.fffZ');

如果你的SQL Server版本低于2016（不支持FORMAT），可以用CONVERT拼接实现：

DECLARE @Created NVARCHAR(30) = LEFT(CONVERT(NVARCHAR(30), GETUTCDATE(), 127), 23) + 'Z';

UsernameToken的Id #

这个Id可以用GUID生成，去掉分隔符后加上前缀即可：

DECLARE @UsernameTokenId NVARCHAR(MAX) = 'UsernameToken-' + REPLACE(CONVERT(NVARCHAR(MAX), NEWID()), '-', '');

这样就能得到类似UsernameToken-10788FB36F6242F7FE151731009896412的唯一标识。

2. 完整存储过程实现 #

推荐用FOR XML PATH来构建XML，它会自动处理特殊字符和命名空间，比直接字符串拼接更可靠。下面是完整的存储过程：

CREATE PROCEDURE dbo.GenerateSOAPTransportRequest
    @DealerCode NVARCHAR(50),
    @Username NVARCHAR(50),
    @Password NVARCHAR(50),
    @SOAPRequest XML OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- 生成安全相关字段
    DECLARE @Nonce VARBINARY(16) = CRYPT_GEN_RANDOM(16);
    DECLARE @NonceBase64 NVARCHAR(MAX) = sys.fn_varbintobase64(@Nonce);
    DECLARE @Created NVARCHAR(30) = FORMAT(GETUTCDATE(), 'yyyy-MM-ddTHH:mm:ss.fffZ');
    DECLARE @UsernameTokenId NVARCHAR(MAX) = 'UsernameToken-' + REPLACE(CONVERT(NVARCHAR(MAX), NEWID()), '-', '');

    -- 构建完整SOAP XML
    SET @SOAPRequest = (
        SELECT
            -- 根节点命名空间
            'http://schemas.xmlsoap.org/soap/envelope/' AS 'xmlns:soapenv',
            'http://tempuri.org/' AS 'xmlns:tem',
            -- Header部分（包含WS-Security）
            (
                SELECT
                    '1' AS 'wsse:Security/@soapenv:mustUnderstand',
                    'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd' AS 'xmlns:wsse',
                    'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd' AS 'xmlns:wsu',
                    -- UsernameToken节点
                    (
                        SELECT
                            @UsernameTokenId AS '@wsu:Id',
                            @Username AS 'wsse:Username',
                            -- Password节点（带Type属性）
                            (
                                SELECT
                                    @Password AS '.',
                                    'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText' AS '@Type'
                                FOR XML PATH('wsse:Password'), TYPE
                            ),
                            -- Nonce节点（带EncodingType属性）
                            (
                                SELECT
                                    @NonceBase64 AS '.',
                                    'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary' AS '@EncodingType'
                                FOR XML PATH('wsse:Nonce'), TYPE
                            ),
                            @Created AS 'wsu:Created'
                        FOR XML PATH('wsse:UsernameToken'), TYPE
                    )
                FOR XML PATH('wsse:Security'), TYPE
            ) AS 'soapenv:Header',
            -- Body部分（业务请求）
            (
                SELECT
                    @DealerCode AS 'tem:Dealer_code'
                FOR XML PATH('tem:GetTransportMethods'), TYPE
            ) AS 'soapenv:Body'
        FOR XML PATH('soapenv:Envelope'), TYPE
    );
END
GO

3. 调用存储过程示例 #

直接调用存储过程就能生成目标XML：

DECLARE @GeneratedRequest XML;
EXEC dbo.GenerateSOAPTransportRequest
    @DealerCode = 'YOUR_DEALER_CODE',
    @Username = 'your_username',
    @Password = 'your_password',
    @SOAPRequest = @GeneratedRequest OUTPUT;

-- 查看生成的XML结果
SELECT @GeneratedRequest;

4. 关键注意事项 #

• 避免直接字符串拼接：FOR XML PATH会自动转义XML特殊字符（比如&、<、>），防止生成格式错误的XML
• 版本兼容：如果用的是SQL Server 2008及更早版本，CRYPT_GEN_RANDOM依然可用；如果是更老的版本，可以用NEWID()转二进制模拟随机数
• 安全性：存储过程中如果涉及敏感密码，建议用加密存储的方式传递，避免明文暴露

内容的提问来源于stack exchange，提问作者Matej Randík