将XML列转换为MS SQL查询中的表数据。

Question

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

  <Group1>
      <Title>
          <Name>John Doe</Name>
          <Phone>555-3421</Phone>
          <Email>[e-mail]</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>[e-mail]</Email></Title></Group1>
10001   <Group1><Title><Name>Mary Sue</Name><Phone>381-2342</Phone><Email>[e-mail]</Email></Title></Group1>
10002   <Group1><Title><Name>Mark Dark</Name><Phone>312-7626</Phone><Email>[e-mail]</Email></Title></Group1>
10003   <Group1><Title><Name>Garth Dan</Name><Phone>341-4572</Phone><Email>[e-mail]</Email><State>California</State></Title></Group1>

我想编写一种 MS SQL 查询，可以像这样为我返回数据：

TheKey  Name        Phone       Email                          State
10000   John Doe    893-3421    [e-mail]                NULL
10001   Mary Sue    381-2342    [e-mail]           NULL
10002   Mark Dark   312-7626    [e-mail]             NULL
10003   Garth Dan   341-4572    [e-mail]         California

注意：最后一条记录有一个额外的XML标签<State>，而其他记录没有。我希望它可以灵活地使用一些父/子引用来实现这一点。

如果能得到任何帮助，我将不胜感激，因为我似乎找不到任何类似的东西=)

- Micro

---

这是一个很好的问题。不幸的是，有人决定将信息以这种格式倾泻到 SQL 表的一列中。但这也发生在我身上了！ - Pramod Mangalore

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不是XML而是varchar类型，则可以在查询期间对其进行转换。就像这样：

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文章中获取的这些信息。(投票支持它以便其他人更容易找到) 如何在tsql中查询xml列

- tgolisch

---

谢谢。我认为这个会很有效。然而，有没有可能让它动态化，比如不需要写出（/Group1/Title/Name）或AS Name，AS Phone，AS Email等。有没有一种方法可以说“输出此父级的子级并为每个子级创建列”呢？还是说这在SQL查询语句中不可能呢？（现在我戴上了我的Web开发帽子）。 - Micro

这可能是可以的，但我从未尝试过或见过其实现。您可以尝试在 Stack Overflow 上发布另一个关于此问题的提问。 - tgolisch

Answer 2

--OBTAIN THE XML DATA
     declare @xml as xml = '
     <division>
      <linea>
        <id_division>01</id_division>
        <division>REPUESTOS</division>
      </linea>
      <linea>
        <id_division>02</id_division>
        <division>BATERIAS</division>
      </linea>
      <linea>
        <id_division>03</id_division>
        <division>LUBRICANTES</division>
      </linea>
      <linea>
        <id_division>04</id_division>
        <division>NEUMATICOS</division>
      </linea>
    </division>'

-- create table or you can use existing table for structure
            create table #tmp_table (
                                    id_division integer,
                                    division nvarchar(20)
                                    )

            declare @docHandle int
            EXEC sp_xml_preparedocument @docHandle OUTPUT, @xml;  

            SELECT *
                FROM OPENXML(@docHandle, '/division/linea',3)   
                WITH #tmp_table;  

            ---DELETE tmp_TABLE
            drop table #tmp_table

- user2989200