65.9K
CodeProject 正在变化。 阅读更多。
Home

在 T-SQL 的一个表中连接具有不同 XML 命名空间的 XML 字段

Aleksei Tuiman
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.33/5 (2投票s)

2013年6月10日

CPOL

4分钟阅读

viewsIcon

15936

从具有不同命名空间的 XML 字段提取数据

介绍 

如何在 SQL Server 中从具有不同 XML 命名空间的 XML 字段的表中提取数据到一个结果集中。

背景  

我维护一个数据库，其中包含一个具有用户活动日志的主表。这个特定的数据库是一个软件的一部分，该软件作为各种第三方信息系统之间的集成总线。日志表中的每一行都包含两个 XML 字段：输入对象和输出对象。输入对象持有对任何第三方系统的请求参数；输出对象持有来自特定第三方系统的响应。输入对象不包含任何 XML 命名空间，但输出对象包含。如何提取表行本身以及两个 XML 字段中的所有数据？

数据提取问题

日志表如下所示： 

CREATE TABLE [dbo].[EventLog](
	[IdEvent] int NOT NULL PRIMARY KEY,
	[EventName] nvarchar(255) NOT NULL,
	[EventDateTime] datetime NOT NULL,
	[InputObject] xml NOT NULL,
	[OutputObject] xml NOT NULL,
	[MethodName] nvarchar(255) NOT NULL,
	[IpAddress] nvarchar(255) NOT NULL,
	[Success] bit NOT NULL,
	[ErrorText] nvarchar(max) NULL,
	[SystemGuid] uniqueidentifier NOT NULL)

这张特定的表包含数十种不同方法的日志，每种方法都需要自己的 XML 结构。让我们仔细看看一种方法的结构。

这是输入对象的示例。请不要担心patient标签的内容，它只是一个记录的搜索字符串。 

<SearchTop10Patient>
  <patient>Name=Smith Surname=John Birthday=27.08.1936 0:00:00 
     SecondName= Document_N=  Document_S=</patient>
  <idLpu>9</idLpu>
  <guid>560FFA9C-A096-4FFB-8A73-0CB065EA7450</guid>
  <idHistory />
</SearchTop10Patient>

这是输出对象的示例。它不是一个完整的对象，我对其进行了截断，因为它太大了，但足以作为示例。 

<SearchTop10PatientResult xmlns="http://schemas.datacontract.org/2004/07/HubService2" 
                         xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <ErrorList />
  <IdHistory>430995</IdHistory>
  <Success>true</Success>
  <ListPatient>
    <Patient>
      <Birthday>1936-08-27T00:00:00</Birthday>
      <Phone>4347129</Phone>
      <IdPatient>222097</IdPatient>
    </Patient>
    <Patient>
      <Birthday>1936-08-27T00:00:00</Birthday>
      <Phone />
      <IdPatient>1504943</IdPatient>
    </Patient>
  </ListPatient>
</SearchTop10PatientResult>

 现在我想从表中提取数据：[IdEvent]、[EventDateTime]、[idLpu]、[IdPatient]、[Birthday] 和 [guid]

  • [IdEvent] 和 [EventDateTime] 包含在表中；
  • [idLpu] 和 [guid] 是输入对象（第一个 XML 字段）的一部分；
  • [IdPatient] 和 [Birthday] 是输出对象（第二个 XML 字段）的一部分。   

让我们尝试使用这段简单的代码来提取所需的数据

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.datacontract.org/2004/07/HubService2')
SELECT 
	el.[IdEvent],
	el.[EventDateTime],
	pats.value('./IdPatient[1]','int') AS [IdPatient],
	pats.value('./Birthday[1]','date') AS [Birthday],
	search.value('./idLpu[1]','nvarchar(20)') AS [idLpu],
	search.value('./guid[1]','uniqueidentifier') AS [guid]
FROM [EventLog] AS el CROSS APPLY [OutputObject].nodes(
         '/SearchTop10PatientResult/ListPatient/Patient') AS ST10Pout(pats)
	CROSS APPLY [InputObject].nodes('/SearchTop10Patient') AS ST10Pin(search)
WHERE (el.[MethodName] = 'SearchTop10Patient')
	AND(el.[EventDateTime] BETWEEN '05.02.2013' AND '06.02.2013')

