时间戳

首页 > 解决方案 > 在混合值表上查询 SQL Server 中 XML 中的值选择

sql-server - 在混合值表上查询 SQL Server 中 XML 中的值选择

问题描述

我有一张桌子，里面有购买慈善晚会门票的人，这张桌子包含注册活动的详细信息，XML 将显示他们带来的客人，还有任何饮食要求的详细信息，以及偶尔参加活动的人可能被禁用。这应该被推送到我们的 CRM 系统，但目前无法正常工作。

我正在尝试从我们导入表的列中的一些 XML 中提取一些值。

我已经看到很多查询普通 XML 块的示例，但当 XML 位于具有其他正常值INT和VARCHAR值的表中时则不然。

我们使用的是 SQL Server 2014。我已经花了几个小时在谷歌上搜索，但对将两者结合在一起的查询毫无头绪。或者即使我应该将 XML 内容推送到一个临时表中，然后我可以与之进行连接。

Declare @xmlstring xml = '<field_import_admin_event_tickets xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
     xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <und is_array="true">
    <item>
      <value>8463</value>
      <revision_id>4763</revision_id>
    </item>
  </und>
</field_import_admin_event_tickets>'

select 
    MainDataCenter.Col.value('(value)[1]', 'varchar(max)') as Name, 
    MainDataCenter.Col.value('(revision_id)[1]', 'varchar(max)') as Value
from 
    @xmlstring.nodes('/field_import_admin_event_tickets/und/item') as MainDataCenter(Col)

^ 这会工作

但我需要同时查询它： -

SELECT * 
FROM [importtickets].[bcc].[entityform]
WHERE type LIKE '%show%'
  AND createdDATETIME > '2019-03-14'
  AND LEN(CAST(field_import_admin_event_tickets AS VARCHAR(MAX)) ) >1
  -- bodging a way of seeing if XML code exists or not, doesn't seem to work with IS NOT NULL
  AND Jobstatus = 'completed'

我可以粗略地从 XML 中获取值的唯一方法是将其转换为 aVARCHAR并使用大量 REPLACE 命令去除 XML 标记以将其归结为值。每个 XML 块中可能有 2 到 18 个数值

这是我在 StackOverflow 上的第一篇文章，我花了几天时间搜索这个，所以请对我温柔一点。谢谢。

2019-07-10 嘿，所以我没有完全说清楚。XML 的每一列（少数为空）包含 2 - 34 个单独的数字。我通过将其转换为 VARCHAR 并运行大量替换命令来更好地理解它，从而对数据进行了一些粗略的操作。 这是一些 XML 中最大的例子，34 个整数值，17 个是“值”，17 个是“revision_id” 所以我然后使用大量的 SUBSTRING 将它们全部推送到一个新表中。这是粗略但有效的，但假设每个值都是五位数长（到目前为止）我的老板并不热衷于这个解决方案。 使用 CAST 到 VARCHAR 粗略粉碎 XML 并手动剥离标签 我只需要在每一行中提取的每组值，这样我就可以对它们进行 JOIN 或子查询，其中包含一行或可识别的内容。这些数字将指的是参加某些慈善活动的客人，这些活动将具有一些属性，例如饮食要求或残疾。

标签： sql-serverxmltsqlxqueryapply

解决方案

我不知道，这是否是解决您的问题的最佳方法，但我希望您的问题是正确的，您希望将针对隔离 XML 的工作查询与表格查询结合起来，其中 XML 是内容一列：

首先，我创建了一个有两行的模型

DECLARE @mockupTable TABLE(ID INT IDENTITY,SomeOtherValue VARCHAR(100),YourXml XML);
INSERT INTO @mockupTable(SomeOtherValue,YourXml) VALUES
('This is some value in row 1'
,'<field_import_admin_event_tickets xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <und is_array="true">
    <item>
      <value>8463</value>
      <revision_id>4763</revision_id>
    </item>
  </und>
</field_import_admin_event_tickets>')

,('This is some value in row 2'
,'<field_import_admin_event_tickets xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <und is_array="true">
    <item>
      <value>999</value>
      <revision_id>888</revision_id>
    </item>
  </und>
</field_import_admin_event_tickets>');

--查询

SELECT t.ID
      ,t.SomeOtherValue
      ,MainDataCenter.Col.value('(value)[1]', 'varchar(max)') as Name 
      ,MainDataCenter.Col.value('(revision_id)[1]', 'varchar(max)') as Value
FROM @mockupTable t
CROSS APPLY t.YourXml.nodes('/field_import_admin_event_tickets/und/item') as MainDataCenter(Col);

结果

ID  SomeOtherValue               Name   Value
1   This is some value in row 1  8463   4763
2   This is some value in row 2   999    888

简而言之：

APPLY允许逐行调用表值函数。在这种情况下，我们将列的内容（在您的情况下为 XML）提交给内置函数.nodes()。
与 a 类似，JOIN我们得到一个连接集，它将列（和行）添加到最终集。我们可以使用该.value()方法从 XML 中检索实际值。

如果这是最好的方法？我不知道...

您上面的示例仅显示了一个<item>. .nodes()将需要返回<item>派生集中的多个元素。只需一个，直接<item>使用就可以更轻松地完成.value()...

推荐阅读