sql - 在 MS SQL 查询中将 XML 列转换为表数据。

Question

在我的数据库的一个表中，我有一列，其中的整个条目是一个具有以下结构的长 XML 字符串:

  <Group1>
      <Title>
          <Name>John Doe</Name>
          <Phone>555-3421</Phone>
          <Email>catman@gmail.com</Email>
          (+more)
      </Title>
  </Group1>

这是我的 SELECT 语句，这是它的输出:

SELECT TheKey, TheData FROM MyTable;

SQL Server Management Studio 中的输出:

TheKey  TheData
10000   <Group1><Title><Name>John Doe</Name><Phone>893-3421</Phone><Email>catman@gmail.com</Email></Title></Group1>
10001   <Group1><Title><Name>Mary Sue</Name><Phone>381-2342</Phone><Email>thebestdude@gmail.com</Email></Title></Group1>
10002   <Group1><Title><Name>Mark Dark</Name><Phone>312-7626</Phone><Email>mybook231@gmail.com</Email></Title></Group1>
10003   <Group1><Title><Name>Garth Dan</Name><Phone>341-4572</Phone><Email>lampshade032@gmail.com</Email><State>California</State></Title></Group1>

我想编写某种 MS SQL 查询，它将像这样向我返回数据:

TheKey  Name        Phone       Email                          State
10000   John Doe    893-3421    catman@gmail.com                NULL
10001   Mary Sue    381-2342    thebestdude@gmail.com           NULL
10002   Mark Dark   312-7626    mybook231@gmail.com             NULL
10003   Garth Dan   341-4572    lampshade032@gmail.com         California

注意最后一个条目如何有一个额外的 XML 标记 <State>其他条目没有。我希望它像这样灵活——使用某种父/子引用？

如果能提供任何帮助，我将不胜感激，我似乎在任何地方都找不到类似的东西 =)

Answer 1

如果您的 XML 列存储为 XML 类型，那么您可以使用 XPath 查询来获取每一列。像这样:

SELECT TheKey, 
    TheData.value('(/Group1/Title/Name)[1]', 'varchar(100)') AS Name,
    TheData.value('(/Group1/Title/Phone)[1]', 'varchar(100)') AS Phone,
    TheData.value('(/Group1/Title/Email)[1]', 'varchar(250)') AS Email,
    TheData.value('(/Group1/Title/State)[1]', 'varchar(100)') AS [State]
FROM MyTable

如果 MyData 列是 varchar 而不是 XML，那么您可以在查询期间转换它。像这样:

SELECT TheKey, 
    Cast(TheData AS XML).value('(/Group1/Title/Name)[1]', 'varchar(100)') AS Name,
    Cast(TheData AS XML).value('(/Group1/Title/Phone)[1]', 'varchar(100)') AS Phone,
    Cast(TheData AS XML).value('(/Group1/Title/Email)[1]', 'varchar(250)') AS Email,
    Cast(TheData AS XML).value('(/Group1/Title/State)[1]', 'varchar(100)') AS [State]
FROM MyTable

顺便说一句，我是从另一篇 SO 文章中获得这些信息的。 (投上一票，让其他人更容易找到)How to query xml column in tsql