此代码不返回任何内容！问题在于 WITH XMLNAMESPACES 构造同时应用于两个 XML 字段，作为默认 XML 命名空间：输入对象和输出对象。如果我注释掉引用其中一个 XML 字段的字符串，它将正常工作，但代码只会返回表行本身以及两个 XML 字段之一的数据。如何解决这个难题？ 

糟糕的解决方案

第一个想到的解决方案是使用临时表：将表与每个 XML 字段的联接分别提取到两个临时表中；然后可以联接这些临时表以获得最终数据集。联接的数据可以选择，甚至可以放在第三个临时表中……对我来说，这个解决方案听起来很糟糕。对于单个数据提取使用两个或三个临时表是在浪费服务器效率，生成的代码也变得更冗长。  

更好的解决方案

我为我的问题找到了一个更好的解决方案。我将数据提取代码分成两部分： 

  1. 表函数将表与输出 XML 对象联接在一起；
  2. 主代码将表与输入 XML 对象联接，并将其与表函数联接。

因此，数据提取算法的第一部分现在如下所示： 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fun_ExtractObject] 
(	
	@CheckDate datetime
)
RETURNS TABLE 
AS
RETURN 
(
WITH XMLNAMESPACES(DEFAULT N'http://schemas.datacontract.org/2004/07/HubService2')
SELECT 
	elog.[IdEvent], 
	x.value('./IdPatient[1]','nvarchar(50)') AS [IdPatient], 
	x.value('./Birthday[1]','date') AS [Birthday]
FROM [dbo].[EventLog] AS elog CROSS APPLY [OutputObject].nodes(
        '/SearchTop10PatientResult/ListPatient/Patient') AS e(x)
WHERE (elog.[MethodName] = 'SearchTop10Patient')
	AND(elog.[EventDateTime] BETWEEN @CheckDate AND DATEADD(DAY,1,@CheckDate))
)

算法的第二部分如下所示： 

DECLARE @CheckDate datetime

SET @CheckDate = '05.02.2013'

SELECT 
elog.[IdEvent],
	elog.[EventDateTime],
	x.value('./idLpu[1]','nvarchar(50)') AS [idLpu],
	x.value('./guid[1]','uniqueidentifier') as [guid],
	f_eo.[IdPatient],
	f_eo.[Birthday]
FROM [dbo].[EventLog] AS elog CROSS APPLY [InputObject].nodes('/SearchTop10Patient') AS e(x)
	INNER JOIN [dbo].[fun_ExtractObject](@CheckDate) AS f_eo ON elog.[IdEvent] = f_eo.[IdEvent]
WHERE (elog.[MethodName] = 'SearchTop10Patient')
	AND(elog.[EventDateTime] BETWEEN @CheckDate AND DATEADD(DAY,1,@CheckDate))

表函数的输入参数有助于最小化联接的数据量，因为这张特定的日志表包含超过 100GB 的数据，并且数据量在不断增长。[IdEvent] 字段用作所有数据片段的联接字段，因为它是数据表的主键。  

数据提取算法的输出是一个扁平表，可以轻松地用于各种报告。 

此外，为了优化速度，我在 [IdEvent] 字段上使用了聚集索引和以下非聚集索引： 

CREATE NONCLUSTERED INDEX [ind_EventLog_DtName] ON [dbo].[EventLog]
(
	[EventDateTime] ASC,
	[EventName] ASC
)
INCLUDE ([IdEvent]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
  SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

如果 [InputObject] XML 字段包含自己的 XML 命名空间，则应在 SELECT 语句之前使用 WITH XMLNAMESPACES 构造声明该命名空间。此外，如果 WITH XMLNAMESPACES 构造不是脚本中的第一个语句，它前面应该有一个分号。

可能是最好的解决方案 

我认为我问题的最佳解决方案是转换输入 XML 或输出 XML 字段，这样它们都将包含相同的 XML 命名空间，或者都不包含。这听起来很合理，但这种方法需要对当前代码进行一些更改，并且还需要对其中一个 XML 字段进行大量更新。所有这些改进都必须同时完成。 

结论

表函数是一个很好的工具，可以避免在数据表包含具有不同 XML 命名空间的 XML 字段时出现数据提取问题。这种方法使得生成的代码在阅读和维护方面看起来更好一些。 

兴趣点 

MSDN 关于 WITH XMLNAMESPACES 构造的页面。 

© . All rights reserved.