OPENXML移植
原有SQL存储过程
/*------------------------------------
-- 用途：成批插入虚拟身份好友IP记录
-- 项目名称：中心6数据代理6.1.8
-- 说明：
-- 时间：2010-04-19 16:13:05
-- 编写者:
--------------------------------------
-- 修改记录:
-- 编号 修改时间 修改人 修改原因 修改标注
------------------------------------*/
CREATE PROCEDURE [dbo].[DataProxy_NIDC_log_FriendIP_BatchInsert]
(
@xml varchar(max) = NULL -- 数据
)
AS
SET NOCOUNT ON;
DECLARE @TableName nvarchar(100)
DECLARE @CreateTableSQL nvarchar(max)
DECLARE @InsertSQL nvarchar(max)
DECLARE @UpdateSQL nvarchar(max)
DECLARE @Suffix nvarchar(100)
DECLARE @docHandle int
CREATE TABLE #TEMP (
[MyCyberID] [bigint] NULL,
[FriendCyberID] [bigint] NULL,
[IP] nvarchar(50) NULL ,
[ClientID] UNIQUEIDENTIFIER NULL DEFAULT NEWID(),
[Time] [datetime] NULL DEFAULT(GETDATE()),
[UnitID] [int] NULL DEFAULT (0),
[ClientNo] [int] NULL DEFAULT (0),
[CyberType] [int] NULL,
[FriendCyberCode] [nvarchar] (128) NULL
)
EXEC sp_xml_preparedocument @docHandle OUTPUT, @Xml
INSERT #TEMP
SELECT
[MyCyberID]
,[FriendCyberID]
,[IP]
,[ClientID]
,MAX([Time])
,[UnitID]
,MAX([ClientNo])
,MAX([CyberType])
,MAX([FriendCyberCode])
FROM OPENXML(@docHandle, N'/ROOT/ROW')
WITH (
[MyCyberID] [bigint]
,[FriendCyberID] [bigint]
,[IP] nvarchar(50)
,[ClientID] UNIQUEIDENTIFIER
,[Time] [datetime]
,[UnitID] [int]
,[ClientNo] [int]
,[CyberType] [int]
,[FriendCyberCode] [nvarchar] (128)
)
WHERE [MyCyberID] <> 0 AND [FriendCyberID] <> 0
AND ClientID <> '00000000-0000-0000-0000-000000000000' AND[IP] <> ''
GROUP BY
[MyCyberID]
,[FriendCyberID]
,[IP]
,[ClientID]
,[UnitID]
EXEC sp_xml_removedocument @docHandle
SELECT
@Suffix = CONVERT(VARCHAR(8),Time,112)
,@TableName = N'[NIDCLOG' + LEFT(@Suffix,6) + '].[dbo].[NIDC_log_FriendIP' + @Suffix + ']'
,@CreateTableSQL = ISNULL(@CreateTableSQL ,N'
DECLARE @TableName nvarchar(200);') + N'
EXEC [dbo].[DataProxy_NIDC_log_FriendIP_CreateTable] @TableName OUTPUT ,''' + @Suffix + ''';'
,@UpdateSQL = ISNULL(@UpdateSQL,N'') + N'
UPDATE D SET
CyberType = T.CyberType
,[FriendCyberCode] = T.[FriendCyberCode]
,[ClientNo] = T.[ClientNo]
FROM ' + @TableName + ' D
,#TEMP T
WHERE D.UnitID = T.UnitID
AND D.ClientID = T.ClientID
AND D.[MyCyberID] = T.[MyCyberID]
AND D.[FriendCyberID] = T.[FriendCyberID]
AND D.[IP] = T.[IP]
AND T.Time >= ''' + @Suffix + '''
AND T.Time < DATEADD(DAY,1,''' + @Suffix + ''')
'
,@InsertSQL = ISNULL(@InsertSQL,N'') + N'
INSERT ' + @TableName + ' (
[MyCyberID]
,[FriendCyberID]
,[IP]
,[ClientID]
,[Time]
,[UnitID]
,[ClientNo]
,[CyberType]
,[FriendCyberCode]
)
SELECT
T.[MyCyberID]
,T.[FriendCyberID]
,T.[IP]
,T.[ClientID]
,T.[Time]
,T.[UnitID]
,T.[ClientNo]
,T.[CyberType]
,T.[FriendCyberCode]
FROM #TEMP T
WHERE T.Time >= ''' + @Suffix + '''
AND T.Time < DATEADD(DAY,1,''' + @Suffix + ''')
AND NOT EXISTS (
SELECT 1
FROM ' + @TableName + ' D --WITH(NOLOCK) --001
WHERE D.UnitID = T.UnitID
AND D.ClientID = T.ClientID
AND D.[MyCyberID] = T.[MyCyberID]
AND D.[FriendCyberID] = T.[FriendCyberID]
AND D.[IP] = T.[IP]
)
'
FROM #TEMP
GROUP BY CONVERT(VARCHAR(8),Time,112)
-- 检查表是否存在，不存在则创建
IF ISNULL(@CreateTableSQL,N'') <> N''
EXEC (@CreateTableSQL);
-- 更新顾客虚拟身份关系记录表
/*
IF ISNULL(@UpdateSQL,N'') <> N''
EXEC (@UpdateSQL);
*/
-- 插入顾客虚拟身份关系记录表
IF ISNULL(@InsertSQL,N'') <> N''
EXEC (@InsertSQL);
此存储过程的主要功能逻辑如下：
创建一个临时表#TEMP；
解析要导入的XML文件，XML中有多条record数据；
导入XML数据记录到临时表#TEMP中，只有满足条件的记录才导入到TEMP表中。
创建日志分库分表。获取当前系统时间，用NIDC_log_FriendIP + 日期得到表名称，用NIDCLOG+日期的年月 来得到数据库名，注意数据库不能自动创建，需要预先创建好。生成建表语句@CreateTableSQL。
调用存储过程[dbo].[DataProxy_NIDC_log_FriendIP_CreateTable] 来自动创建表。
从临时表TEMP中查询满足条件的记录，生成 INSERT 语句@InsertSQL。
执行@CreateTableSQL 和 @InsertSQL 语句。
到此，XML文件中的记录被插入到日志分库分表中
移植方案
由于TDS不支持XML导入，但是UDBTX支持XML的导入，因此XML解析和插入到临时表的工作可以放到UDBTX的函数中，在SQL Server存储过程中调用UDBTX封装的导入XML数据的函数来完成XML到表的处理。
由于SQL Server存储过程本身也有大量的代码，这部分代码UDB-TDS本身还是大部分支持的，所以这部分不需要全部改为UDBTX的函数来实现，即尽量保留原有的存储过程代码。
移植后的代码
UDBTX 函数部分
CREATE OR REPLACE FUNCTION nidc_dbo.importXmlToTable_DataProxy_NIDC_log_FriendIP_BatchInsert(xml_path
TEXT)
RETURNS INTEGER AS $$
DECLARE
data xml; records XML[]; record XML; MyCyberID bigint; FriendCyberID bigint; IP varchar(50); ClientID varchar(64); TimeS TIMESTAMP; UnitID int; ClientNo int; CyberType int; FriendCyberCode varchar; BEGIN -- Load the XML file --data := pg_catalog.pg_read_binary_file(xml_path, 0, 10000000); -- Adjust the size accordingly data := XMLPARSE(DOCUMENT convert_from(pg_catalog.pg_read_binary_file(xml_path), 'UTF8')); -- Adjust the size accordingly -- Extract records from the XML data records := xpath('/dataset/record', data); -- Assuming records are wrapped in <record> tags under a <root> element -- Loop through each record FOREACH record IN ARRAY records LOOP -- Extract the ID value using xpath MyCyberID := (xpath('//MyCyberID/text()', record))[1]::text::bigint; FriendCyberID := (xpath('//FriendCyberID/text()', record))[1]::text::bigint; IP := (xpath('//IP/text()', record))[1]::text; ClientID := (xpath('//ClientID/text()', record))[1]::text; TimeS := (xpath('//Time/text()', record))[1]::text::datetime; UnitID := (xpath('//UnitID/text()', record))[1]::text::int; ClientNo := (xpath('//ClientNo/text()', record))[1]::text::int; CyberType := (xpath('//CyberType/text()', record))[1]::text::int; FriendCyberCode := (xpath('//FriendCyberCode/text()', record))[1]::text; -- Check if ID is greater than 100 IF MyCyberID <> 0 AND FriendCyberID <> 0 AND ClientID <> '00000000-0000-0000-0000-000000000000' AND IP <> '' THEN -- Build the INSERT query for the record INSERT INTO TEMP (MyCyberID, FriendCyberID, IP, ClientID, Time, UnitID, ClientNo, CyberType,FriendCyberCode) VALUES (MyCyberID, FriendCyberID, IP, ClientID, TimeS, UnitID, ClientNo, CyberType,FriendCyberCode); END IF; END LOOP; RETURN 0; END; $$ LANGUAGE plpgsql; 上述函数的功能是把指定的XML格式的数据保存到临时表TEMP中，需要注意的是临时表在SQL Server存储过程中创建和删除。
此函数不太容易写成通用的函数，因为每个存储过程入库时有各种条件判断，根据客户现有的情况，针对每个OPENXML的存储过程定制写一个，函数的逻辑基本相同。
上述函数中用到了pg_read_binary_file 系统函数，此函数需要额外授权。授权命令如下（注意不是授权给 sa用户，而是nidc_dbo）：
GRANT EXECUTE ON FUNCTION pg_read_binary_file(text,bigint,bigint,boolean) TO nidc_dbo; GRANT EXECUTE ON FUNCTION pg_read_binary_file(text,bigint,bigint) TO nidc_dbo; GRANT EXECUTE ON FUNCTION pg_read_binary_file(text) TO nidc_dbo;
存储过程部分
CREATE PROCEDURE [dbo].[DataProxy_NIDC_log_FriendIP_BatchInsert] ( @xml varchar(max) = NULL -- 数据 ) AS SET NOCOUNT ON; DECLARE @TableName nvarchar(100) DECLARE @CreateTableSQL nvarchar(max) DECLARE @InsertSQL nvarchar(max) DECLARE @UpdateSQL nvarchar(max) DECLARE @Suffix nvarchar(100) DROP TABLE IF EXISTS TEMP; CREATE TABLE TEMP ( [MyCyberID] [bigint] NULL, [FriendCyberID] [bigint] NULL, [IP] nvarchar(50) NULL , [ClientID] UNIQUEIDENTIFIER NULL DEFAULT NEWID(), [Time] [datetime] NULL DEFAULT(GETDATE()), [UnitID] [int] NULL DEFAULT (0), [ClientNo] [int] NULL DEFAULT (0), [CyberType] [int] NULL, [FriendCyberCode] [nvarchar] (128) NULL ); SELECT [dbo].[importXmlToTable_DataProxy_NIDC_log_FriendIP_BatchInsert](@xml); SELECT @Suffix = CONVERT(VARCHAR(8),GETDATE(),112) SELECT @TableName = N'[NIDCLOG' + LEFT(@Suffix,6) + '].[dbo].[NIDC_log_FriendIP' + @Suffix + ']' SELECT -- @Suffix = CONVERT(VARCHAR(8),Time,112) --,@TableName = N'[NIDCLOG' + LEFT(@Suffix,6) + '].[dbo].[NIDC_log_FriendIP' + @Suffix + ']' --,@CreateTableSQL = ISNULL(@CreateTableSQL ,N' @CreateTableSQL = ISNULL(@CreateTableSQL ,N' DECLARE @TableName nvarchar(200);') + N' EXEC [dbo].[DataProxy_NIDC_log_FriendIP_CreateTable] @TableName OUTPUT ,''' + @Suffix + ''';' ,@UpdateSQL = ISNULL(@UpdateSQL,N'') + N' UPDATE D SET CyberType = T.CyberType ,[FriendCyberCode] = T.[FriendCyberCode] ,[ClientNo] = T.[ClientNo] FROM ' + @TableName + ' D ,TEMP T WHERE D.UnitID = T.UnitID AND D.ClientID = T.ClientID AND D.[MyCyberID] = T.[MyCyberID] AND D.[FriendCyberID] = T.[FriendCyberID] AND D.[IP] = T.[IP] AND T.Time >= @Suffix AND T.Time < DATEADD(DAY,1, @Suffix) ' ,@InsertSQL = ISNULL(@InsertSQL,N'') + N' INSERT ' + @TableName + ' ( [MyCyberID] ,[FriendCyberID] ,[IP] ,[ClientID] ,[Time] ,[UnitID] ,[ClientNo] ,[CyberType] ,[FriendCyberCode] ) SELECT T.[MyCyberID] ,T.[FriendCyberID] ,T.[IP] ,T.[ClientID] ,T.[Time] ,T.[UnitID] ,T.[ClientNo] ,T.[CyberType] ,T.[FriendCyberCode] FROM TEMP T WHERE T.Time >= ''' + @Suffix + ''' AND T.Time < DATEADD(DAY,1,''' + @Suffix + ''') AND NOT EXISTS ( SELECT 1 FROM ' + @TableName + ' D --WITH(NOLOCK) --001 WHERE D.UnitID = T.UnitID AND D.ClientID = T.ClientID AND D.[MyCyberID] = T.[MyCyberID] AND D.[FriendCyberID] = T.[FriendCyberID] AND D.[IP] = T.[IP] ) ' FROM TEMP --GROUP BY CONVERT(VARCHAR(8),Time,112) GROUP BY CONVERT(VARCHAR(8),GETDATE(),112) -- 检查表是否存在，不存在则创建 IF ISNULL(@CreateTableSQL,N'') <> N'' EXEC (@CreateTableSQL); -- 更新顾客虚拟身份关系记录表 /* IF ISNULL(@UpdateSQL,N'') <> N'' EXEC (@UpdateSQL); */ -- 插入顾客虚拟身份关系记录表 IF ISNULL(@InsertSQL,N'') <> N'' EXEC (@InsertSQL);
上述存储过程是在原有代码的基础上做了稍微做了一些修改而来：
创建临时表：由于SQL Server的临时表表名必须是#开头，比如#TEMP，而UDBTX不允许表名中包含特殊字符。因此无法与SQL Server兼容。所以在存储过程中，只能使用普通表，在存储过程执行完成时显式的删除表。
删除存储过程中原有的处理XML，并插入到临时表TEMP的代码。改为调用UDBTX的函数SELECT [dbo].importXmlToTable_DataProxy_NIDC_log_FriendIP_BatchInsert;
修改构造@CreateTableSQL、@UpdateSQL 和 @InsertSQL 语句的代码。此部分代码的执行结果与SQL Server不一致，应该是运算的优先级问题，原有的代码运行结果@Suffix、@TableName为NULL，解决方法是：把拼接语句中用到的变量放到前面单独的语句中，先进行赋值。如下：
SELECT @Suffix = CONVERT(VARCHAR(8),GETDATE(),112)
SELECT @TableName = N’[NIDCLOG’ + LEFT(@Suffix,6) + ‘].[dbo].[NIDC_log_FriendIP’ + @Suffix + ‘]’
关于此类问题，可能需要对所有的存储过程进行测试验证，存在问题的都需要调整。
执行结果
XML执行样例
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <dataset> <record> <MyCyberID>2</MyCyberID> <FriendCyberID>2</FriendCyberID> <IP>192.168.1.10</IP> <ClientID>A4156412CB6A4F3FA68C88941EDB2B1C</ClientID> <Time>2024-03-29 10:10:10</Time> <UnitID>2</UnitID> <ClientNo>2</ClientNo> <CyberType>2</CyberType> <FriendCyberCode>Friend cyber code</FriendCyberCode> </record> <record> <MyCyberID>3</MyCyberID> <FriendCyberID>3</FriendCyberID> <IP>192.168.1.10</IP> <ClientID>A4156412CB6A4F3FA68C88941EDB2B1C</ClientID> <Time>2024-03-29 10:10:10</Time> <UnitID>3</UnitID> <ClientNo>3</ClientNo> <CyberType>3</CyberType> <FriendCyberCode>Friend cyber code</FriendCyberCode> </record> </dataset>
插入到TEMP的数据
（略）
插入到分库分表中的数据
（略）
总结
OPENXML 相关的存储过程，功能都差不多，基本上按照上述思路可以解决。按照此方案进行移植，可以保持存储过程的接口不变，现有的应用软件也不修要修改。
在移植过程中，也发现UDB-TDS存在语句执行结果与原有的语句执行结果不一致的问题，所以所有存储过程都需要进行测试验证，不一致的地方要修改。